How do I read a Text or CSV file from EXCEL

[Triopstor]

Hi!

I'm having problems reading a TEXT *.txt file and also a CSV file from EXCEL.
Is this possible?

I have data in an EXCEL file and I wish the FreeBASIC program to read it. Do I need a special program to convert this?

[shadow008]

I don't know what the format of a txt file looks like from excel, but parsing csv is easy. Read string, read comma, repeat. There are a few examples on the forums of csv readers. I found this one that looks approximately correct: viewtopic.php?t=14157

[jevans4949]

A CSV file can be read be read using GET # in the standard way, provided none of the fields are likely to contain carriage return line breaks, as the I/O routines don't recognise that these are not meant to be end-of-line indicators. Also note that in Italian CSV files, commas are used to indicate decimal points and field delimiters are semicolons. If either of these are likely, you will have to parse the file yourself, reading in blocks using the C runtime fread function.
If using GET, you will need to read all fields in the line. Also watch out for files containing headers.

[Berkeley]

First: CSV are pure text files, representing a "spreadsheet" page. It looks correctly like this:
"<cell A1>", "<cell B1>", "<cell C1>", "<cell D1>"
"<cell A2>", "<cell B2>", "<cell C2>", "<cell D2>"
"<cell A3>", "<cell B3>", "<cell C3>", "<cell D3>"
"<cell A4>", "<cell B4>", "<cell C4>", "<cell D4>"
Therefore it doesn't matter, if a "cell" contains a point or comma, but quotation marks can be the problem. Pseudo-CSV might use no quotation marks and e.g. tabulators instead of commas.

I dunno whether FreeBASIC could read this properly somehow, but you may also read the whole file at once into a string, and parse this one later. Last way is much more complicated though. Just test what you get with INPUT#(or LINE INPUT#?)...

You'd might use this here, but have to strip off quotation marks:

Code: Select all

' STRGETLISTITEM(STRING list, INTEGER index[, STRING divider])
'   returns a substring of a string list with the form "xx,xx,xx,xx"
'   (or another specified divider)
'   index starts with 0 = first substring
'   Divider must be a single ASCII character except a nullbyte, " and \.
'   Strips off spaces except if using quotation marks to enclose the entries
'   => also ""xx","xx"...", further supporting the "escape sequences" \" and \\.
'   Dividers between quotation marks are ignored.
'
FUNCTION STRGETLISTITEM(BYVAL list AS STRING, BYVAL index AS INTEGER, BYVAL divider AS STRING=",") AS STRING
  DIM AS STRING result
  DIM AS INTEGER i, l
  DIM AS UBYTE c, d

  ' validate divider
  IF LEN(divider)<>1 THEN RETURN "" ' more than 1 char is invalid => error
  d=divider[0]
  IF d=0 OR d>127 OR d=34 OR d=92 THEN RETURN "" ' invalid char => error

  ' "browse" the list
  i=0
  l=LEN(list)
  result=""
  WHILE i<l AND index>=0
    c=list[i]
    IF c=34 THEN ' quotation mark
      ' all between quotation marks is part of the "item"
      i+=1
      WHILE i<l
        c=list[i]
        IF c=34 THEN EXIT WHILE
        IF c=92 THEN ' backslash => "masking" following \ or "
          i+=1
          IF i>=l THEN EXIT WHILE
          c=list[i]
          IF index=0 THEN ' item index reached
            IF c<>92 AND c<>34 THEN result+="\" ' no effect in any other case
          ENDIF
        ENDIF
        IF index=0 THEN ' item index reached
          result+=CHR(c)
        ENDIF
        i+=1
      WEND
      WHILE i<l AND c<>d ' get next divider(comma)
        c=list[i]
        i+=1
      WEND
      index-=1 ' end of item => next item
    ELSEIF c=92 THEN ' backslash
      i+=1
      IF i>=l THEN EXIT WHILE
      c=list[i]
      IF index=0 THEN ' item index reached
        IF c<>92 AND c<>34 THEN result+="\" ' no effect in any other case
      ENDIF
    ELSEIF c<>d THEN ' not divider
      IF c<33 THEN ' "space chars" => ignore
        WHILE i<l
          c=list[i]
          IF c=d OR c>32 THEN EXIT WHILE
          i+=1
        WEND
      ELSE
        IF index=0 THEN ' item index reached
          result+=CHR(c)
        ENDIF
        i+=1
      endif
    ELSE ' divider reached
      index-=1
      i+=1
    ENDIF
  WEND

  RETURN result
END FUNCTION