User Tools

Site Tools


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$() = ""
          INPUT #F%,A$
          IF ASCA$=10:A$ = MID$(A$,2)
          FOR C% = 0 TO DIM(array$(),2)
            I% = 1
            WHILE ASCMID$(A$,I%)=34
                I% = INSTR(A$,"""",I%+1)+1
                IF I%=1 THEN
                  INPUT #F%,C$
                  A$ += CHR$13 + C$
              UNTIL I%<>1 OR EOF#F%
            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)
              I% = INSTR(C$, """""")
              IF I%:C$ = LEFT$(C$,I%)+MID$(C$,I%+2)
            UNTIL I%=0
            array$(R%,C%) = C$
          R% += 1
        UNTIL R% > DIM(array$(),1) OR EOF#F%
        CLOSE #F%
        DEF PROCwriteCSV(file$,array$())
        F% = OPENOUT(file$)
        IF F%=0 THEN ERROR 192, "Could not create file "+file$
        FOR R% = 0 TO DIM(array$(),1)
          A$ = ""
            C$ = array$(R%,C%)
            I% = -1
              I% = INSTR(C$,"""",I%+2)
            UNTIL I%=0
            IF INSTR(C$,",") OR INSTR(C$,"""") OR INSTR(C$,CHR$13) OR INSTR(C$,CHR$10) THEN
              A$ += """"+C$+""","
              A$ += C$+","
          NEXT C%
          PRINT #F%,LEFT$(A$)
          BPUT #F%,10
        NEXT R%
        CLOSE #F%
reading_20and_20writing_20csv_20files.txt · Last modified: 2018/04/17 18:25 by tbest3112