CSV spreadsheet files are suitable for storing tabular data in a relatively portable way.
The CSV format is flexible but somewhat ill-defined.
For present purposes, authors may assume that the data fields contain no commas, backslashes, or quotation marks.
Read a CSV file, change some values and save the changes back to a file.
For this task we will use the following CSV file:
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
Suggestions
L(=line) File(‘data.csv’).read_lines() I L.index == 0 line ‘’= ‘,SUM’ E line ‘’= ‘,’sum(line.split(‘,’).map(Int)) print(line)
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Ada has no build-in or predefined functions to read or write CSV tables. We thus define a (very simplistic) package CSV, which allows to read a row (function Line), to step from column to column (function Next), and to get the items in the column (function Item):
packageCSVistypeRow(<>)istaggedprivate;functionLine(S:String;Separator:Character:=',')returnRow;functionNext(R:inoutRow)returnBoolean;-- if there is still an item in R, Next advances to it and returns TruefunctionItem(R:Row)returnString;-- after calling R.Next i times, this returns the i'th item (if any)privatetypeRow(Length:Natural)istaggedrecordStr:String(1..Length);Fst:Positive;Lst:Natural;Nxt:Positive;Sep:Character;end record;endCSV;
The implementation of the package is
packagebodyCSVisfunctionLine(S:String;Separator:Character:=',')returnRowis(Length=>S'Length,Str=>S,Fst=>S'First,Lst=>S'Last,Nxt=>S'First,Sep=>Separator);functionItem(R:Row)returnStringis(R.Str(R.Fst..R.Lst));functionNext(R:inoutRow)returnBooleanisLast:Natural:=R.Nxt;beginR.Fst:=R.Nxt;whileLast<=R.Str'Lastand thenR.Str(Last)/=R.Seploop-- find SeparatorLast:=Last+1;endloop;R.Lst:=Last-1;R.Nxt:=Last+1;return(R.Fst<=R.Str'Last);endNext;endCSV;
Finally, the main program which uses the package CSV:
withCSV,Ada.Text_IO;useAda.Text_IO;procedureCSV_Data_ManipulationisHeader:String:=Get_Line;beginPut_Line(Header&", SUM");whilenotEnd_Of_FileloopdeclareR:CSV.Row:=CSV.Line(Get_Line);Sum:Integer:=0;beginwhileR.NextloopSum:=Sum+Integer'Value(R.Item);Put(R.Item&",");endloop;Put_Line(Integer'Image(Sum));end;endloop;endCSV_Data_Manipulation;
>./csv_data_manipulation < csv_sample.csv C1,C2,C3,C4,C5, SUM1,5,9,13,17, 452,6,10,14,18, 503,7,11,15,19, 554,8,12,16,20, 60
voidread_csv(list t, text path){ file f; list l; f_affix(f, path); while (f_news(f, l, 0, 0, ",") ^ -1) { l_append(t, l); }}listsum_columns(list t){ list c, l; integer i; l_append(c, "SUM"); for (i, l in t) { if (i) { integer j, sum; text s; sum = 0; for (j, s in l) { sum += atoi(s); } l_append(c, sum); } } return c;}voidadd_column(list t, list c){ integer i; list l; for (i, l in t) { l_append(l, c[i]); }}voidwrite_csv(list t, text path){ integer i; file f; list l; f_create(f, path, 00644); for (i, l in t) { f_(f, l[0]); l_ocall(l, f_, 2, 1, -1, f, ","); f_newline(f); }}integermain(void){ list t; read_csv(t, "tmp/CSV_data_manipulation.csv"); add_column(t, sum_columns(t)); write_csv(t, "tmp/CSV_data_manipulated.csv"); return 0;}C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
# count occurrances of a char in string #PROC char count = (CHAR c, STRING str) INT: BEGIN INT count := 0; FOR i TO UPB str DO IF c = str[i] THEN count +:= 1 FI OD; count END; # split string on separator #PROC char split = (STRING str, CHAR sep) FLEX[]STRING : BEGIN INT strlen := UPB str, cnt := 0; INT len, p; INT start := 1; [char count (sep, str) + 1] STRING list; WHILE start <= strlen ANDF char in string (sep, p, str[start:]) DO p +:= start - 1; list[cnt +:= 1] := str[start:p-1]; start := p + 1 OD; IF cnt = 0 THEN list[cnt +:= 1] := str ELIF start <= UPB str + 1 THEN list[cnt +:= 1] := str[start:] FI; list END;PROC join = ([]STRING words, STRING sep) STRING: IF UPB words > 0 THEN STRING str := words [1]; FOR i FROM 2 TO UPB words DO str +:= sep + words[i] OD; str ELSE "" FI;# read a line from file #PROC readline = (REF FILE f) STRING: BEGIN STRING line; get (f, line); new line (f); line END;# Add one item to tuple #OP +:= = (REF FLEX[]STRING tuple, STRING item) VOID: BEGIN [UPB tuple+1]STRING new; new[:UPB tuple] := tuple; new[UPB new] := item; tuple := new END;# convert signed number TO INT #OP TOINT = (STRING str) INT: BEGIN INT n := 0, sign := 1; FOR i TO UPB str WHILE sign /= 0 DO IF is digit (str[i]) THEN n := n * 10 + ABS str[i] - ABS "0" ELIF i = 1 AND str[i] = "-" THEN sign := -1 ELIF i /= 1 OR str[i] /= "+" THEN sign := 0 FI OD; n * sign END;OP STR = (INT i) STRING: whole (i,0);# The main program #FILE foo;open (foo, "CSV_data_manipulation.data", stand in channel);FLEX[0]STRING header := char split (readline (foo), ",");header +:= "SUM";print ((join (header, ","), new line));WHILE NOT end of file (foo) DO FLEX[0]STRING fields := char split (readline (foo), ","); INT sum := 0; FOR i TO UPB fields DO sum +:= TOINT fields[i] OD; fields +:= STR sum; print ((join (fields, ","), new line))OD;close (foo)
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
; data.csv;; C1,C2,C3,C4,C5; 1,5,9,13,17; 2,6,10,14,18; 3,7,11,15,19; 4,8,12,16,20table:read.csv"data.csv"data:[]looptable'row[addable:["SUM"]ifrow<>firsttable->addable:@[to:stringsummaprow'x[to:integerx]]'data++@[row++addable]]loopdata'row[looprow'column->printspadcolumn6print""]
Loop,Read,Data.csv{i:=A_IndexLoop,Parse,A_LoopReadLine,CSVOutput.=(i=A_Index&&i!=1?A_LoopField**2:A_LoopField)(A_Index=5?"`n":",")}FileAppend,%Output%,NewData.csv
Output:
C1,C2,C3,C4,C51,25,9,13,172,6,100,14,183,7,11,225,194,8,12,16,400
adds a column sum to a csv table
#!/usr/bin/awk -fBEGIN{FS=OFS=","}NR==1{print$0,"SUM"next}{sum=0for(i=1;i<=NF;i++){sum+=$i}print$0,sum}
awk -f csv_data_manipulation.awk data.csv C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Load the data, change a value and add a column with totals. Then save and print.
OPTION COLLAPSE TRUEOPTION DELIM ","csv$ = LOAD$("data.csv")DOTIMES AMOUNT(csv$, NL$) line$ = TOKEN$(csv$, _, NL$) IF _ = 1 THEN total$ = APPEND$(line$, 0, "SUM") ELSE line$ = CHANGE$(line$, _, STR$(_*10) ) total$ = APPEND$(total$, 0, line$, NL$) total$ = APPEND$(total$, 0, STR$(LOOP(i, AMOUNT(line$), VAL(TOKEN$(line$, i)))) ) FIDONESAVE total$ TO "data.csv"PRINT total$C1,C2,C3,C4,C5,SUM1,20,9,13,17,602,6,30,14,18,703,7,11,40,19,804,8,12,16,50,90
OPEN"manip.csv"FORINPUTAS#1OPEN"manip2.csv"FOROUTPUTAS#2LINEINPUT#1,header$PRINT#2,header$+",SUM"WHILENOTEOF(1)INPUT#1,c1,c2,c3,c4,c5sum=c1+c2+c3+c4+c5WRITE#2,c1,c2,c3,c4,c5,sumWENDCLOSE#1,#2END
Igual que la entrada de FreeBASIC.
csvstr←⟨"C1,C2,C3,C4,C5","1,5,9,13,17","2,6,10,14,18","3,7,11,15,19","4,8,12,16,20"⟩Split←(⊢-˜¬×+`)∘=⟜','⊸⊔strdata←>Split¨csvstrintdata←•BQN¨⌾(1⊸↓)strdatasums←⟨"SUMS"⟩∾+´˘1↓intdatadone←sums∾˜˘intdata
#define TITLE "CSV data manipulation"#define URL "http://rosettacode.org/wiki/CSV_data_manipulation"#define _GNU_SOURCE#define bool int#include<stdio.h>#include<stdlib.h> /* malloc...*/#include<string.h> /* strtok...*/#include<ctype.h>#include<errno.h>/** * How to read a CSV file ? */typedefstruct{char*delim;unsignedintrows;unsignedintcols;char**table;}CSV;/** * Utility function to trim whitespaces from left & right of a string */inttrim(char**str){inttrimmed;intn;intlen;len=strlen(*str);n=len-1;/* from right */while((n>=0)&&isspace((*str)[n])){(*str)[n]='\0';trimmed+=1;n--;}/* from left */n=0;while((n<len)&&(isspace((*str)[0]))){(*str)[0]='\0';*str=(*str)+1;trimmed+=1;n++;}returntrimmed;}/** * De-allocate csv structure */intcsv_destroy(CSV*csv){if(csv==NULL){return0;}if(csv->table!=NULL){free(csv->table);}if(csv->delim!=NULL){free(csv->delim);}free(csv);return0;}/** * Allocate memory for a CSV structure */CSV*csv_create(unsignedintcols,unsignedintrows){CSV*csv;csv=malloc(sizeof(CSV));csv->rows=rows;csv->cols=cols;csv->delim=strdup(",");csv->table=malloc(sizeof(char*)*cols*rows);if(csv->table==NULL){gotoerror;}memset(csv->table,0,sizeof(char*)*cols*rows);returncsv;error:csv_destroy(csv);returnNULL;}/** * Get value in CSV table at COL, ROW */char*csv_get(CSV*csv,unsignedintcol,unsignedintrow){unsignedintidx;idx=col+(row*csv->cols);returncsv->table[idx];}/** * Set value in CSV table at COL, ROW */intcsv_set(CSV*csv,unsignedintcol,unsignedintrow,char*value){unsignedintidx;idx=col+(row*csv->cols);csv->table[idx]=value;return0;}voidcsv_display(CSV*csv){introw,col;char*content;if((csv->rows==0)||(csv->cols==0)){printf("[Empty table]\n");return;}printf("\n[Table cols=%d rows=%d]\n",csv->cols,csv->rows);for(row=0;row<csv->rows;row++){printf("[|");for(col=0;col<csv->cols;col++){content=csv_get(csv,col,row);printf("%s\t|",content);}printf("]\n");}printf("\n");}/** * Resize CSV table */intcsv_resize(CSV*old_csv,unsignedintnew_cols,unsignedintnew_rows){unsignedintcur_col,cur_row,max_cols,max_rows;CSV*new_csv;char*content;boolin_old,in_new;/* Build a new (fake) csv */new_csv=csv_create(new_cols,new_rows);if(new_csv==NULL){gotoerror;}new_csv->rows=new_rows;new_csv->cols=new_cols;max_cols=(new_cols>old_csv->cols)?new_cols:old_csv->cols;max_rows=(new_rows>old_csv->rows)?new_rows:old_csv->rows;for(cur_col=0;cur_col<max_cols;cur_col++){for(cur_row=0;cur_row<max_rows;cur_row++){in_old=(cur_col<old_csv->cols)&&(cur_row<old_csv->rows);in_new=(cur_col<new_csv->cols)&&(cur_row<new_csv->rows);if(in_old&&in_new){/* re-link data */content=csv_get(old_csv,cur_col,cur_row);csv_set(new_csv,cur_col,cur_row,content);}elseif(in_old){/* destroy data */content=csv_get(old_csv,cur_col,cur_row);free(content);}else{/* skip */}}}/* on rows */free(old_csv->table);old_csv->rows=new_rows;old_csv->cols=new_cols;old_csv->table=new_csv->table;new_csv->table=NULL;csv_destroy(new_csv);return0;error:printf("Unable to resize CSV table: error %d - %s\n",errno,strerror(errno));return-1;}/** * Open CSV file and load its content into provided CSV structure **/intcsv_open(CSV*csv,char*filename){FILE*fp;unsignedintm_rows;unsignedintm_cols,cols;charline[2048];char*lineptr;char*token;fp=fopen(filename,"r");if(fp==NULL){gotoerror;}m_rows=0;m_cols=0;while(fgets(line,sizeof(line),fp)!=NULL){m_rows+=1;cols=0;lineptr=line;while((token=strtok(lineptr,csv->delim))!=NULL){lineptr=NULL;trim(&token);cols+=1;if(cols>m_cols){m_cols=cols;}csv_resize(csv,m_cols,m_rows);csv_set(csv,cols-1,m_rows-1,strdup(token));}}fclose(fp);csv->rows=m_rows;csv->cols=m_cols;return0;error:fclose(fp);printf("Unable to open %s for reading.",filename);return-1;}/** * Open CSV file and save CSV structure content into it **/intcsv_save(CSV*csv,char*filename){FILE*fp;introw,col;char*content;fp=fopen(filename,"w");for(row=0;row<csv->rows;row++){for(col=0;col<csv->cols;col++){content=csv_get(csv,col,row);fprintf(fp,"%s%s",content,((col==csv->cols-1)?"":csv->delim));}fprintf(fp,"\n");}fclose(fp);return0;}/** * Test */intmain(intargc,char**argv){CSV*csv;printf("%s\n%s\n\n",TITLE,URL);csv=csv_create(0,0);csv_open(csv,"fixtures/csv-data-manipulation.csv");csv_display(csv);csv_set(csv,0,0,"Column0");csv_set(csv,1,1,"100");csv_set(csv,2,2,"200");csv_set(csv,3,3,"300");csv_set(csv,4,4,"400");csv_display(csv);csv_save(csv,"tmp/csv-data-manipulation.result.csv");csv_destroy(csv);return0;}
Column0,C2,C3,C4,C51,100,9,13,172,6,200,14,183,7,11,300,194,8,12,16,400
#include<gadget/gadget.h>LIB_GADGET_STARTvoidMuestra_archivo_original();Mainif(Exist_file("load_matrix.txt")){/* recupero informacion del archivo para su apertura segura */F_STATdataFile=Stat_file("load_matrix.txt");if(dataFile.is_matrix)// tiene forma de matriz???{Newmultitypetest;/* establezco los rangos a leer */Rangefortest[0:1:dataFile.total_lines-1,0:1:dataFile.max_tokens_per_line-1];/* cargamos el array con detección de enteros como LONG */test=Load_matrix_mt(pSDS(test),"load_matrix.txt",dataFile,DET_LONG);/* modifica algunas cosas del archivo *//* sChg() no es necesario aquí, porque no se está cambiando el tipo de la celda, sino que se reemplaza el string: *////sChg( test, 0,1, "Columna 1");/* Con Let() basta... */Let($s-test[0,1],"Columna 1");$l-test[2,1]=1000;$l-test[2,2]=2000;/* inserto filas *//* preparo la fila a insertar */Newmultitypenueva_fila;sAppend_mt(nueva_fila,"fila 3.1");Append_mt(nueva_fila,float,0.0);Append_mt(nueva_fila,int,0);Append_mt(nueva_fila,double,0.0);Append_mt(nueva_fila,long,0L);/* insertamos la misma fila en el array, 3 veces */test=Insert_row_mt(pSDS(test),pSDS(nueva_fila),4);test=Insert_row_mt(pSDS(test),pSDS(nueva_fila),4);test=Insert_row_mt(pSDS(test),pSDS(nueva_fila),4);Freemultitypenueva_fila;Print"\nGuardando archivo en\"save_matrix.txt\"...\n";DEC_PREC=20;/* establece precision decimal para despliegue */Allrangefortest;Save_matrix_mt(SDS(test),"save_matrix.txt");Freemultitypetest;Print"\nArchivo original:\n";Muestra_archivo_original();}}EndvoidMuestra_archivo_original(){Stringcsys;csys=`catload_matrix.txt`;Print"\n%s\n",csys;Freesecurecsys;}
$ ./tests/loadmtGuardando archivo en "save_matrix.txt"...Archivo original:+,head 1,head 2,head 3,head 4fila 1,0.7226562500000,0.7198443412781,0.7170542478561,0.7142857313156fila 2,83,77,93,86fila 3,0.5000000000000,0.5150380749101,0.5299192642332,0.5446390350150fila 4,30886,36915,38335,60492fila 5,1.8213465987073e+2,1.8213465987073e+4,1.8213465987073e+6,1.8213465987073e+8fila 6,1.8213465987073e-2,1.8213465987073e-4,1.8213465987073e-6,1.8213465987073e-8$ cat save_matrix.txt +,Columna 1,head 2,head 3,head 4fila 1,0.72265625000000000000,0.71984434127810004167,0.71705424785609994665,0.71428573131560002540fila 2,1000,2000,93,86fila 3,0.50000000000000000000,0.51503807491010000774,0.52991926423320001582,0.54463903501499999482fila 3.1,0.00000000000000000000,0,0.00000000000000000000,0fila 3.1,0.00000000000000000000,0,0.00000000000000000000,0fila 3.1,0.00000000000000000000,0,0.00000000000000000000,0fila 4,30886,36915,38335,60492fila 5,182.13465987073001883800,18213.46598707299926900305,1821346.59870730014517903328,182134659.87073001265525817871fila 6,0.01821346598707300132,0.00018213465987073003,0.00000182134659870730,0.00000001821346598707$
usingSystem.IO;usingSystem.Linq;namespaceCSV_data_manipulation{classProgram{staticvoidMain(){varinput=File.ReadAllLines("test_in.csv");varoutput=input.Select((line,i)=>{if(i==0)returnline+",SUM";varsum=line.Split(',').Select(int.Parse).Sum();returnline+","+sum;}).ToArray();File.WriteAllLines("test_out.csv",output);}}}
Column0,C2,C3,C4,C5,SUM1,100,9,13,17,1402,6,200,14,18,2403,7,11,300,19,3404,8,12,16,400,440
#include<map>#include<vector>#include<iostream>#include<fstream>#include<utility>#include<functional>#include<string>#include<sstream>#include<algorithm>#include<cctype>classCSV{public:CSV(void):m_nCols(0),m_nRows(0){}boolopen(constchar*filename,chardelim=','){std::ifstreamfile(filename);clear();if(file.is_open()){open(file,delim);returntrue;}returnfalse;}voidopen(std::istream&istream,chardelim=','){std::stringline;clear();while(std::getline(istream,line)){unsignedintnCol=0;std::istringstreamlineStream(line);std::stringcell;while(std::getline(lineStream,cell,delim)){m_oData[std::make_pair(nCol,m_nRows)]=trim(cell);nCol++;}m_nCols=std::max(m_nCols,nCol);m_nRows++;}}boolsave(constchar*pFile,chardelim=','){std::ofstreamofile(pFile);if(ofile.is_open()){save(ofile);returntrue;}returnfalse;}voidsave(std::ostream&ostream,chardelim=','){for(unsignedintnRow=0;nRow<m_nRows;nRow++){for(unsignedintnCol=0;nCol<m_nCols;nCol++){ostream<<trim(m_oData[std::make_pair(nCol,nRow)]);if((nCol+1)<m_nCols){ostream<<delim;}else{ostream<<std::endl;}}}}voidclear(){m_oData.clear();m_nRows=m_nCols=0;}std::string&operator()(unsignedintnCol,unsignedintnRow){m_nCols=std::max(m_nCols,nCol+1);m_nRows=std::max(m_nRows,nRow+1);returnm_oData[std::make_pair(nCol,nRow)];}inlineunsignedintGetRows(){returnm_nRows;}inlineunsignedintGetCols(){returnm_nCols;}private:// trim string for empty spaces in begining and at the endinlinestd::string&trim(std::string&s){s.erase(s.begin(),std::find_if(s.begin(),s.end(),std::not1(std::ptr_fun<int,int>(std::isspace))));s.erase(std::find_if(s.rbegin(),s.rend(),std::not1(std::ptr_fun<int,int>(std::isspace))).base(),s.end());returns;}private:std::map<std::pair<unsignedint,unsignedint>,std::string>m_oData;unsignedintm_nCols;unsignedintm_nRows;};intmain(){CSVoCSV;oCSV.open("test_in.csv");oCSV(0,0)="Column0";oCSV(1,1)="100";oCSV(2,2)="200";oCSV(3,3)="300";oCSV(4,4)="400";oCSV.save("test_out.csv");return0;}
Column0,C2,C3,C4,C51,100,9,13,172,6,200,14,183,7,11,300,194,8,12,16,400
(require'[clojure.data.csv:ascsv]'[clojure.java.io:asio])(defnadd-sum-column[coll](let[titles(firstcoll)values(restcoll)](cons(conjtitles"SUM")(map#(conj%(reduce +(mapread-string%)))values))))(with-open[in-file(io/reader"test_in.csv")](doall(let[out-data(add-sum-column(csv/read-csvin-file))](with-open[out-file(io/writer"test_out.csv")](csv/write-csvout-fileout-data)))))
test_out.csv):C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Using tech.ml.dataset
(require'[tech.v3.dataset:asds]'[tech.v3.datatype.functional:asdfn])(defnadd-sum[dataframe](assocdataframe"SUM"(applydfn/+(mapdataframe(ds/column-namesdataframe)))))(ds/write!(add-sum(ds/->dataset"resources/input.csv"))"resources/output.csv")
IDENTIFICATIONDIVISION.PROGRAM-ID.CSV.AUTHOR.BillGunshannon.INSTALLATION.Home.DATE-WRITTEN.19December2021. ************************************************************ ** Program Abstract: ** CSVs are something COBOL does pretty well. ** The commented out CONCATENATE statements are a ** second method other than the STRING method. ************************************************************ENVIRONMENTDIVISION.CONFIGURATIONSECTION.REPOSITORY.FUNCTIONALLINTRINSIC.INPUT-OUTPUTSECTION.FILE-CONTROL.SELECTCSV-FileASSIGNTO"csv.txt"ORGANIZATIONISLINESEQUENTIAL.SELECTOut-FileASSIGNTO"new.csv.txt"ORGANIZATIONISLINESEQUENTIAL.DATADIVISION.FILESECTION.FDCSV-FileDATARECORDISCSV-Record.01CSV-Record.05Field1PIC X(64).FDOut-FileDATARECORDISOut-Line.01Out-LinePIC X(80).WORKING-STORAGESECTION.01EofPIC XVALUE'F'.01CSV-Data.05CSV-Col1PIC 9(5).05CSV-Col2PIC 9(5).05CSV-Col3PIC 9(5).05CSV-Col4PIC 9(5).05CSV-Col5PIC 9(5).01CSV-SumPIC ZZZ9.01CSV-Sum-AlphaREDEFINESCSV-SumPIC X(4).PROCEDUREDIVISION.Main-Program.OPENINPUTCSV-FileOPENOUTPUTOut-FilePERFORMRead-a-RecordPERFORMBuild-HeaderPERFORMUNTILEof='T'PERFORMRead-a-RecordIFEofNOTEQUAL'T'PERFORMProcess-a-RecordEND-PERFORMCLOSECSV-FileCLOSEOut-FileSTOPRUN.Read-a-Record.READCSV-FileATENDMOVE'T'TOEofEND-READ.Build-Header. ** MOVE CONCATENATE(TRIM(CSV-Record), ",SUM" ** TO Out-Line.STRINGTRIM(CSV-Record),",SUM"INTOOut-Line.WRITEOut-Line.MOVESPACESTOOut-Line.Process-a-Record.UNSTRINGCSV-RecordDELIMITEDBY','INTOCSV-Col1CSV-Col2CSV-Col3CSV-Col4CSV-Col5.COMPUTECSV-Sum=CSV-Col1+CSV-Col2+CSV-Col3+CSV-Col4+CSV-Col5. ** MOVE CONCATENATE(TRIM(CSV-Record), "," TRIM(CSV-Sum-Alpha)) ** TO Out-Line.STRINGTRIM(CSV-Record),","TRIM(CSV-Sum-Alpha)INTOOut-Line.WRITEOut-Line.MOVESPACESTOOut-Line.END-PROGRAM.
Used only built-in functions which are in the standard. There are widespread libraries for working with csv (which can be easily loaded via quicklisp). As another example, I didn't use a split-string function, even though it is available in some implementations and in many compatibility layers and libraries. Instead, I formatted the csv file into s-expressions for the reader to understand it. Also, it deserves a mention that Common Lisp has built-in arrays, but for so little data it is easier to use nested lists.
(defuncsvfile-to-nested-list(filenamedelim-char)"Reads the csv to a nested list, where each sublist represents a line."(with-open-file(inputfilename)(loop:forline:=(read-lineinputnil):whileline:collect(read-from-string(substitute#\SPACEdelim-char(formatnil"(~a)~%"line))))))(defunsublist-sum-list(nested-list)"Return a list with the sum of each list of numbers in a nested list."(mapcar(lambda(l)(if(every#'numberpl)(reduce#'+l)nil))nested-list))(defunappend-each-sublist(nested-list1nested-list2)"Horizontally append the sublists in two nested lists. Used to add columns."(mapcar#'appendnested-list1nested-list2))(defunnested-list-to-csv(nested-listdelim-string)"Converts the nested list back into a csv-formatted string."(formatnil(concatenate'string"~{~{~2,'0d"delim-string"~}~%~}")nested-list))(defunmain()(let*((csvfile-path#p"projekte/common-lisp/example_comma_csv.txt")(result-path#p"results.txt")(data-list(csvfile-to-nested-listcsvfile-path#\,))(list-of-sums(sublist-sum-listdata-list))(result-header"C1,C2,C3,C4,C5,SUM"))(setfdata-list; add list of sums as additional column(rest; remove old header(append-each-sublistdata-list(mapcar#'listlist-of-sums))));; write to output-file(with-open-file(outputresult-path:direction:output:if-exists:supersede)(formatoutput"~a~%~a"result-header(nested-list-to-csvdata-list",")))))(main)
results.txt):C1,C2,C3,C4,C5,SUM01,05,09,13,17,45,02,06,10,14,18,50,03,07,11,15,19,55,04,08,12,16,20,60,
voidmain(){importstd.stdio,std.csv,std.file,std.typecons,std.array,std.algorithm,std.conv,std.range;autorows="csv_data_in.csv".File.byLine;autofout="csv_data_out.csv".File("w");fout.writeln(rows.front);fout.writef("%(%(%d,%)\n%)",rows.dropOne.map!(r=>r.csvReader!int.front.map!(x=>x+1)));}
csv_data_out.csv):C1,C2,C3,C4,C52,6,10,14,183,7,11,15,194,8,12,16,205,9,13,17,21
programCSV_data_manipulation;{$APPTYPE CONSOLE}usesSystem.SysUtils,System.IoUtils,System.Types;typeTStringDynArrayHelper=recordhelperforTStringDynArrayfunctionSum:Integer;end;{ TStringDynArrayHelper }functionTStringDynArrayHelper.Sum:Integer;varvalue:string;beginResult:=0;forvalueinselfdoResult:=Result+StrToIntDef(value,0);end;constFILENAME='./Data.csv';vari:integer;Input,Row:TStringDynArray;beginInput:=TFile.ReadAllLines(FILENAME);fori:=0toHigh(Input)dobeginifi=0thenInput[i]:=Input[i]+',SUM'elsebeginRow:=Input[i].Split([',']);Input[i]:=Input[i]+','+row.Sum.ToString;end;end;TFile.WriteAllLines(FILENAME,Input);end.
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
DuckDB has several functions that make it easy to import CSV files.For example, read_csv_auto() will infer column headers and typesfrom the data:
createorreplacetablecsvasfromread_csv_auto('rc-csv-data-manipulation.csv');#Viewthefirstrowofthetable:fromcsvlimit1;
┌───────┬───────┬───────┬───────┬───────┐│ C1 │ C2 │ C3 │ C4 │ C5 ││ int64 │ int64 │ int64 │ int64 │ int64 │├───────┼───────┼───────┼───────┼───────┤│ 1 │ 5 │ 9 │ 13 │ 17 │└───────┴───────┴───────┴───────┴───────┘
Writing out the data to a CSV file with an added column can be done inone step if desired:
copy(select*,(C1+C2+C3+C4+C5)assumfromcsv)to'junk.csv';
Or the table itself can be modified, and then exported as a CSV file:
ALTERTABLEcsvADDCOLUMNsuminteger;UPDATEcsvSETsum=C1+C2+C3+C4+C5;copycsvto'junk2.csv';
s$ = inputprint s$ & ",SUM"repeat s$ = input until s$ = "" sum = 0 for v in number strsplit s$ "," sum += v . print s$ & "," & sum.input_dataC1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
;; CSV -> LISTS(define(csv->rowline)(map(lambda(x)(or(string->numberx)x))(string-splitline",")))(define(csv->tablecsv)(mapcsv->row(string-splitcsv"\n")));; LISTS -> CSV(define(row->csvrow)(string-joinrow","))(define(table->csvheaderrows)(string-join(cons(row->csvheader)(for/list((rowrows))(row->csvrow)))"\n"))(define(taskfile)(let*((table(csv->tablefile))(header(firsttable))(rows(resttable)))(table->csv(appendheader"SUM");; add last column(for/list((rowrows))(appendrow(apply+row))))))
(definefile.csv#<<C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20>>#)(taskfile.csv)→"C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60"
// Assumes a CSV file exists and has been sprayed to a Thor clusterMyFileLayout := RECORDSTRING Field1;STRING Field2;STRING Field3;STRING Field4;STRING Field5;END;MyDataset := DATASET ('~Rosetta::myCSVFile', MyFileLayout,CSV(SEPARATOR(',')));MyFileLayout Appended(MyFileLayout pInput):= TRANSFORM SELF.Field1 := pInput.Field1 +'x'; SELF.Field2 := pInput.Field2 +'y'; SELF.Field3 := pInput.Field3 +'z'; SELF.Field4 := pInput.Field4 +'a'; SELF.Field5 := pInput.Field5 +'b';END ; MyNewDataset := PROJECT(MyDataset,Appended(LEFT)); OUTPUT(myNewDataset,,'~Rosetta::myNewCSVFile',CSV,OVERWRITE);(contents of Rosetta
C1x,C2y,C3z,C4a,C5b 1x,5y,9z,13a,17b 2x,6y,10z,14a,18b 3x,7y,11z,15a,19b 4x,8y,12z,16a,20b
defmoduleCsvdodefstructheader:"",data:"",separator:","deffrom_file(path)do[header|data]=path|>File.stream!|>Enum.to_list|>Enum.map(&String.trim/1)%Csv{header:header,data:data}enddefsums_of_rows(csv)doEnum.map(csv.data,fn(row)->sum_of_row(row,csv.separator)end)enddefsum_of_row(row,separator)dorow|>String.split(separator)|>Enum.map(&String.to_integer/1)|>Enum.sum|>to_stringenddefappend_column(csv,column_header,column_data)doheader=append_to_row(csv.header,column_header,csv.separator)data=[csv.data,column_data]|>List.zip|>Enum.map(fn({row,value})->append_to_row(row,value,csv.separator)end)%Csv{header:header,data:data}enddefappend_to_row(row,value,separator)dorow<>separator<>valueenddefto_file(csv,path)dobody=Enum.join([csv.header|csv.data],"\n")File.write(path,body)endendcsv=Csv.from_file("in.csv")csv|>Csv.append_column("SUM",Csv.sums_of_rows(csv))|>Csv.to_file("out.csv")
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
-module(csv_data).-export([change/2,from_binary/1,from_file/1,into_file/2,task/0]).change(CSV,Changes)->lists:foldl(funchange_foldl/2,CSV,Changes).from_binary(Binary)->Lines=binary:split(Binary,<<"\n">>,[global]),[binary:split(X,<<",">>,[global])||X<-Lines].from_file(Name)->{ok,Binary}=file:read_file(Name),from_binary(Binary).into_file(Name,CSV)->Binaries=join_binaries([join_binaries(X,<<",">>)||X<-CSV],<<"\n">>),file:write_file(Name,Binaries).task()->CSV=from_file("CSV_file.in"),New_CSV=change(CSV,[{2,3,<<"23">>},{4,4,<<"44">>}]),into_file("CSV_file.out",New_CSV).change_foldl({Row_number,Column_number,New},Acc)->{Row_befores,[Row_columns|Row_afters]}=split(Row_number,Acc),{Column_befores,[_Old|Column_afters]}=split(Column_number,Row_columns),Row_befores++[Column_befores++[New|Column_afters]]++Row_afters.join_binaries(Binaries,Binary)->[_Last|Rest]=lists:reverse(lists:flatten([[X,Binary]||X<-Binaries])),lists:reverse(Rest).split(1,List)->{[],List};split(N,List)->lists:split(N-1,List).
Contents of "CSV_file.out"
C1,C2,C3,C4,C51,5,23,13,172,6,10,14,183,7,11,44,194,8,12,16,20
--- Read CSV file and add columns headed with 'SUM'--- with trace-- trace(0)include get.einclude std/text.efunction split(sequence s, integer c) sequence removables = " \t\n\r\x05\u0234\" " sequence out integer first, delim out = {} first = 1 while first <= length(s) do delim = find_from(c,s,first) if delim = 0 then delim = length(s)+1 end if out = append(out,trim(s[first..delim-1],removables)) first = delim + 1 end while return outend function procedure main() integer fn -- the file number integer fn2 -- the output file number integer e -- the number of lines read object line -- the next line from the file sequence data = {} -- parsed csv data row sequence headerNames = {} -- array saving column names atom sum = 0.0 -- sum for each row sequence var -- holds numerical data read -- First we try to open the file called "data.csv". fn = open("data.csv", "r") if fn = -1 then puts(1, "Can't open data.csv\n") -- abort(); end if -- Then we create an output file for processed data. fn2 = open("newdata.csv", "w") if fn2 = -1 then puts(1, "Can't create newdata.csv\n") end if -- By successfully opening the file we have established that -- the file exists, and open() gives us a file number (or "handle") -- that we can use to perform operations on the file. e = 1 while 1 do line = gets(fn) if atom(line) then exit end if data = split(line, ',') if (e=1) then -- Save the header labels and -- write them to output file. headerNames = data for i=1 to length(headerNames) do printf(fn2, "%s,", {headerNames[i]}) end for printf(fn2, "SUM\n") end if -- Run a sum for the numerical data. if (e >= 2) then for i=1 to length(data) do printf(fn2, "%s,", {data[i]})var = value(data[i])if var[1] = 0 then -- data read is numerical -- add to sum sum = sum + var[2]end if end for printf(fn2, "%g\n", {sum}) sum = 0.0 end if e = e + 1 end while close(fn) close(fn2)end proceduremain()Contents of "newdata.csv"
C1C2C3C4C5SUM159131745261014185037111519554812162060
openSystem.IO[<EntryPoint>]letmain_=letinput=File.ReadAllLines"test_in.csv"letoutput=input|>Array.mapi(funiline->ifi=0thenline+",SUM"elseletsum=Array.sumByint(line.Split(','))sprintf"%s,%i"linesum)File.WriteAllLines("test_out.csv",output)0
Column0,C2,C3,C4,C5,SUM1,100,9,13,17,1402,6,200,14,18,2403,7,11,300,19,3404,8,12,16,400,440
Thecsv vocabulary provides words for working with csv files, strings, and streams.
USING:csvio.encodings.utf8kernelmath.parsersequences;IN:rosetta-code.csv-manipulation:append-sum(seq--seq')dup[string>number]map-sumnumber>stringsuffix;:csv-sums(seq--seq')[0=["SUM"suffix][append-sum]if]map-index;"example.csv"utf8[file>csvcsv-sums][csv>file]2bi
Contents ofexample.csv
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
\ csvsum.fs Add a new column named SUM that contain sums from rows of CommaSeparatedValues\ USAGE:\ gforth-fast csvsum.fs -e "stdout stdin csvsum bye" <input.csv >output.csvCHAR,CONSTANTSEPARATOR3CONSTANTDECIMALS1E1DECIMALSS>DD>FF**FCONSTANTFSCALE:colsum( ca u -- F: -- sum ;return SUM from CSV-string )0E0OVERSWAPBOUNDS?DO( a )IC@SEPARATOR=IF( a )ITUCKOVER->FLOATIFF+THEN1+THENLOOPDROP;:f>string( -- ca u F: x -- )FSCALEF*F>DTUCKDABS<#DECIMALS0DO#LOOP[CHAR].HOLD#SROTSIGN#>;:rowC!+( offs char -- u+1 ;store CHAR at here+OFFS,increment offset )OVERHERE+C!1+;:row$!+( offs ca u -- offs+u ;store STRING at here+OFFS,update offset )ROT2DUP+>RHERE+SWAPMOVER>;\ If run program with '-m 4G'option, we have practically 4G to store a row:csvsum( fo fi -- ;write into FILEID-OUTPUT processed input from FILEID-INPUT )2DUPHEREUNUSEDROTREAD-LINETHROWIF( fo fi fo u )HERESWAP( fo fi fo ca u )SEPARATORrowC!+s\"SUM"row$!+( fo fi fo ca u' )ROTWRITE-LINETHROWBEGIN( fo fi )2DUPHEREUNUSEDROTREAD-LINETHROWWHILE( fo fi fo u )HERESWAP( fo fi fo ca u )SEPARATORrowC!+HEREOVERcolsumf>string( fo fi fo ca u ca' u' )row$!+( fo fi fo ca u'+u )ROTWRITE-LINETHROWREPEATTHEN2DROP2DROP;
C1,C2,C3,C4,C5,SUM1,5,9,13,17,45.0002,6,10,14,18,50.0003,7,11,15,19,55.0004,8,12,16,20,60.000
It's fairly easy to read arbitrary lines using allocatable character strings, available since Fortran 2003.
programrowsumimplicit nonecharacter(:),allocatable::line,name,a(:)character(20)::fmtdouble precision,allocatable::v(:)integer::n,nrow,ncol,icallget_command_argument(1,length=n)allocate(character(n)::name)callget_command_argument(1,name)open(unit=10,file=name,action="read",form="formatted",access="stream")deallocate(name)callget_command_argument(2,length=n)allocate(character(n)::name)callget_command_argument(2,name)open(unit=11,file=name,action="write",form="formatted",access="stream")deallocate(name)nrow=0ncol=0do while(readline(10,line))nrow=nrow+1callsplit(line,a)if(nrow==1)thenncol=size(a)write(11,"(A)",advance="no")linewrite(11,"(A)")",Sum"allocate(v(ncol+1))write(fmt,"('(',G0,'(G0,:,''',A,'''))')")ncol+1,","else if(size(a)/=ncol)then print"(A,' ',G0)","Invalid number of values on row",nrowstop end if doi=1,ncolread(a(i),*)v(i)end dov(ncol+1)=sum(v(1:ncol))write(11,fmt)vend if end do close(10)close(11)contains functionreadline(unit,line)useiso_fortran_envlogical::readlineinteger::unit,ios,ncharacter(:),allocatable::linecharacter(10)::bufferline=""readline=.false.do read(unit,"(A)",advance="no",size=n,iostat=ios)bufferif(ios==iostat_end)returnreadline=.true.line=line//buffer(1:n)if(ios==iostat_eor)return end do end function subroutinesplit(line,array,separator)character(*)linecharacter(:),allocatable::array(:)character,optional::separatorcharacter::sepinteger::n,m,p,i,kif(present(separator))thensep=separatorelsesep=","end ifn=len(line)m=0p=1k=1doi=1,nif(line(i:i)==sep)thenp=p+1m=max(m,i-k)k=i+1end if end dom=max(m,n-k+1)if(allocated(array))deallocate(array)allocate(character(m)::array(p))p=1k=1doi=1,nif(line(i:i)==sep)then array(p)=line(k:i-1)p=p+1k=i+1end if end do array(p)=line(k:n)end subroutineend program
This task in general soon becomes messy, especially when texts instead of numbers are amongst the input possibilities. Primarily, what is the upper bound on the number of values to a line? Will all lines have the same number of values; if not, is this an error? Do the data have special types (such as calendar dates?) or are all simple numbers? Very soon it becomes better to read a record as text and scan the text: problems can then be dealt with and good complaints made. So, how long is the longest record? Only the B6700 filesystem would make available a file's maximum record length: others offer no help. I have confronted a singularly witless format for supplying electricity data that would write up to an entire year's worth of half-hourly values to one line though it might be used to write just a few day's worth of data also. The header line specified the date and time slot for each column asCountry,Island,Node,MEAN Energy,01AUG2010 Daily ENERGY,01AUG2010 01,01AUG2010 02,01AUG2010 03,etc. so all-in-all it was less trouble to specify CHARACTER*246810 for the input record scratchpad so as not to have to struggle with piecemeal input. More generally, I have a routine for reading this sort of data that, provoked by multiple methods of specifying dates and times, is 1,500 lines long...
Rather than present a monster, what follows is a simple programme that employs the Fortran free-format (or "list-directed") input protocol, applied to the example data. Each READ statement reads an arbitrary number of input records until its input list is satisfied. In this case, notably, every line has five values, and five values are requested in each READ. Instead of numbers the first line presents texts that are column headings and, happily, the free-format input reader will parse the example headings as desired, provided that the read statement's input list is of CHARACTER type, not a number type. Commas are used as as delimiters, and the READ statement accepts that,but also spaces; fortunately no spaces appear within the heading texts. This would be the first reason for writing a data-scanning system. It also accepts quoted texts (and also, allows hexadecimal/octal/binary values thereby) but this example does not require them. On the other hand, the appearance of a / in an input line marks the end of reading for that line and if the input list demands more data, an additional input record will be sought. Thus, if the input data includes dates expressed as say 6/12/2015 there will be difficulty, and another cause for devising a data-scanning system. The ISO standard of 2015-12-6 would also cause trouble as that would manifest as three numbers, if accepted at all, there being no delimiters (spaces or commas) in the sequence. Thus the / character marks the start of in in-line comment (as ! does in later-style Fortran source code) and is treated as end-of-line. The free-format input scan also recognises boolean values ("T" or "F", without the quotes) and complex numbers presented as(real,imag) pairs, and also repetition counts as in24*6 meaning twenty-four occurrences of the value six, alas not using the @-symbol. Opportunities escalate still further with NAMELIST style I/O (allowingname =value(s)), so considerable flexibility is available. However, the input statement's list of variables must have the correct type for the input data (orvice-versa) and the counting of the number of values requested and the number supplied must be correct too, lest everything get out of step. These are further reasons for writing a data-scanning system, but for the simple example, the standard features suffice. The idea is that these features suffice for getting data into a programme without much trouble, so that programming time can be devoted to what is to be done with the data, not how they are to be read.
So much for the header. The data lines can be read as numbers without difficulty, as they are numbers, so all that is necessary is that the input list be of type number. Chosen to be integer here, to match the example.
Output presents some worries, the usual one being how long is a piece of string? Unlike a number, which can be read as input with arbitrary size (0007 or 7, or 7.000000, etc.) character sequences are not translated into a number. Until there is provision for reading a string of arbitrary length, which will be stored by the READ statement as a string of an arbitrary length as encountered, an upper bound must be specified, and for this problem, six will do. For output, the special function TRIM removes trailing spaces, but is an intrinsic function only for F90 and later. Alas, it does not work for an array span, only a specific element at a time, so an implicit DO-loop is needed in the WRITE statement. Text literals in a FORMAT statement are rolled forth until such time as there is an edit code for a list item for which the output list is already exhausted. This would normally mean that the last datum written would be followed by a comma (from the text literal ",") however the mysterious colon in the FORMAT statement (instead of the more usual comma) means that text literals following it arenot to be rolled forth unless a value from the output list is still waiting for its opportunity. One could instead use 666(",",I0) to omit the trailing comma but now there would be a leading comma. So, ... I0,666(",",I0) would avoid the leading comma, except if there was only one value to send forth, there would still be a trailing comma...
Another F90 feature is the SUM function that adds the elements of an array span. Even though for the example the first column looks rather like a record number, all five columns will be added, but otherwise the statement would be SUM(X(2:N)). Other modifications can be made without much difficulty, if desired. The output format is I0 rather than say I2, as it provides only the needed number of characters to present the integer's value. There is no corresponding F format code, and free-format output would roll out many spaces as padding in case of large numbers, that are not present here. It would be needed for a more general solution, but for this example, I0 will do.
Copiesafilewith5comma-separatedvaluestoaline,appendingacolumnholdingtheirsum.INTEGERN!Instead of littering the source with "5"PARAMETER(N=5)!Provide some provenance.CHARACTER*6HEAD(N)!A perfect size?INTEGERX(N)!Integers suffice.INTEGERLINPR,IN!I/O unit numbers.LINPR=6!Standard output via this unit number.IN=10!Some unit number for the input file.OPEN(IN,FILE="CSVtest.csv",STATUS="OLD",ACTION="READ")!For formatted input.READ(IN,*)HEAD!The first line has texts as column headings.WRITE(LINPR,1)(TRIM(HEAD(I)),I=1,N),"Sum"!Append a "Sum" column.1FORMAT(666(A:","))!The : sez "stop if no list element awaits".2READ(IN,*,END=10)X!Read a line's worth of numbers, separated by commas or spaces.WRITE(LINPR,3)X,SUM(X)!Write, with a total appended.3FORMAT(666(I0:","))!I0 editing uses only as many columns as are needed.GOTO2!Do it again.10CLOSE(IN)!All done.END!That's all.
Output could of course be written to a disc file instead of a screen, but here it is:
C1,C2,C3,C4,C5,Sum1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
' FB 1.05.0 Win64Open"manip.csv"ForInputAs#1' existing CSV fileOpen"manip2.csv"ForOutputAs#2' new CSV file for writing changed dataDimheaderAsStringLineInput#1,headerheader+=",SUM"Print#2,headerDimAsIntegerc1,c2,c3,c4,c5,sumWhileNotEof(1)Input#1,c1,c2,c3,c4,c5sum=c1+c2+c3+c4+c5Write#2,c1,c2,c3,c4,c5,sumWendClose#1Close#2
' contents of manip2.csvC1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
import io.{lines, PrintWriter}data Table( header, rows )def read( file ) = l = lines( file ) def next = vector( l.next().split(',') ) if l.isEmpty() then return Table( vector(), [] ) header = next() rows = seq() while l.hasNext() rows += next() Table( header, rows.toList() ) def write( table, out ) = w = if out is String then PrintWriter( out ) else out w.println( table.header.mkString(',') ) for r <- table.rows w.println( r.mkString(',') ) if out is String w.close() def updateRow( header, row, updates ) = r = dict( (header(i), row(i)) | i <- 0:header.length() ) updates( r ) vector( r(f) | f <- header )def update( table, updates ) = Table( table.header, (updateRow(table.header, r, updates) | r <- table.rows).toList() ) def addColumn( table, column, updates ) = Table( table.header + [column], (updateRow(table.header + [column], r + [null], updates) | r <- table.rows).toList() )t = addColumn( read('test.csv'), 'SUM', r -> r('SUM') = sum(int(v) | (_, v) <- r if v != null) )write( t, 'test_out.csv' )write( t, System.out )C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
This Rosetta Code task calls for the use of the following CSV file:
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
While this file has column headers, it lacks row identifiers. The code below adds the missing row IDs. A screenshot of the output CSV file is shown as it appears when it's opened in the macOS Numbers spreadsheet application. An extra AVG column has been added which includes an average of the numbers in each respective row. It only required a single line of code.
include "NSLog.incl"include resources "rosetta_csv.csv"/*This ASCII text data is saved as a resource filenamed "rosetta_csv.csv" in the application bundle.C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20*/void local fn ManipulateCSV CFURLRef url = fn BundleURLForResource( fn BundleMain, @"rosetta_csv", @"csv", NULL ) CFStringRef csvString = fn StringWithContentsOfURL( url, NSUTF8StringEncoding, NULL ) CFArrayRef csvArray = fn StringComponentsSeparatedByCharactersInSet( csvString, fn CharacterSetNewlineSet ) CFMutableStringRef mutStr = fn MutableStringWithCapacity(0) long i MutableStringAppendFormat( mutStr, @",%@,SUM,AVG\n", csvArray[0] ) for i = 1 to len(csvArray) - 1 CFArrayRef nums = fn StringComponentsSeparatedByString( csvArray[i], @"," ) CFNumberRef sum = fn ObjectValueForKeyPath( nums, @"@sum.self" ) CFNumberRef avg = fn ObjectValueForKeyPath( nums, @"@avg.self" ) MutableStringAppendFormat( mutStr, @"R%ld,%@,%@,%@\n",i,csvArray[i],sum,avg ) next NSLog( @"%@", mutStr ) CFURLRef desktopURL = fn FileManagerURLForDirectory( NSDesktopDirectory, NSUserDomainMask ) url = fn URLByAppendingPathComponent( desktopURL, @"final_csv.csv" ) fn StringWriteToURL( mutStr, url, YES, NSUTF8StringEncoding, NULL )end fnfn ManipulateCSVHandleEvents
PublicSubForm_Open()DimsDataAsString=File.Load("data.csv")DimsLine,sTempAsStringDimsOutputAsNewString[]DimsiCountAsShortDimbLine1AsBooleanForEachsLineInSplit(sData,gb.NewLine)IfNotbLine1ThensLine&=",SUM"sOutput.Add(sLine)bLine1=TrueContinueEndIfForEachsTempInSplit(sLine)siCount+=Val(sTemp)NextsOutput.Add(sLine&","&Str(siCount))siCount=0NextsData=""ForEachsTempInsOutputsData&=sTemp&gb.NewLinePrintsTemp;PrintNextFile.Save(User.home&/"CSVData.csv",sData)End
Output:
C1, C2, C3, C4, C5,SUM1, 5, 9, 13, 17,452, 6, 10, 14, 18,503, 7, 11, 15, 19,554, 8, 12, 16, 20,60
packagemainimport("encoding/csv""log""os""strconv")funcmain(){rows:=readSample()appendSum(rows)writeChanges(rows)}funcreadSample()[][]string{f,err:=os.Open("sample.csv")iferr!=nil{log.Fatal(err)}rows,err:=csv.NewReader(f).ReadAll()f.Close()iferr!=nil{log.Fatal(err)}returnrows}funcappendSum(rows[][]string){rows[0]=append(rows[0],"SUM")fori:=1;i<len(rows);i++{rows[i]=append(rows[i],sum(rows[i]))}}funcsum(row[]string)string{sum:=0for_,s:=rangerow{x,err:=strconv.Atoi(s)iferr!=nil{return"NA"}sum+=x}returnstrconv.Itoa(sum)}funcwriteChanges(rows[][]string){f,err:=os.Create("output.csv")iferr!=nil{log.Fatal(err)}err=csv.NewWriter(f).WriteAll(rows)f.Close()iferr!=nil{log.Fatal(err)}}
C1,C2,C3,C4,C51,5,9,13,172,six,10,14,183,7,11,15,194,8,12,16,20
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,six,10,14,18,NA3,7,11,15,19,554,8,12,16,20,60
defcsv=[]defloadCsv={source->source.splitEachLine(/,/){csv<<it.collect{it}}}defsaveCsv={target->target.withWriter{writer->csv.each{writer.printlnit.join(',')}}}loadCsvnewFile('csv.txt')csv[0][0]='Column0'(1..4).each{i->csv[i][i]=i*100}saveCsvnewFile('csv_out.txt')
csv_out.txt:
Column0,C2,C3,C4,C51,100,9,13,172,6,200,14,183,7,11,300,194,8,12,16,400
Solution 1Array-based solution:
importData.Array(Array(..),(//),bounds,elems,listArray)importData.List(intercalate)importControl.Monad(when)importData.Maybe(isJust)delimiters::Stringdelimiters=",;:"fields::String->[String]fields[]=[]fieldsxs=let(item,rest)=break(`elem`delimiters)xs(_,next)=break(`notElem`delimiters)restinitem:fieldsnextunfields::Maybe(Array(Int,Int)String)->[String]unfieldsNothing=[]unfields(Justa)=everyfieldNumber$elemsawhere((_,_),(_,fieldNumber))=boundsaevery_[]=[]everynxs=let(y,z)=splitAtnxsinintercalate","y:everynzfieldArray::[[String]]->Maybe(Array(Int,Int)String)fieldArray[]=NothingfieldArrayxs=Just$listArray((1,1),(lengthxs,length$headxs))$concatxsfieldsFromFile::FilePath->IO(Maybe(Array(Int,Int)String))fieldsFromFile=fmap(fieldArray.mapfields.lines).readFilefieldsToFile::FilePath->Maybe(Array(Int,Int)String)->IO()fieldsToFilef=writeFilef.unlines.unfieldssomeChanges::Maybe(Array(Int,Int)String)->Maybe(Array(Int,Int)String)someChanges=fmap(//[((1,1),"changed"),((3,4),"altered"),((5,2),"modified")])main::IO()main=doa<-fieldsFromFile"example.txt"when(isJusta)$fieldsToFile"output.txt"$someChangesa
Solution 2List-based solution, heavily using functors and lenses
{-# LANGUAGE FlexibleContexts, TypeFamilies, NoMonomorphismRestriction #-}importData.List(intercalate)importData.List.Split(splitOn)importLens.Micro(<$$>)::(Functorf1,Functorf2)=>(a->b)->f1(f2a)->f1(f2b)(<$$>)=fmap.fmap-------------------------------------------------------------- reading and writingnewtypeCSV=CSV{values::[[String]]}readCSV::String->CSVreadCSV=CSV.(splitOn","<$$>lines)instanceShowCSVwhereshow=unlines.map(intercalate",").values-------------------------------------------------------------- construction and combinationmkColumn,mkRow::[String]->CSV(<||>),(<==>)::CSV->CSV->CSVmkColumnlst=CSV$sequence[lst]mkRowlst=CSV[lst]CSVt1<||>CSVt2=CSV$zipWith(++)t1t2CSVt1<==>CSVt2=CSV$t1++t2-------------------------------------------------------------- access and modification via lensestable=lensvalues(\csvt->csv{values=t})rowi=table.ixi.traversecoli=table.traverse.ixiitemij=table.ixi.ixj------------------------------------------------------------sample=readCSV"C1, C2, C3, C4, C5\n\\1, 5, 9, 13, 17\n\\2, 6, 10, 14, 18\n\\3, 7, 11, 15, 19\n\\4, 8, 12, 16, 20"
Examples:
1. Reading from a file
λ> readCSV <$> readFile "example.csv"C1, C2, C3, C4, C51, 5, 9, 13, 172, 6, 10, 14, 183, 7, 11, 15, 194, 8, 12, 16, 20
2. Access and modification
λ> sample ^. item 2 3"14"λ> sample ^.. row 2["2","6","10","14","18"]λ> sample ^.. col 2["C3","9","10","11","12"]λ> (item 3 2 .~ "Ok") sampleC1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,Ok,15,194,8,12,16,20λ> (item 3 2 %~ (show.(^2).read)) sampleC1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,121,15,194,8,12,16,20λ> (row 4 %~ (show.(^2).read)) sampleC1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,1916,64,144,256,400λ> (col 4 %~ (++"!")) sampleC1,C2,C3,C4,C5!1,5,9,13,17!2,6,10,14,18!3,7,11,15,19!4,8,12,16,20!
3. Construction and combination
sampleSum=sample<||>(mkRow["SUM"]<==>mkColumnsums)wheresums=map(show.sum)(read<$$>drop1(valuessample))
λ> sampleSumC1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
This version only works in Unicon, but can be easily adapted to work in Icon.
importUtils# To get CSV proceduresproceduremain(A)f:=open(A[1])|&inputi:=1write(!f)# header line(?)everycsv:=parseCSV(!f)do{csv[i+:=1]*:=100write(encodeCSV(csv))}end
Sample run:
->csv csv.datC1,C2,C3,C4,C51,500,9,13,172,6,1000,14,183,7,11,1500,194,8,12,16,2000->
Like other languages it is not necessary to use the csv utilities to accomplish this task.
data=:(','&splitstring);.2freads'rc_csv.csv'NB. read and parse datadata=:(<'"spam"')(<23)}dataNB. amend cell in 3rd row, 4th column (0-indexing)'rc_outcsv.csv'fwrites~;<@(','&joinstring"1)dataNB. format and write out amended data
Using thedelimiter-separated-values utilities (of whichtables/csv is a special case) will handle more complex csv constructs:
require'tables/csv'data=:makenumreadcsv'rc_csv.csv'NB. read data and convert cells to numeric where possibledata=:(<'spam')(23;30)}dataNB. amend 2 cellsdatawritecsv'rc_outcsv.csv'NB. write out amended data. Strings are double-quoted
Adding a column with the sum of the rows:
require'tables/csv''hdr data'=:splitreadcsv'rc_csv.csv'NB. read data, split the header & datahdr=:hdr,<'SUM'NB. add title for extra column to headerdata=:<"0(,.+/"1)makenumdataNB. convert to numeric, sum rows & append column(hdr,data)writecsv'rc_out.csv'
Tacit version of above:
sumCSVrows=:writecsv~(((<'SUM'),~{.),[:(<"0)@(,.+/"1)makenum@}.)@readcsv'rc_out.csv'sumCSVrows'rc.csv'
importjava.io.BufferedReader;importjava.io.File;importjava.io.FileReader;importjava.io.FileWriter;importjava.io.IOException;importjava.util.ArrayList;importjava.util.List;
publicclassCSV{publicstaticvoidmain(String[]args)throwsIOException{CSVcsv=newCSV("data.csv");csv.sumAllRows();csv.write();}privatefinalFilefile;privateList<List<String>>data;publicCSV(Filefile)throwsIOException{this.file=file;open();}/* convenience constructor */publicCSV(Stringpath)throwsIOException{this(newFile(path));}publicvoidsumAllRows(){appendColumn("SUM");intsum;intlength;for(intindex=1;index<data.size();index++){sum=sum(data.get(index));length=data.get(index).size();data.get(index).set(length-1,String.valueOf(sum));}}privateintsum(List<String>row){intsum=0;for(intindex=0;index<row.size()-1;index++)sum+=Integer.parseInt(row.get(index));returnsum;}privatevoidappendColumn(Stringtitle){List<String>titles=data.get(0);titles.add(title);/* append an empty cell to each row */for(intindex=1;index<data.size();index++)data.get(index).add("");}privatevoidopen()throwsIOException{try(BufferedReaderreader=newBufferedReader(newFileReader(file))){data=newArrayList<>();Stringline;while((line=reader.readLine())!=null){/* using a limit of -1 will preserve trailing commas */data.add(newArrayList<>(List.of(line.split(",",-1))));}}}publicvoidwrite()throwsIOException{try(FileWriterwriter=newFileWriter(file)){Stringnewline=System.lineSeparator();for(List<String>row:data){writer.write(String.join(",",row));writer.write(newline);}writer.flush();}}}
First iteration
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Second iteration
C1,C2,C3,C4,C5,SUM,SUM1,5,9,13,17,45,902,6,10,14,18,50,1003,7,11,15,19,55,1104,8,12,16,20,60,120
importjava.io.*;importjava.awt.Point;importjava.util.HashMap;importjava.util.Scanner;publicclassCSV{privateHashMap<Point,String>_map=newHashMap<Point,String>();privateint_cols;privateint_rows;publicvoidopen(Filefile)throwsFileNotFoundException,IOException{open(file,',');}publicvoidopen(Filefile,chardelimiter)throwsFileNotFoundException,IOException{Scannerscanner=newScanner(file);scanner.useDelimiter(Character.toString(delimiter));clear();while(scanner.hasNextLine()){String[]values=scanner.nextLine().split(Character.toString(delimiter));intcol=0;for(Stringvalue:values){_map.put(newPoint(col,_rows),value);_cols=Math.max(_cols,++col);}_rows++;}scanner.close();}publicvoidsave(Filefile)throwsIOException{save(file,',');}publicvoidsave(Filefile,chardelimiter)throwsIOException{FileWriterfw=newFileWriter(file);BufferedWriterbw=newBufferedWriter(fw);for(introw=0;row<_rows;row++){for(intcol=0;col<_cols;col++){Pointkey=newPoint(col,row);if(_map.containsKey(key)){bw.write(_map.get(key));}if((col+1)<_cols){bw.write(delimiter);}}bw.newLine();}bw.flush();bw.close();}publicStringget(intcol,introw){Stringval="";Pointkey=newPoint(col,row);if(_map.containsKey(key)){val=_map.get(key);}returnval;}publicvoidput(intcol,introw,Stringvalue){_map.put(newPoint(col,row),value);_cols=Math.max(_cols,col+1);_rows=Math.max(_rows,row+1);}publicvoidclear(){_map.clear();_cols=0;_rows=0;}publicintrows(){return_rows;}publicintcols(){return_cols;}publicstaticvoidmain(String[]args){try{CSVcsv=newCSV();csv.open(newFile("test_in.csv"));csv.put(0,0,"Column0");csv.put(1,1,"100");csv.put(2,2,"200");csv.put(3,3,"300");csv.put(4,4,"400");csv.save(newFile("test_out.csv"));}catch(Exceptione){}}}
Column0,C2,C3,C4,C51,100,9,13,172,6,200,14,183,7,11,300,194,8,12,16,400
Using theApache commons-csv library.
importjava.io.*;importjava.util.*;importorg.apache.commons.csv.*;publicclassRCsv{privatestaticfinalStringNL=System.getProperty("line.separator");privatestaticfinalStringFILENAME_IR="data/csvtest_in.csv";privatestaticfinalStringFILENAME_OR="data/csvtest_sum.csv";privatestaticfinalStringCOL_NAME_SUM="SUM, \"integers\"";// demonstrate white space, comma & quote handlingpublicstaticvoidmain(String[]args){ReaderiCvs=null;WriteroCvs=null;System.out.println(textFileContentsToString(FILENAME_IR));try{iCvs=newBufferedReader(newFileReader(FILENAME_IR));oCvs=newBufferedWriter(newFileWriter(FILENAME_OR));processCsv(iCvs,oCvs);}catch(IOExceptionex){ex.printStackTrace();}finally{try{if(iCvs!=null){iCvs.close();}if(oCvs!=null){oCvs.close();}}catch(IOExceptionex){ex.printStackTrace();}}System.out.println(textFileContentsToString(FILENAME_OR));return;}publicstaticvoidprocessCsv(ReaderiCvs,WriteroCvs)throwsIOException{CSVPrinterprinter=null;try{printer=newCSVPrinter(oCvs,CSVFormat.DEFAULT.withRecordSeparator(NL));List<String>oCvsHeaders;List<String>oCvsRecord;CSVParserrecords=CSVFormat.DEFAULT.withHeader().parse(iCvs);Map<String,Integer>irHeader=records.getHeaderMap();oCvsHeaders=newArrayList<String>(Arrays.asList((irHeader.keySet()).toArray(newString[0])));oCvsHeaders.add(COL_NAME_SUM);printer.printRecord(oCvsHeaders);for(CSVRecordrecord:records){oCvsRecord=record2list(record,oCvsHeaders);printer.printRecord(oCvsRecord);}}finally{if(printer!=null){printer.close();}}return;}privatestaticList<String>record2list(CSVRecordrecord,List<String>oCvsHeaders){List<String>cvsRecord;Map<String,String>rMap=record.toMap();longrecNo=record.getRecordNumber();rMap=alterRecord(rMap,recNo);intsum=0;sum=summation(rMap);rMap.put(COL_NAME_SUM,String.valueOf(sum));cvsRecord=newArrayList<String>();for(Stringkey:oCvsHeaders){cvsRecord.add(rMap.get(key));}returncvsRecord;}privatestaticMap<String,String>alterRecord(Map<String,String>rMap,longrecNo){intrv;Randomrg=newRandom(recNo);rv=rg.nextInt(50);String[]ks=rMap.keySet().toArray(newString[0]);intix=rg.nextInt(ks.length);longyv=0;Stringky=ks[ix];Stringxv=rMap.get(ky);if(xv!=null&&xv.length()>0){yv=Long.valueOf(xv)+rv;rMap.put(ks[ix],String.valueOf(yv));}returnrMap;}privatestaticintsummation(Map<String,String>rMap){intsum=0;for(Stringcol:rMap.keySet()){Stringnv=rMap.get(col);sum+=nv!=null&&nv.length()>0?Integer.valueOf(nv):0;}returnsum;}privatestaticStringtextFileContentsToString(Stringfilename){StringBuilderlineOut=newStringBuilder();Scannerfs=null;try{fs=newScanner(newFile(filename));lineOut.append(filename);lineOut.append(NL);while(fs.hasNextLine()){Stringline=fs.nextLine();lineOut.append(line);lineOut.append(NL);}}catch(FileNotFoundExceptionex){// TODO Auto-generated catch blockex.printStackTrace();}finally{if(fs!=null){fs.close();}}returnlineOut.toString();}}
data/csvtest_in.csv
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
data/csvtest_sum.csv
C1,C2,C3,C4,C5,"SUM, ""integers"""1,5,9,21,17,5336,6,10,14,18,843,7,11,27,19,674,8,12,53,20,97
Using theuniVocity-parsers library.
publicstaticvoidmain(String[]args)throwsIOException{// 1st, config the CSV reader with line separatorCsvParserSettingssettings=newCsvParserSettings();settings.getFormat().setLineSeparator("\n");// 2nd, config the CSV reader with row processor attaching the bean definitionBeanListProcessor<Employee>rowProcessor=newBeanListProcessor<Employee>(Employee.class);settings.setRowProcessor(rowProcessor);// 3rd, creates a CSV parser with the configsCsvParserparser=newCsvParser(settings);// 4th, parse all rows from the CSF file into the list of beans you definedparser.parse(newFileReader("/examples/employees.csv"));List<Employee>resolvedBeans=rowProcessor.getBeans();// 5th, Store, Delete duplicates, Re-arrange the words in specific order// ......// 6th, Write the listed of processed employee beans out to a CSV file.CsvWriterSettingswriterSettings=newCsvWriterSettings();// 6.1 Creates a BeanWriterProcessor that handles annotated fields in the Employee class.writerSettings.setRowWriterProcessor(newBeanWriterProcessor<Employee>(Employee.class));// 6.2 persistent the employee beans to a CSV file.CsvWriterwriter=newCsvWriter(newFileWriter("/examples/processed_employees.csv"),writerSettings);writer.processRecords(resolvedBeans);writer.writeRows(newArrayList<List<Object>>());}
As an embedded scripting language which evolved in browsers carefully isolated from local file systems, JavaScript has no standard file IO libraries. The readFile() and writeFile() functions used in this example are written for JS embedded in macOS as 'JavaScript for Automation'. Other embeddings will require other definitions of these functions, and in some JS contexts it will not be possible to write them at all.
(function(){'use strict';// splitRegex :: Regex -> String -> [String]functionsplitRegex(rgx,s){returns.split(rgx);}// lines :: String -> [String]functionlines(s){returns.split(/[\r\n]/);}// unlines :: [String] -> Stringfunctionunlines(xs){returnxs.join('\n');}// macOS JavaScript for Automation version of readFile.// Other JS contexts will need a different definition of this function,// and some may have no access to the local file system at all.// readFile :: FilePath -> maybe StringfunctionreadFile(strPath){varerror=$(),str=ObjC.unwrap($.NSString.stringWithContentsOfFileEncodingError($(strPath).stringByStandardizingPath,$.NSUTF8StringEncoding,error));returnerror.code?error.localizedDescription:str;}// macOS JavaScript for Automation version of writeFile.// Other JS contexts will need a different definition of this function,// and some may have no access to the local file system at all.// writeFile :: FilePath -> String -> IO ()functionwriteFile(strPath,strText){$.NSString.alloc.initWithUTF8String(strText).writeToFileAtomicallyEncodingError($(strPath).stringByStandardizingPath,false,$.NSUTF8StringEncoding,null);}// EXAMPLE - appending a SUM columnvardelimCSV=/,\s*/g;varstrSummed=unlines(lines(readFile('~/csvSample.txt')).map(function(x,i){varxs=x?splitRegex(delimCSV,x):[];return(xs.length?xs.concat(// 'SUM' appended to first line, others summed.i>0?xs.reduce(function(a,b){returna+parseInt(b,10);},0).toString():'SUM'):[]).join(',');}));return(writeFile('~/csvSampleSummed.txt',strSummed),strSummed);})();
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
We can easily manipulate files with Node.js. Below is a toy example to add new columns to an CSV file. Other manipulations, i.e. adding new rows, modifying existing values and so forth, can be accomplished very easily.
constfs=require('fs');// formats for the data parameter in the function below: {col1: array | function, col2: array | function}functionaddCols(path,data){letcsv=fs.readFileSync(path,'utf8');csv=csv.split('\n').map(line=>line.trim());letcolNames=Object.keys(data);for(leti=0;i<colNames.length;i++){letc=colNames[i];if(typeofdata[c]==='function'){csv=csv.map((line,idx)=>idx===0?line+','+c:line+','+data[c](line,idx));}elseif(Array.isArray(data[c])){csv=csv.map((line,idx)=>idx===0?line+','+c:line+','+data[c][idx-1]);}}fs.createWriteStream(path,{flag:'w',defaultEncoding:'utf8'}).end(csv.join('\n'));}addCols('test.csv',{sum:function(line,idx){lets=0;line=line.split(',').map(d=>+(d.trim()));for(leti=0;i<line.length;i++){s+=line[i];}returns;},id:function(line,idx){returnidx;}});
C1,C2,C3,C4,C5,sum,id1,5,9,13,17,45,12,6,10,14,18,50,23,7,11,15,19,55,34,8,12,16,20,60,4
Works with gojq, the Go implementation of jq.
The following adds a column with header "SUM" as suggested in the task description.It is assumed that the input has a header row.For all other rows, the sum will simply be the last field of each output row.
# Input: a single row# Omit empty rowsdef read_csv: if length>0 then split(",") else empty end ;# Input: an array# Output: the same array but with an additional summation column.# If .[0] is a number, then it is assumed the entire row consists of numbers or numeric strings;# otherwise, 0 is addeddef add_sum: (if .[0] | type == "number" then (map(tonumber) | add) else 0 end) as $sum | . + [$sum] ;# `tocsv` is only needed if fields should only be quoted by necessity:def tocsv: map( if type == "string" and test("[,\"\r\n]") then "\"\(.)\"" else . end ) | join(",");( input | read_csv | . + ["SUM"] | @csv),(inputs | read_csv | add_sum | @csv)Invocation: jq -Rn -r -f CSV_data_manipulation.jq input.csv
If jq's @csv builtin is used as shown in the program listing above, then every value will be quoted as shown here:
"C1","C2","C3","C4","C5","SUM""1","5","9","13","17""2","6","10","14","18""3","7","11","15","19""4","8","12","16","20"
If the calls to `@csv` are replaced by calls to `tocsv`, then the output would be:
C1,C2,C3,C4,C5,SUM1,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
usingDataFrames,CSVifn="csv_data_manipulation_in.dat"ofn="csv_data_manipulation_out.dat"df=CSV.read(ifn,DataFrame)df.SUM=sum.(eachrow(df))CSV.write(ofn,df)
$ cat csv_data_manipulation_out.dat C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
// version 1.1.3importjava.io.Filefunmain(args:Array<String>){vallines=File("example.csv").readLines().toMutableList()lines[0]+=",SUM"for(iin1untillines.size){lines[i]+=","+lines[i].split(',').sumBy{it.toInt()}}valtext=lines.joinToString("\n")File("example2.csv").writeText(text)// write to new fileprintln(text)// print to console}
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
------------------------------------------ Simplified CSV parser (without escape character support etc.).-- First line is interrepted as header with column names.-- @param {string} csvStr-- @param {string} [sep=","] - single char as string-- @param {string} [eol=RETURN]-- @return {propList}----------------------------------------on parseSimpleCSVString (csvStr, sep, eol) if voidP(sep) then sep="," if voidP(eol) then eol = RETURN lines = explode(eol, csvStr) if lines.getLast()="" then lines.deleteAt(lines.count) res = [:] res[#header] = explode(sep, lines[1]) res[#data] = [] cnt = lines.count repeat with i = 2 to cnt res[#data].append(explodeBySingleChar(sep, lines[i])) end repeat return resend------------------------------------------ Simplified CSV creater (without escape character support etc.).-- @param {propList} csvData-- @param {string} [sep=","]-- @param {string} [eol=RETURN]-- @return {string}----------------------------------------on createSimpleCSVString (csvData, sep, eol) if voidP(sep) then sep="," if voidP(eol) then eol = RETURN res = "" put implode(sep, csvData[#header])&eol after res cnt = csvData[#data].count repeat with i = 1 to cnt put implode(sep, csvData[#data][i])&eol after res end repeat return resend------------------------------------------ Explodes string into list-- @param {string} delim-- @param {string} str-- @return {list}----------------------------------------on explode (delim, str) if delim.length=1 then return explodeBySingleChar(delim, str) l = [] if voidP(str) then return l dl = delim.length repeat while true pos = offset(delim, str) if pos=0 then exit repeat l.add(str.char[1..pos-1]) delete char 1 to pos+dl-1 of str end repeat if pos=0 then pos = 1-dl l.add(str.char[pos+dl..str.length]) return lend------------------------------------------ Explode string into list based on single char delimiter-- (uses Lingo's build-in 'item' support, therefor faster)-- @param {string} delim-- @param {string} str-- @return {list}----------------------------------------on explodeBySingleChar (delim, str) l = [] if voidP(str) then return l od = _player.itemDelimiter _player.itemDelimiter = delim cnt = str.item.count repeat with i = 1 to cnt l.add(str.item[i]) end repeat _player.itemDelimiter = od return lend------------------------------------------ Implodes list into string-- @param {string} delim-- @param {list} l-- @return {string}----------------------------------------on implode (delim, l) str = "" cnt = l.count repeat with i = 1 to cnt put l[i]&delim after str end repeat delete char (str.length-delim.length+1) to str.length of str return strendsep = ","eol = numtochar(10)-- load CSV string from filefn = _movie.path & "file.csv"fp = xtra("fileIO").new()fp.openFile(fn, 1)csvStr = fp.readFile()fp.closeFile()-- parse CSV string into propListcsvData = parseSimpleCSVString(csvStr, sep, eol)-- add SUM columncsvData[#header].append("SUM")repeat with row in csvData[#data] sum = 0 repeat with cell in row sum = sum+integer(cell) end repeat row.append(sum)end repeat-- create CSV string from updated propListcsvString = createSimpleCSVString(csvData, sep, eol)-- save CSV string to filefn = _movie.path & "file.csv"fp.openFile(fn, 2)if not fp.status() then fp.delete()fp.createFile(fn)fp.openFile(fn, 2)fp.writeString(csvString)fp.closeFile()-- show the CSV stringput csvString-- "C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60"UCBLogo has no built-in support for generic CSV files.
to csv.data.manipulation :in :out local [header line list sum]openread :insetread :inopenwrite :outsetwrite :outmake "header readword print word :header ",SUMwhile [not eofp] [make "line readwordmake "list parse map [ifelse equalp ? ", ["\ ] [?]] :linemake "sum apply "sum :listprint (word :line "\, :sum)]close :insetread []close :outsetwrite []end
csv.data.manipulation "data.csv "output.csv
Contents of output.csvC1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Simple
-- Lua has no built in methods to handle csv files.-- it does have string.gmatch, which we use to global.match whatever isn't a commaprint(io.read"l"..",SUM")forlineinio.lines()dolocalfields,sum={},0forfieldinline:gmatch"[^,]+"dotable.insert(fields,field)sum=sum+fieldendtable.insert(fields,sum)print(table.concat(fields,","))end
This does as the task asks. opens a file, saves the record into a table and rewrites the file
localcsv={}-- read csv file, save records and fields into tableforlineinio.lines('file.csv')dolocalfields={}forfieldinline:gmatch"[^,]+"dotable.insert(fields,tonumber(field)orfield)endtable.insert(csv,fields)end-- change csv valuestable.insert(csv[1],'SUM')fori=2,#csvdolocalsum=0for_,valinipairs(csv[i])dosum=sum+valendtable.insert(csv[i],sum)end-- SavelocalfileHandler=io.open('file.csv','w')forNR,fieldsinipairs(csv)dofileHandler:write(table.concat(fields,","),"\n")end
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Module Checkit { Function Sum { Long c=0 while not empty { c+=number } =c } Document CSV$ = {C1,C2,C3,C4,C5 1,5,9,13,17 2,6,10,14,18 3,7,11,15,19 4,8,12,16,20 } \\ export encoded to UTF-16LE Save.Doc CSV$, "data1.csv", 0 \\ Open Wide read UTF-16LE \\ use standard colum sep. as "," \\ use standard decimal point char \\ use standard (non json style string) \\ True = use bare strings (without "") Input With "",,,true Write With"",,,true \\ for excel csv use Input With chr$(9),,true, true Open "data1.csv" for Wide Input as #M Open "data2.csv" for Wide Output as #M1 Input #M, h1$, h2$, h3$, h4$, h5$ Write #M1, h1$, h2$, h3$, h4$, h5$ Print h1$, h2$, h3$, h4$, h5$ While not Eof(#M) { Input #M, A1, A2, A3, A4, A5 Write #M1, A1, A2, A3, A4, A5, Sum(A1, A2, A3, A4, A5) Print A1, A2, A3, A4, A5 } close #M1 Close #M Open "data2.csv" for Wide Input as #M Input #M, h1$, h2$, h3$, h4$, h5$ Print h1$, h2$, h3$, h4$, h5$ While not Eof(#M) { Input #M, A1, A2, A3, A4, A5, Sum Print A1, A2, A3, A4, A5, Sum } Close #M}CheckitEntire script:
M := ImportMatrix("data.csv",source=csv);M(..,6) := < "Total", seq( add(M[i,j], j=1..5), i=2..5 ) >;ExportMatrix("data_out.csv",M,target=csv);Running this script showing interactive results:
> M := ImportMatrix("data.csv",source=csv); ["C1" "C2" "C3" "C4" "C5"] [ ] [ 1 5 9 13 17 ] [ ] M := [ 2 6 10 14 18 ] [ ] [ 3 7 11 15 19 ] [ ] [ 4 8 12 16 20 ]> M(..,6) := < "Total", seq( add(M[i,j], j=1..5), i=2..5 ) >; ["C1" "C2" "C3" "C4" "C5" "Total"] [ ] [ 1 5 9 13 17 45 ] [ ] M := [ 2 6 10 14 18 50 ] [ ] [ 3 7 11 15 19 55 ] [ ] [ 4 8 12 16 20 60 ]> ExportMatrix("data_out.csv",M,target=csv); 96Mathematica's Import and Export functions support CSV files.
iCSV=Import["test.csv"]->{{"C1","C2","C3","C4","C5"},{1,5,9,13,17},{2,6,10,14,18},{3,7,11,15,19},{4,8,12,16,20}}iCSV=Transpose@Append[Transpose[iCSV],Join[{"Sum"},Total/@Drop[iCSV,1]]];iCSV//MatrixFormExport["test.csv",iCSV];
(C1C2C3C4C5Sum159131745261014185037111519554812162060)
filename='data.csv';fid=fopen(filename);header=fgetl(fid);fclose(fid);X=dlmread(filename,',',1,0);fid=fopen('data.out.csv','w+');fprintf(fid,'%s,sum\n',header);fork=1:size(X,1),fprintf(fid,"%i,",X(k,:));fprintf(fid,"%i\n",sum(X(k,:)));end;fclose(fid);
tablefilename='data.csv';data=readtable(filename);data.SUM=sum([data{:,:}],2);writetable(data,filename);
Nanoquery was created to parse and manipulate data files, with CSV being the first targeted format. As a result, it has a number of operators for retrieving data from a CSV file, including the record operator '#', the column operator '@', and the lookup operator '~'
def sum(record) sum = 0 for i in range(1, len(record) - 1) sum = sum + int(record ~ i) end for return sumend def open "file.csv"add "SUM" for i in range($dbsize, 1) (i ~ @"SUM") = sum(#i)end for write
Using theApache commons-csv library.
/* NetRexx */optionsreplaceformatcommentsjavacrossrefsymbolsimportorg.apache.commons.csv.--=============================================================================classRCsvpublicfinalpropertiesprivateconstantNL=StringSystem.getProperty("line.separator")COL_NAME_SUM=String'SUM, "integers"'CSV_IFILE='data/csvtest_in.csv'CSV_OFILE='data/csvtest_sumRexx.csv'--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~methodmain(args=String[])publicstaticArg=Rexx(args)iCvs=ReadernulloCvs=Writernullparseargifileofile.ififile='',ifile='.'thenifile=CSV_IFILEifofile='',ofile='.'thenofile=CSV_OFILEsaytextFileContentsToString(ifile)doiCvs=BufferedReader(FileReader(ifile))oCvs=BufferedWriter(FileWriter(ofile))processCsv(iCvs,oCvs);catchex=IOExceptionex.printStackTrace();finallydoifiCvs\=nulltheniCvs.close()ifoCvs\=nullthenoCvs.close()catchex=IOExceptionex.printStackTrace()endendsaytextFileContentsToString(ofile)return--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~methodprocessCsv(iCvs=Reader,oCvs=Writer)publicstaticbinarysignalsIOExceptionprinter=CSVPrinternulldoprinter=CSVPrinter(oCvs,CSVFormat.DEFAULT.withRecordSeparator(NL))oCvsHeaders=java.util.ListoCvsRecord=java.util.Listrecords=CSVFormat.DEFAULT.withHeader(String[0]).parse(iCvs)irHeader=records.getHeaderMap()oCvsHeaders=ArrayList(Arrays.asList((irHeader.keySet()).toArray(String[0])))oCvsHeaders.add(COL_NAME_SUM)printer.printRecord(oCvsHeaders)recordIterator=records.iterator()record=CSVRecordloopwhilerecordIterator.hasNext()record=CSVRecordrecordIterator.next()oCvsRecord=record2list(record,oCvsHeaders)printer.printRecord(oCvsRecord)endfinallyifprinter\=nullthenprinter.close()endreturn--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~methodrecord2list(record=CSVRecord,oCvsHeaders=java.util.List)privatestaticbinaryreturnsjava.util.ListcvsRecord=java.util.ListrMap=record.toMap()recNo=record.getRecordNumber()rMap=alterRecord(rMap,recNo)sum=summation(record.iterator())rMap.put(COL_NAME_SUM,sum)cvsRecord=ArrayList()loopci=0tooCvsHeaders.size()-1key=oCvsHeaders.get(ci)cvsRecord.add(rMap.get(key))endcireturncvsRecord--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~methodalterRecord(rMap=Map,recNo=long)privatestaticbinaryreturnsMaprv=intrg=Random(recNo)rv=rg.nextInt(50)ks=rMap.keySet().toArray(String[0])ix=rg.nextInt(ks.length)yv=long0ky=ks[ix];xv=StringrMap.get(ky)ifxv\=null&xv.length()>0thendoyv=Long.valueOf(xv).longValue()+rvrMap.put(ks[ix],String.valueOf(yv))endreturnrMap--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~methodsummation(iColumn=Iterator)privatestaticsum=0loopwhileiColumn.hasNext()nv=Rexx(StringiColumn.next())ifnv=null,nv.length()=0,\nv.datatype('n')thennv=0sum=sum+nvendreturnsum--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~methodtextFileContentsToString(filename)privatestaticlineOut=''fs=Scannernulldofs=Scanner(File(filename))lineOut=lineout||filename||NLloopwhilefs.hasNextLine()line=fs.nextLine()lineOut=lineout||line||NLendcatchex=FileNotFoundExceptionex.printStackTrace()finallyiffs\=nullthenfs.close()endreturnlineOut
data/csvtest_in.csv
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
data/csvtest_sumRexx.csv
C1,C2,C3,C4,C5,"SUM, ""integers"""1,5,9,21,17,4536,6,10,14,18,503,7,11,27,19,554,8,12,53,20,60
Nim's standard library contains a robust CSV parser, but for this simple document that's not necessary.
importstrutils,streamsletcsv=newFileStream("data.csv",fmRead)outf=newFileStream("data-out.csv",fmWrite)varlineNumber=1whiletrue:ifatEnd(csv):breakvarline=readLine(csv)iflineNumber==1:line.add(",SUM")else:varsum=0forninsplit(line,","):sum+=parseInt(n)line.add(",")line.add($sum)outf.writeLine(line)inclineNumber
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Objeck has a CSV parser with built-in functions.
use System.IO.File;use Data.CSV;class CsvData { function : Main(args : String[]) ~ Nil { file_out : FileWriter; leaving { if(file_out <> Nil) { file_out->Close(); }; }; if(args->Size() > 0) { file_name := args[0]; csv := CsvTable->New(FileReader->ReadFile(file_name)); if(csv->IsParsed()) { csv->AppendColumn("SUM"); for(i := 1; i < csv->Size(); i += 1;) { row := csv->Get(i); sum := row->Sum(row->Size() - 1); row->Set("SUM", sum->ToString()); }; }; output := csv->ToString(); output->PrintLine(); file_out := FileWriter->New("new-csv.csv"); file_out->WriteString(output); }; }}C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Using thecsv module available inOpam:
letlist_add_lastthislst=List.rev(this::(List.revlst))let()=letcsv=Csv.load"data.csv"inletfields,data=(List.hdcsv,List.tlcsv)inletfields=list_add_last"SUM"fieldsinletsums=List.map(funrow->lettot=List.fold_left(funtotthis->tot+int_of_stringthis)0rowinlist_add_last(string_of_inttot)row)datainCsv.output_all(Csv.to_channelstdout)(fields::sums)
$ opam install csv$ ocaml -I $(ocamlfind query csv) csv.cma rc_csv.mlC1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
PARI/GP has no built-in support for strings or/and generic CSV files.
Note: stok(str,d) - Tokenize a string - can be found here on RC.
\\ CSV data manipulation\\ 10/24/16 aev\\ processCsv(fn): Where fn is an input path and file name (but no actual extension).processCsv(fn)={my(F, ifn=Str(fn,".csv"), ofn=Str(fn,"r.csv"), cn=",SUM",nf,nc,Vr,svr);if(fn=="", return(-1));F=readstr(ifn); nf=#F;F[1] = Str(F[1],cn);for(i=2, nf, Vr=stok(F[i],","); if(i==2,nc=#Vr); svr=sum(k=1,nc,eval(Vr[k])); F[i] = Str(F[i],",",svr););\\fend ifor(j=1, nf, write(ofn,F[j]))}\\ Testing:processCsv("c:\\pariData\\test");data/test.csv file
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
data/testr.csv file
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
In Pascal you can use TStringList CommaText property to work with CSV.
programCSV_Data_Manipulation;usesClasses,SysUtils;vars:string;ts:tStringList;inFile,outFile:Text;Sum:integer;Number:string;beginAssign(inFile,'input.csv');Reset(inFile);Assign(outFile,'result.csv');Rewrite(outFile);ts:=tStringList.Create;ts.StrictDelimiter:=True;// Handle the headerReadLn(inFile,s);// Read a line from input filets.CommaText:=s;// Split it to linests.Add('SUM');// Add a lineWriteLn(outFile,ts.CommaText);// Reassemble it with comma as delimiter// Handle the datawhilenoteof(inFile)dobeginReadLn(inFile,s);ts.CommaText:=s;Sum:=0;forNumberintsdoSum+=StrToInt(Number);ts.Add('%D',[Sum]);writeln(outFile,ts.CommaText);end;Close(outFile);Close(inFile);ts.Free;end.
input.csv file
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
result.csv file
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
beginvarlines:=ReadLines('data.csv').Select((line,i)->beginifi=0thenResult:=line+',SUM'elseResult:=line+','+line.Split(',').Sum(x->x.ToInteger);end);WriteLines('outdata.csv',lines);lines.PrintLines;end.
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
For simple files, you can usesplit:
#!/usr/bin/perlusewarnings;usestrict;useList::Util'sum';my@header=split/,/,<>;# Remove the newline.chomp$header[-1];my%column_number;formy$i(0..$#header){$column_number{$header[$i]}=$i;}my@rows=map[split/,/],<>;chomp$_->[-1]for@rows;# Add 1 to the numbers in the 2nd column:$_->[1]++for@rows;# Add C1 into C4:$_->[$column_number{C4}]+=$_->[$column_number{C1}]for@rows;# Add sums to both rows and columns.push@header,'Sum';$column_number{Sum}=$#header;push@$_,sum(@$_)for@rows;push@rows,[map{my$col=$_;sum(map$_->[$column_number{$col}],@rows);}@header];# Print the output.printjoin(','=>@header),"\n";printjoin(','=>@$_),"\n"for@rows;
However, if the CSV can contain quoted texts (the type MS Excel produces), you should rather use theText::CSV. Only reading the data and printing the result is different:
#!/usr/bin/perlusewarnings;usestrict;useText::CSV;useList::Util'sum';my$csv='Text::CSV'->new({eol=>"\n"})ordie'Cannot use CSV: '.'Text::CSV'->error_diag;my$file=shift;my@rows;openmy$FH,'<',$fileordie"Cannot open $file: $!";my@header=@{$csv->getline($FH)};while(my$row=$csv->getline($FH)){push@rows,$row;}$csv->eofor$csv->error_diag;## The processing is the same.## Print the output.$csv->print(*STDOUT,$_)for\@header,@rows;
Note that error checking is omitted, in particular for scanf, and obviously we use an inline constant for pwa/p2js, but normal file i/o for desktop/Phix.
withjavascript_semanticsconstanttcsv="""C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20"""sequencelines=iff(platform()=JS?split(tcsv,"\n"):get_text("test.csv",GT_LF_STRIPPED))fori=1tolength(lines)dolines[i]=split(trim(lines[i]),',')endforlines[1]=join(lines[1],',')&",SUM"fori=2tolength(lines)dosequences=deep_copy(lines[i]),t={}forj=1tolength(s)dot&=scanf(s[j],"%d")[1][1]endfor-- s[rand(length(s))] = rand(100) -- (if you like)t&=sum(t)lines[i]=sprintf("%d,%d,%d,%d,%d,%d",t)endforlines=join(lines,'\n')ifplatform()!=JSthenintegerfn=open("out.csv","w")puts(fn,lines)close(fn)endifputs(1,lines)
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
<?php// fputcsv() requires at least PHP 5.1.0// file "data_in.csv" holds input data// the result is saved in "data_out.csv"// this version has no error-checking$handle=fopen('data_in.csv','r');$handle_output=fopen('data_out.csv','w');$row=0;$arr=array();while($line=fgetcsv($handle)){$arr[]=$line;}//change some data to zeroes$arr[1][0]=0;// 1,5,9,13,17 => 0,5,9,13,17$arr[2][1]=0;// 2,6,10,14,18 => 2,0,10,14,18//add sum and write fileforeach($arras$line){if($row==0){array_push($line,"SUM");}else{array_push($line,array_sum($line));}fputcsv($handle_output,$line);$row++;}?>
(in "data.csv" (prinl (line) "," "SUM") (while (split (line) ",") (prinl (glue "," @) "," (sum format @)) ) )
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
*process source xref attributes or(!); csv: Proc Options(Main); /********************************************************************* * 19.10.2013 Walter Pachl * 'erase d:\csv.out' * 'set dd:in=d:\csv.in,recsize(300)' * 'set dd:out=d:\csv.out,recsize(300)' * Say 'Input:' * 'type csv.in' * 'csv' * Say ' ' * Say 'Output:' * 'type csv.out' *********************************************************************/ Dcl in Record Input; Dcl out Record Output; On Endfile(in) Goto part2; Dcl (INDEX,LEFT,SUBSTR,TRIM) Builtin; Dcl (i,j,p,m,n) Bin Fixed(31) Init(0); Dcl s Char(100) Var; Dcl iline(10) Char(100) Var; Dcl a(20,20) Char(10) Var; Dcl sum Dec Fixed(3); Dcl oline Char(100) Var; Do i=1 By 1; Read File(in) Into(s); iline(i)=s; m=i; Call sep((s)); End; part2: Do i=1 To m; If i=1 Then oline=iline(1)!!','!!'SUM'; Else Do; sum=0; Do j=1 To n; sum=sum+a(i,j); End; oline=iline(i)!!','!!trim(sum); End; Write File(out) From(oline); End; sep: Procedure(line); Dcl line Char(*) Var; loop: Do j=1 By 1; p=index(line,','); If p>0 Then Do; a(i,j)=left(line,p-1); line=substr(line,p+1); End; Else Do; a(i,j)=line; Leave loop; End; End; n=j; End; End;
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
## Create a CSV file@"C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20"@-split"`r`n"|Out-File-FilePath.\Temp.csv-Force## Import each line of the CSV file into an array of PowerShell objects$records=Import-Csv-Path.\Temp.csv## Sum the values of the properties of each object$sums=$records|ForEach-Object{[int]$sum=0foreach($fieldin$_.PSObject.Properties.Name){$sum+=$_.$field}$sum}## Add a column (Sum) and its value to each object in the array$records=for($i=0;$i-lt$sums.Count;$i++){$records[$i]|Select-Object*,@{Name='Sum';Expression={$sums[$i]}}}## Export the array of modified objects to the CSV file$records|Export-Csv-Path.\Temp.csv-Force## Display the object in tabular form$records|Format-Table-AutoSize
C1 C2 C3 C4 C5 Sum-- -- -- -- -- ---1 5 9 13 17 452 6 10 14 18 503 7 11 15 19 554 8 12 16 20 60
Add a "SUM" column. Output is as for Lua and is not repeated here.
The following uses SWI-Prolog's csv_read_file_row/3 in order todemonstrate that it is not necessary to read more than a line at a time.
test:-augment('test.csv','test.out.csv').% augment( +InFileName, +OutFileName)augment(InFile,OutFile):-open(OutFile,write,OutStream),((csv_read_file_row(InFile,Row,[line(Line)]),% Row is of the form row( Item1, Item2, ....).addrow(Row,Out),csv_write_stream(OutStream,[Out],[]),fail);close(OutStream)).% If the first item in a row is an integer, then append the sum;% otherwise append 'SUM':addrow(Term,NewTerm):-Term=..[F|List],List=[X|_],(integer(X)->sum_list(List,Sum);Sum='SUM'),append(List,[Sum],NewList),NewTerm=..[F|NewList].
EnableExplicit#Separator$=","DefinefInput$="input.csv";insertpathtoinputfileDefinefOutput$="output.csv";insertpathtooutputfileDefineheader$,row$,field$DefinenbColumns,sum,iIfOpenConsole()IfNotReadFile(0,fInput$)PrintN("Error opening input file")GotoFinishEndIfIfNotCreateFile(1,fOutput$)PrintN("Error creating output file")CloseFile(0)GotoFinishEndIf;Readheaderrowheader$=ReadString(0);DeterminenumberofcolumnsnbColumns=CountString(header$,",")+1;Changeheaderrowheader$+#Separator$+"SUM";WritetooutputfileWriteStringN(1,header$);Readremainingrows,processandwritetooutputfileWhileNotEof(0)row$=ReadString(0)sum=0Fori=1TonbColumnsfield$=StringField(row$,i,#Separator$)sum+Val(field$)Nextrow$+#Separator$+sumWriteStringN(1,row$)WendCloseFile(0)CloseFile(1)Finish:PrintN("")PrintN("Press any key to close the console")Repeat:Delay(10):UntilInkey()<>""CloseConsole()EndIf
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
fileinputNote that thecsv module is not required for such a simple and regular CSV file. Here overwriting is done in place.
importfileinputchangerow,changecolumn,changevalue=2,4,'"Spam"'withfileinput.input('csv_data_manipulation.csv',inplace=True)asf:forlineinf:iffileinput.filelineno()==changerow:fields=line.rstrip().split(',')fields[changecolumn-1]=changevalueline=','.join(fields)+'\n'print(line,end='')
After this the data filecsv_data_manipulation.csv gets changed from that of the task to:
C1,C2,C3,C4,C51,5,9,"Spam",172,6,10,14,183,7,11,15,194,8,12,16,20
csv,pathlib andtempfileIn this example overwriting is performednot in place but by usingtempfile library for creating a temporary file andpathlib library for overwriting the initial file.csv module is used to allow easier manipulation with delimiters.
importcsvfrompathlibimportPathfromtempfileimportNamedTemporaryFilefilepath=Path('data.csv')temp_file=NamedTemporaryFile('w',newline='',delete=False)withfilepath.open()ascsv_file,temp_file:reader=csv.reader(csv_file)writer=csv.writer(temp_file)header=next(reader)writer.writerow(header+['SUM'])forrowinreader:row_sum=sum(map(int,row))writer.writerow(row+[row_sum])temp_file_path=Path(temp_file.name)temp_file_path.replace(filepath)
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
pandasimportpandasaspdfilepath='data.csv'df=pd.read_csv(filepath)rows_sums=df.sum(axis=1)df['SUM']=rows_sumsdf.to_csv(filepath,index=False)
t:("IIIII";enlist",")0:`:input.csv/ Read CSV file input.csv into table tt:updateSUM:sumvaluefliptfromt/ Add SUM column to t`:output.csv0:csv0:t/ Write updated table as CSV to output.csv
df<-read.csv(textConnection("C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20"))df$sum<-rowSums(df)write.csv(df,row.names=FALSE)
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
This output can also be saved to a file:
write.csv(df,file="foo.csv",row.names=FALSE)
#langracket(require(planetneil/csv:1:=7)net/url)(definemake-reader(make-csv-reader-maker'((separator-chars#\,)(strip-leading-whitespace?.#t)(strip-trailing-whitespace?.#t))))(define(all-rowsport)(defineread-row(make-readerport))(definehead(append(read-row)'("SUM")))(definerows(for/list([row(in-producerread-row'())])(definexs(mapstring->numberrow))(appendrow(list(~a(apply+xs))))))(define(->stringrow)(string-joinrow","#:after-last"\n"))(string-append*(map->string(consheadrows))))
Example:
(definecsv-file"C1, C2, C3, C4, C5 1, 5, 9, 13, 17 2, 6, 10, 14, 18 3, 7, 11, 15, 19 4, 8, 12, 16, 20")(display(all-rows(open-input-stringcsv-file)))
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
(formerly Perl 6)On the face of it this task is pretty simple. Especially given the sample CSV file and the total lack of specification ofwhat changes to make to the file. Something like this would suffice.
my$csvfile ='./whatever.csv';my$fh =open($csvfile, :r);my@header =$fh.get.split(',');my@csv =map {[.split(',')]>>.Num},$fh.lines;close$fh;my$out =open($csvfile, :w);$out.say((@header,'SUM').join(','));$out.say((@$_, [+] @$_).join(','))for@csv;close$out;
But if your CSV file is at all complex you are better off using a CSV parsing module. (Complex meaning fields that contain commas, quotes, newlines, etc.)
useText::CSV;my$csvfile ='./whatever.csv';my@csv =Text::CSV.parse-file($csvfile);# modify(@csv); # do whatever;csv-write-file(@csv, :file($csvfile) );
>>filein:read/lines%file.csv>>data:copy[]>>foreachitemfilein[append/onlydatasplititem","]; [["C1" "C2" "C3" "C4" "C5"] ["1" "5" "9" "13" "17"] ["2" "6" "10" "14" "18"] ["3" "7" "11" "15" "19"]["4" "8" "12" "16" "20"]]
>>foralldata[either(index? data)=1[appenddata/1"SUM"][appenddata/1tostring!(tointeger!data/1/1)+(tointeger!data/1/2)+(tointeger!data/1/3)+(tointeger!data/1/4)+(tointeger!data/1/5)]]
>>foreachitemdata[appenditem/6"^/"repeatc5[appenditem/:c","]]>>printdataC1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60>>writefileout.csvformdata
/* REXX **************************************************************** extend in.csv to add a column containing the sum of the lines' elems* 21.06.2013 Walter Pachl**********************************************************************/csv='in.csv'Doi=1By1Whilelines(csv)>0l=linein(csv)Ifi=1Thenl.i=l',SUM'ElseDool=lsum=0DoWhilel<>''ParseVarle','lsum=sum+eEndl.i=ol','sumEndEndCalllineoutcsv'erase'csvDoi=1Toi-1Calllineoutcsv,l.iEnd
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
This REXX version has no need to use an operating system command to ERASE the (input) file, it uses instead
an idiomatic REXX method to write to the input file (starting at record one).
Also supported is the ability to specify the fileID of the data file to be specified.
/*REXX program reads a CSV file & appends a SUM column (which is the sum of all columns)*/parseargiFID./*obtain optional argument from the CL*/ifiFID==''|iFID==","theniFID='CSV_SUM.DAT'/*Not specified? Then use the default*/calllineiniFID,1,0/*position the input file to line one.*//* [↑] only needed if pgm is nested. */dorec=1whilelines(iFID)\==0/*read the input file (all records). */x=linein(iFid);y=translate(x,,',')/*read a rec; change commas to blanks.*/$=0/*initial the sum to zero. */doj=1forwords(y);_=word(y,j)/*get a CSV value. */ifdatatype(_,'N')then$=$+_/*Numeric? Add to $.*/else$='SUM'/*Not? Append "SUM".*/end/*j*/@.rec=x','$/*append the sum to the record. */end/*rec*//*Note: at EOF, REC ≡ # of records+1.*/sayrec-1' records read from: 'iFID/* [↓] this elides the need for ERASE*/calllineoutiFID,@.1,1/*set file ptr to 1st rec., write hdr.*/dok=2forrec-2/*process all the records just read. */calllineoutiFID,@.k/*write the new CSV record (has SUM). */end/*k*//*stick a fork in it, we're all done.*/
5 records read from: CSV_SUM.DAT
# Project : CSV data manipulationload "stdlib.ring"fnin = "input.csv"fnout = "output.csv"fpin = fopen(fnin,"r")fpout = fopen(fnout,"r")csv = read(fnin)nr = 0csvstr = ""while not feof(fpin) sum = 0 nr = nr + 1 line = readline(fpin) if nr = 1 line = substr(line,nl,"") line = line + ",SUM" csvstr = csvstr + line + windowsnl() else csvarr = split(line,",") for n = 1 to len(csvarr) sum = sum + csvarr[n] next line = substr(line,nl,"") line = line + "," + string(sum) csvstr = csvstr + line + windowsnl() okendwrite(fnout,csvstr)csvend = read(fnout)fclose(fpin)fclose(fpout)see csvend + nl
Output:
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
require'csv'# read:ar=CSV.table("test.csv").to_a#table method assumes headers and converts numbers if possible.# manipulate:ar.first<<"SUM"ar[1..-1].each{|row|row<<row.sum}# write:CSV.open("out.csv",'w')do|csv|ar.each{|line|csv<<line}end
c1,c2,c3,c4,c5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
csv$ = "C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20"print csv$dim csvData$(5,5)for r = 1 to 5 a$ = word$(csv$,r,chr$(13)) for c = 1 to 5 csvData$(r,c) = word$(a$,c,",") next cnext r[loop]input "Row to change:";rinput "Col to change;";cif r > 5 or c > 5 then print "Row ";r;" or Col ";c;" is greater than 5" goto [loop]end ifinput "Change Row ";r;" Col ";c;" from ";csvData$(r,c);" to ";d$csvData$(r,c) = d$for r = 1 to 5 for c = 1 to 5 print cma$;csvData$(r,c); cma$ = "," next c cma$ = "" printnext r
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20Row to change:?4Col to change;?4Change Row 4 Col 4 from 15 to ?99C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,99,194,8,12,16,20
usestd::error::Error;usestd::num::ParseIntError;usecsv::{Reader,Writer};fnmain()->Result<(),Box<dynError>>{letmutreader=Reader::from_path("data.csv")?;letmutwriter=Writer::from_path("output.csv")?;// headers() returns an immutable reference, so clone() before appendingletmutheaders=reader.headers()?.clone();headers.push_field("SUM");writer.write_record(headers.iter())?;forrowinreader.records(){letmutrow=row?;// `sum` needs the type annotation so that `parse::<i64>` knows what error type to returnletsum:Result<_,ParseIntError>=row.iter().try_fold(0,|accum,s|{Ok(accum+s.parse::<i64>()?)});row.push_field(&sum?.to_string());writer.write_record(row.iter())?;}writer.flush()?;Ok(())}
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
data_null_;infiledatalines dlm="," firstobs=2;file"output.csv" dlm=",";input c1-c5;if_n_=1thenput"C1,C2,C3,C4,C5,Sum";s=sum(of c1-c5);putc1-c5 s;datalines;C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20;run;
importscala.io.SourceobjectparseCSVextendsApp{valrawData="""|C1,C2,C3,C4,C5 |1,5,9,13,17 |2,6,10,14,18 |3,7,11,15,19 |20,21,22,23,24""".stripMarginvaldata=Seq((Source.fromString(rawData).getLines()).map(_.split(",")).toSeq:_*)valoutput=((data.take(1).flatMap(x=>x):+"SUM").mkString(",")+:// Header linedata.drop(1).map(_.map(_.toInt)).// Convert per line each array of String to array of integermap(cells=>(cells,cells.sum)).//Add sum column to assemble a tuple. Part 1 are original numbers, 2 is the summap(part=>s"${part._1.mkString(",")},${part._2}")).mkString("\n")println(output)/* Outputs:C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,5520,21,22,23,24,110*/}
The program below assumes that the input file has the name csvDataManipulation.in andis in the same directory as the program.
$ include "seed7_05.s7i";const proc: main is func local var file: input is STD_NULL; var array array string: csvData is 0 times 0 times ""; var integer: line is 0; begin input := open(dir(PROGRAM) & "/csvDataManipulation.in", "r"); while hasNext(input) do csvData &:= split(getln(input), ","); end while; csvData[3][3] := "X"; for key line range csvData do writeln(join(csvData[line], ",")); end for; end func;
C1,C2,C3,C4,C51,5,9,13,172,6,X,14,183,7,11,15,194,8,12,16,20
// For test purposes, start by creating (or re-creating) the data fileput {{C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20}} into file "myData.csv"// Read the file as a list of lists (rather than as the default list of property lists)put CSVValue(file "myData.csv", asLists:Yes) into csvDatainsert "SUM" into item 1 of csvData -- add a new column heading// Go through all of the data rows to add the sumrepeat with rowNum= 2 to the number of items in csvDatainsert the sum of item rowNum of csvData into item rowNum of csvDataend repeatput csvData -- see the modified data as a list of listsput CSVFormat of csvData into file "myData.csv"put file "myData.csv" -- display the updated file contentsFor simple files we can use thesplit method.
# Readvarcsvfile=%f'data.csv';varfh=csvfile.open_r;varheader=fh.line.trim_end.split(',');varcsv=fh.lines.map{.trim_end.split(',').map{.to_num}};fh.close;# Writevarout=csvfile.open_w;out.say([header...,'SUM'].join(','));csv.each{|row|out.say([row...,row.sum].join(','))};out.close;
For complex files, theText::CSV library is recommended.
varcsv=require('Text::CSV').new(Hash(eol=>"\n"));# Openvarcsvfile=%f'data.csv';varfh=csvfile.open_r;# Readvarrows=[];varheader=csv.getline(fh);while(varrow=csv.getline(fh)){rows.append(row.map{.to_num});}# Processheader.append('SUM');rows.each{|row|row.append(row.sum)};# Writevarout=csvfile.open_w;[header,rows...].each{|row|csv.print(out,row);};
import delim input.csv, clearreplace c5=c3+c4egen sum=rowtotal(c*)drop ifmod(c3,3)==0export delim output.csv, replace
packagerequirestruct::matrixpackagerequirecsvprocaddSumColumn{filename{title"SUM"}}{setm[struct::matrix]# Load the CSV insetf[open$filename]csv::read2matrix$f$m","autoclose$f# Add the column with the sumssetsumcol[$mcolumns]$maddcolumn$titlefor{seti1}{$i<[$mrows]}{incri}{# Fill out a dummy value$msetcell$sumcol$i0$msetcell$sumcol$i[tcl::mathop::+{*}[$mgetrow$i]]}# Write the CSV outsetf[open$filenamew]csv::writematrix$m$fclose$f$mdestroy}addSumColumn"example.csv"
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Although, for this specific small task,
setf[openexample.csvr]puts"[gets $f],SUM"while{[gets$frow]>0}{puts"$row,[expr [string map {, +} $row]]"}close$f
suffices.
$$ MODE DATA$$ csv=*C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20$$ MODE TUSCRIPTLOOP/CLEAR n,line=csv IF (n==1) THEN line=CONCAT (line,",SUM") ELSE lineadd=EXCHANGE(line,":,:':") sum=SUM(lineadd) line=JOIN(line,",",sum) ENDIF csv=APPEND(csv,line)ENDLOOP
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
@(coll)@{name /[^,]+/}@(end)@(collect :vars (value sum))@ (bind sum 0)@ (coll)@{value /[^,]+/}@(set sum @(+ sum (int-str value)))@(end)@(end)@(output)@ (rep)@name,@(last)SUM@(end)@ (repeat)@ (rep)@value,@(last)@sum@(end)@ (end)@(end)Very simple solution using powerfull and ancient but strong linux command, I named "tr" and "bc", and internal variable bash test capabilities :
cat csv | while read S; do [ -z ${S##*C*} ] && echo $S,SUM || echo $S,`echo $S | tr ',' '+' | bc`doneResult :
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
Other solution (not from me) :
bash>exec 0<"$1" # open the input file on stdinexec 1>"$1.new" # open an output file on stdout{ read -r header echo "$header,SUM" IFS=, while read -r -a numbers; do sum=0 for num in "${numbers[@]}"; do (( sum += num )) done # can write the above loop as # sum=$(( $(IFS=+; echo "${numbers[*]}") )) echo "${numbers[*]},$sum" done } &&mv "$1" "$1.bak" && mv "$1.new" "$1"To make this work with ksh, change
read -a
to
read -A
uBasic/4tH can read text files and has a built-in tokenizer, so parsing simple CSV files is not a problem.
if set (a, open ("yourcsv.csv", "r")) < 0 then print "Cannot open \qyourcsv.csv\q" ' open file a for reading end ' abort on file opening errorsendifif set (b, open ("mycsv.csv", "w")) < 0 then print "Cannot open \qmycsv.csv\q" ' open file a for writing end ' abort on file opening errorsendifif read (a) = 0 then ' read the header line print "Unexpected end of file" ' if it fails, write the error close a : close b : end ' close files and terminateendif ' process the header linefor c = 0 step 1 ' don't know number of columns p = here() ' get input buffer position y = tok (ord (",")) ' parse the first fielduntil p = here() ' until buffer position doesn't change write b, show (y);","; ' write it outnextwrite b, "Sum" ' add a columndo while read (a) ' read a line s = 0 ' reset the sum for x = 0 to c-1 ' read all columns y = iif (set (y, val (tok (ord (",")))) = info ("nil"), 0, y) s = s + y ' add value to sum write b, y;","; ' write the value next ' next column write b, s ' write the sumloopclose a : close b : end ' close files and terminate# Simple CSV manipulation# Effect of running `&fras "example.csv"`$ C1,C2,C3,C4,C5$ 1,5,9,13,17$ 2,6,10,14,18$ 3,7,11,15,19$ 4,8,12,16,20&p⍜°csv(⍜⍉⊂:⊂□"SUM"≡(□⍜⋕/+)↘1.)# Save using `&fwa "example.csv"`
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
## csv data manipulation## declare a string stream to hold linesdecl string<> lines# open the file specified on the command line, halting# execution if they didn't enter one. it will be created if# it doesn't exist yetdecl file fif (< (size args) 2) out "error: please specify a csv file" endl console stopend iff.create args<1>f.open args<1># read in all lines from the fileset lines (f.readlines)# append sum column to headerset lines<0> (+ lines<0> ",SUM")# determine sums and append themdecl int i sumfor (set i 1) (< i (size lines)) (inc i) set sum 0 for (decl int j) (< j (size (split lines<i> ","))) (inc j) set sum (int (+ sum (int (split lines<i> ",")<j>))) end for set lines<i> (+ lines<i> (+ "," sum))end for# delete the file, then create it againf.delete args<1>f.create args<1># output all lines to the filefor (set i 0) (< i (size lines)) (inc i) out lines<i> endl fend for
Using Excel VBA to load a CSV file in a new workbook.
SubReadCSV()Workbooks.OpenFilename:="L:\a\input.csv"Range("F1").Value="Sum"Range("F2:F5").Formula="=SUM(A2:E2)"ActiveWorkbook.SaveAsFilename:="L:\a\output.csv",FileFormat:=xlCSVActiveWindow.CloseEndSub
'Instatiate FSO.SetobjFSO=CreateObject("Scripting.FileSystemObject")'Open the CSV file for reading. The file is in the same folder as the script and named csv_sample.csv.SetobjInCSV=objFSO.OpenTextFile(objFSO.GetParentFolderName(WScript.ScriptFullName)&"\csv_sample.csv",1,False)'Set header status to account for the first line as the column headers.IsHeader=True'Initialize the var for the output string.OutTxt=""'Read each line of the file.DoUntilobjInCSV.AtEndOfStreamline=objInCSV.ReadLineIfIsHeaderThenOutTxt=OutTxt&line&",SUM"&vbCrLfIsHeader=FalseElseOutTxt=OutTxt&line&","&AddElements(line)&vbCrLfEndIfLoop'Close the file.objInCSV.Close'Open the same file for writing.SetobjOutCSV=objFSO.OpenTextFile(objFSO.GetParentFolderName(WScript.ScriptFullName)&"\csv_sample.csv",2,True)'Write the var OutTxt to the file overwriting existing contents.objOutCSV.WriteOutTxt'Close the file.objOutCSV.CloseSetobjFSO=Nothing'Routine to add each element in a row.FunctionAddElements(s)arr=Split(s,",")Fori=0ToUBound(arr)AddElements=AddElements+CInt(arr(i))NextEndFunction
C1,C2,C3,C4,C51,5,9,13,172,6,10,14,183,7,11,15,194,8,12,16,20
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
This example adds 100 to the values in each cell at row n+1, column n.
File_Open("input.csv")for (#1 = 0; #1 < 4; #1++) { Goto_Line(#1+2) // line (starting from line 2) if (#1) { Search(",", ADVANCE+COUNT, #1) // column } #2 = Num_Eval() // #2 = old value Del_Char(Chars_Matched) // delete old value Num_Ins(#2+100, LEFT+NOCR) // write new value}File_Save_As("output.csv", OK+NOMSG)output.csv:
C1,C2,C3,C4,C5101,5,9,13,172,106,10,14,183,7,111,15,194,8,12,116,20
CLOSE DATABASES ALLSET SAFETY OFFMODIFY FILE file1.csv NOEDIT*!* Create a cursor with integer columnsCREATE CURSOR tmp1 (C1 I, C2 I, C3 I, C4 I, C5 I)APPEND FROM file1.csv TYPE CSVSELECT C1, C2, C3, C4, C5, C1+C2+C3+C4+C5 As sum ;FROM tmp1 INTO CURSOR tmp2COPY TO file2.csv TYPE CSVMODIFY FILE file2.csv NOEDIT IN SCREENSET SAFETY ON
Wren does not have any built-in functions for dealing with generic CSV files so we therefore need to work from first principles.
import "io" for Filevar lines = File.read("rc.csv").split("\n").map { |w| w.trim() }.toListvar file = File.create("rc.csv") // overwrite existing filefile.writeBytes(lines[0] + ",SUM\n")for (line in lines.skip(1)) { if (line != "") { var nums = line.split(",").map { |s| Num.fromString(s) } var sum = nums.reduce { |acc, n| acc + n } file.writeBytes(line + ",%(sum)\n") }}file.close()Contents of rc.csv after manipulation:
C1,C2,C3,C4,C5,SUM1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60
XPL0 has no built-in functions that handle CSV files. However, it's easyenough to make the InField procedure shown here that reads in a CSV fieldand returns its text and integer values. The task's csv file is read andwritten from a command line like this: csv <csv.txt >csv2.txt
string 0; \use zero-terminated stringsdef LF=$0A, EOF=$1A;int Val, Char;char Str(80);proc InField;int I;[I:= 0; Val:= 0;loop [Char:= ChIn(1); if Char=^, or Char=LF or Char=EOF then quit; Str(I):= Char; I:= I+1; if Char>=^0 and Char<=^9 then Val:= Val*10 + Char - ^0; ];Str(I):= 0;];int Sum;[loop [InField; Text(0, Str); if Char = LF then quit; ChOut(0, ^,); ];Text(0, ",SUM");CrLf(0);loop [Sum:= 0; loop [InField; if Char = EOF then return; if rem(Val/5)=0 then Val:= Val*20; IntOut(0, Val); Sum:= Sum + Val; if Char = LF then quit; ChOut(0, ^,); ]; Text(0, ","); IntOut(0, Sum); CrLf(0); ];]
C1,C2,C3,C4,C5,SUM1,100,9,13,17,1402,6,200,14,18,2403,7,11,300,19,3404,8,12,16,400,440
open #1, "manipy.csv", "r" //existing CSV file separated by spaces, not commasopen #2, "manip2.csv", "w" //new CSV file for writing changed dataline input #1 header$ header$ = header$ + ",SUM"print #2 header$while !eof(1) input #1 c1, c2, c3, c4, c5 sum = c1 + c2 + c3 + c4 + c5 print #2 c1, c2, c3, c4, c5, sumwendclose #1close #2end
csvFile:=File("test.csv");header:=csvFile.readln().strip(); // remove trailing "\n" and leading white spacelistOfLines:=csvFile.pump(List,fcn(line){ line.strip().split(",").apply("toInt") }); newFile:=File("test2.csv","w");newFile.writeln(header + ",sum");listOfLines.pump(newFile.writeln,fcn(ns){ String(ns.concat(","),",",ns.sum()) });newFile.close();$ cat test2.csv C1,C2,C3,C4,C5,sum1,5,9,13,17,452,6,10,14,18,503,7,11,15,19,554,8,12,16,20,60