# BBC BASIC Programmers' Reference

## 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 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.

```        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```