This is an old revision of the document!
=====Reading and writing CSV files===== //by Richard Russell, July 2007//\\ \\ **Comma Separated Value** or **CSV** files are a common format typically used to represent the contents of a spreadsheet or database (or any other 2D matrix of data cells); they can by read and written by programs such as **Microsoft Excel** and **Microsoft Access**. The main properties of CSV files are as follows:\\ \\ * Data rows are separated by newlines (CRLF). * Data columns are separated by commas. * Quotation marks must be escaped using the convention **""**. * Data cells containing commas, newlines or quotes must be enclosed in quotes. * Any data cell may optionally be enclosed in quotes. \\ Here is an example of the contents of a CSV file:\\ \\ 1997,Ford,E350,"AC, ABS, moon roof",3000.00 1999,Chevy,"Venture ""Extended Edition""",,4900.00 For more details see this [[http://en.wikipedia.org/wiki/Comma-separated_values|Wikipedia article]].\\ \\ The routines listed below read a CSV file into a 2D string array or write a 2D string array into a CSV file respectively. Note that spaces before an initial quote character are not permitted. If the CSV file uses a non-standard line termination (e.g. **LF** or **LFCR**) then replace the **INPUT #F%,A$** with **A$ = FNreadline(F%)** where the FNreadline function can be found in this [[/Reading%20and%20writing%20plain%20text%20files|article]].\\ \\ DEF PROCreadCSV(file$,array$()) LOCAL F%,R%,C%,I%,A$,C$ F% = OPENIN(file$) IF F%=0 THEN ERROR 214, "Could not open file "+file$ array$() = "" REPEAT INPUT #F%,A$ IF ASCA$=10:A$ = MID$(A$,2) FOR C% = 0 TO DIM(array$(),2) I% = 1 WHILE ASCMID$(A$,I%)=34 REPEAT I% = INSTR(A$,"""",I%+1)+1 IF I%=1 THEN INPUT #F%,C$ A$ += CHR$13 + C$ ENDIF UNTIL I%<>1 OR EOF#F% ENDWHILE I% = INSTR(A$,",",I%) IF I%=0:I% = LENA$+1 IF ASCA$=34:C$ = MID$(A$,2,I%-3) ELSE C$ = LEFT$(A$,I%-1) A$ = MID$(A$,I%+1) REPEAT I% = INSTR(C$, """""") IF I%:C$ = LEFT$(C$,I%)+MID$(C$,I%+2) UNTIL I%=0 array$(R%,C%) = C$ NEXT R% += 1 UNTIL R% > DIM(array$(),1) OR EOF#F% CLOSE #F% ENDPROC DEF PROCwriteCSV(file$,array$()) LOCAL F%,R%,C%,I%,A$,C$ F% = OPENOUT(file$) IF F%=0 THEN ERROR 192, "Could not create file "+file$ FOR R% = 0 TO DIM(array$(),1) A$ = "" FOR C% = 0 TO DIM(array$(),2) C$ = array$(R%,C%) I% = -1 REPEAT I% = INSTR(C$,"""",I%+2) IF I% : C$ = LEFT$(C$,I%)+MID$(C$,I%) UNTIL I%=0 IF INSTR(C$,",") OR INSTR(C$,"""") OR INSTR(C$,CHR$13) OR INSTR(C$,CHR$10) THEN A$ += """"+C$+"""," ELSE A$ += C$+"," ENDIF NEXT C% PRINT #F%,LEFT$(A$) BPUT #F%,10 NEXT R% CLOSE #F% ENDPROC