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:

code format="bb4w" 1997,Ford,E350,"AC, ABS, moon roof",3000.00 1999,Chevy,"Venture ""Extended Edition""",,4900.00 code For more details see this [|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 article.

code format="bb4w" 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 code