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