How do I read a Text or CSV file from EXCEL
How do I read a Text or CSV file from EXCEL
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?
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?
Re: How do I read a Text or CSV file from EXCEL
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
-
- Posts: 1188
- Joined: May 08, 2006 21:58
- Location: Crewe, England
Re: How do I read a Text or CSV file from EXCEL
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.
If using GET, you will need to read all fields in the line. Also watch out for files containing headers.
Re: How do I read a Text or CSV file from EXCEL
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:
"<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
Re: How do I read a Text or CSV file from EXCEL
I think I either created a bad .CSV file or forgot to use as a file extension of .CSV. Works now. Thanks shadow008!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
Re: How do I read a Text or CSV file from EXCEL
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.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.
Re: How do I read a Text or CSV file from EXCEL
Nice code. It will be useful for stripping quotation marks. I deeply appreciate you sharing your code! Thanks Berkeley!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#?)...
Re: How do I read a Text or CSV file from EXCEL
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:
CASE CLOSED: Everything works fine now.
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
Re: How do I read a Text or CSV file from EXCEL
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:
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 .......