How do I read a Text or CSV file from EXCEL

New to FreeBASIC? Post your questions here.
Post Reply
Triopstor
Posts: 118
Joined: Apr 25, 2006 13:11

How do I read a Text or CSV file from EXCEL

Post by 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
Posts: 117
Joined: Nov 26, 2013 2:43

Re: How do I read a Text or CSV file from EXCEL

Post by 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
Posts: 1188
Joined: May 08, 2006 21:58
Location: Crewe, England

Re: How do I read a Text or CSV file from EXCEL

Post by 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
Posts: 83
Joined: Jun 08, 2024 15:03

Re: How do I read a Text or CSV file from EXCEL

Post by 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
Triopstor
Posts: 118
Joined: Apr 25, 2006 13:11

Re: How do I read a Text or CSV file from EXCEL

Post by Triopstor »

shadow008 wrote: Oct 01, 2024 15:45 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
I think I either created a bad .CSV file or forgot to use as a file extension of .CSV. Works now. Thanks shadow008!
Triopstor
Posts: 118
Joined: Apr 25, 2006 13:11

Re: How do I read a Text or CSV file from EXCEL

Post by Triopstor »

jevans4949 wrote: Oct 01, 2024 17:38 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.
Thanks for your advice and cautioning me about Italian .CVS files with comma's interfering with reading a file when indicating a decimal point. My code works now.
Triopstor
Posts: 118
Joined: Apr 25, 2006 13:11

Re: How do I read a Text or CSV file from EXCEL

Post by Triopstor »

Berkeley wrote: Oct 01, 2024 20:44 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#?)...

Nice code. It will be useful for stripping quotation marks. I deeply appreciate you sharing your code! Thanks Berkeley!
Triopstor
Posts: 118
Joined: Apr 25, 2006 13:11

Re: How do I read a Text or CSV file from EXCEL

Post by Triopstor »

I created a .CSV file containing the data in General or Number Format:
1, 2
3, 4
5, 6

Using the procedure:
1. Open your Excel file: Start by opening the Excel workbook you want to convert to a CSV file.
2. Go to the File tab: Click on the “File” tab in the top-left corner of the Excel window.
3. Select Save As: Choose “Save As” from the menu.
4. Choose the location: Select the location where you want to save the CSV file.
5. Select CSV format: In the “Save as type” dropdown menu, choose “CSV (Comma delimited) (*.csv)”.
6. Name your file: Enter a name for your CSV file.
7. Save the file: Click “Save”.

My FileReadTester working code:

Code: Select all

'REM FileReadTester.bas
DIM SHARED as Single Q1,Q2,Q3,Q4
DIM SHARED as String Q1S, Q2S
DIM SHARED as String F1S
DIM SHARED as Single Array1(1 to 5000)
DIM SHARED as Single Array2(1 to 5000)
DIM SHARED as Integer X1,X2

DECLARE SUB READDATA

'MAIN
F1S="TEST.CSV"
READDATA
SLEEP


SUB READDATA
'PURPOSE: Read the result file
OPEN F1S FOR INPUT LOCK WRITE AS #1
X1=0
DO
  X1=X1+1
  INPUT #1, Q1,Q2
  Array1(X1)=Q1:Array2(X1)=Q2
  PRINT Q1,Q2
  SLEEP
LOOP WHILE NOT EOF(1)
PRINT:FOR X2=1 TO X1:PRINT Array1(X2),Array2(X2):NEXT X2
CLOSE #1


END SUB
CASE CLOSED: Everything works fine now.
Kuan Hsu
Posts: 613
Joined: Sep 16, 2007 15:12
Location: Taiwan

Re: How do I read a Text or CSV file from EXCEL

Post by Kuan Hsu »

Using fxm's split function, you can split the string by sign into dynamic arrays
split function: https://www.freebasic.net/forum/viewtop ... 17#p284817

Example code:

Code: Select all

#include Once "fbc-Int/array.bi"

' This Function by fxm
' https://www.freebasic.net/forum/viewtopic.php?p=284817#p284817
Function split(Byref s As Const String, result() As String, Byref delimiter As Const String = ",") As Integer
   
    Const As Integer resizing1 = 36
    Const As Integer resizing2 = 10 * resizing1
   
    Dim As Integer i
    Redim As Integer index(resizing1)
    index(0) = 1
   
    If delimiter = "" Then Return 1  '' supplied delimiter empty
   
    Do
        Dim As Integer n = Instr(index(i), s, delimiter)
        If n = 0 Then Exit Do
        If i = Ubound(index) Then
            Redim Preserve index(i + resizing1 * (i \ resizing2 + 1))
        End If
        i += 1
        index(i) = n + Len(delimiter)
    Loop
   
    If i = 0 Then Return 2  '' no delimiter found in String
   
    If Ubound(result) - Lbound(result) < i Then
        Dim As Const FBC.FBARRAY Ptr pd = FBC.ArrayConstDescriptorPtr(result())
        If (pd->flags And FBC.FBARRAY_FLAGS_FIXED_LEN) Then Return 3  '' supplied Fix-Len result array() too small
        Redim Preserve result(Lbound(result) To Lbound(result) + i)
    End If
   
    For j As Integer = 0 To i - 1
        result(Lbound(result) + j) = Mid(s, index(j), index(j + 1) - index(j) - Len(delimiter))
    Next j
    result(Lbound(result) + i) = Mid(s, index(i))
    Return 0  '' OK
   
End Function


Dim As String text 

' Read the CSV file To text
Open "TEST.CSV" For Binary Access Read As #1
text = String( Lof(1), 0 )
Get #1, ,text
Close #1

'Split into lines
Dim As String lineData()
split( text, lineData(), Chr(10) )

For i As Integer = 0 To Ubound( lineData )
	Print lineData(i)
Next

'Continue split the lineData by comma   .......
Post Reply