BBC BASIC Programmers' Reference

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====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 or LF).
  * 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.

<code 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
        A$ = GET$#F%
        IF A$="" IF PTR#F%>1 THEN PTR#F%=PTR#F%-2 : IF BGET#F%<>BGET#F% A$=GET$#F%
          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>