Reading an EXCEL File

[perfinit]

Is there a way to read and process an EXCEL file in Liberty Basic?  I've attached an example that I'd like to process.  The example I've attached is a small sample of a larger file.  I want to be able to process each row as a separate record, manipulate the data and then go to the next row until I reach the end.  I haven't found any direction that provides an example of how I could read and process an EXCEL file.

Test1.xlsx (8.67 KB)

[tsh73]

How about saving filer from Excel into CSV format
and reading it in LB using new INPUTCSV command?
Liberty BASIC Essentials #4 - Reading CSV files into your programs

[metro]

I have just started looking at this myself. I receive an xlsx file daily with stock market data
so for the last week or two I have been opening the file using Libre office and saving it as <tab> delimited as there are currency fields with ","'s.
the best option at this stage for me at least is to investigate command line solutions.
I am using Linux so there are a few more options.

I have now found this can be done "headless", so there is one option....install Libre Office. ask.libreoffice.org/en/question/46466/how-to-convert-specific-sheet-to-csv-via-command-line/
or........this may be another option
gallery.technet.microsoft.com/How-to-convert-Excel-xlsx-d9521619

[Rod]

metro this will be slower but you can roll your own csv input.

open "rod.dat" for output as #1
'"ABC, Inc.",222 Main Street,9","1,000.00"
print #1, chr$(34)+"ABC, Inc."+chr$(34)+",222 Main Street,9"+chr$(34)+","+chr$(34)+"1,000.00"+chr$(34)
close #1
open "rod.dat" for input as #1
dataitems=4
line input #1,l$
col=1 'the column we are at
pos=1 'the position in l$
while col<=dataitems
   'first skip past any commas so we are at the start of the data
   if mid$(l$,pos,1)="," then pos=pos+1
   'now see if it is a comma we are looking for or a quote
   if mid$(l$,pos,1)=chr$(34) then
       pos2=instr(l$,chr$(34),pos+1)
       col$=mid$(l$,pos+1,pos2-pos-1)
   else
       pos2=instr(l$,",",pos+1)
       col$=mid$(l$,pos,pos2-pos)
   end if
print col,col$
pos=pos2+1
col=col+1
wend
close #1
end


[metro]

Thanks Rod,
Maybe I have misunderstood the OP's question!
the problem I have and I assumed that perfinit was also experiencing
is ....
XLSX is a file extension for an open XML spreadsheet file format used by MicrosoftExcel.

In my limited understanding there is no easy way to extract each spreadsheet within a workbook
or even read the cell data from a single sheet whilst in the XML format.

PS your "While Wend" loop is very useful, thanks

[Rod]

Yeah, I was assuming a CSV export. I thought you were having problems with , in numbers. .xmls is zipped file format containing lots of format info, I am sure the data is there somewhere. I can find string values with 7Zip but I have yet to find numeric data. So easy way is CSV export or one of your command line solutions.

[meerkat]

I usually insert the csv file into the db directly.

Example of file with 4 values:i"abc","def","1,234.56","e'nd";
"axc","def","1,234.56","end";
"ahc","def","1,234.56","end";
"adc","d'ef","1,234.56","end";
"abxc","def","1,234.56","end";
"abc","def","1,234.56","end";
"abc","def","1,234.56","end";


Read the file and double quote single quotes from (') to ('')
replace end of each record with ")" and begin with "("
Using a single sql command it will insert all CSV's into your database.

Code Example:
open "e:\data\temp\a.txt" for input as #f        ' this reads the above file into memory
a$ = input$(#f, lof(#f))
close #f
a$ = strRep$(a$,"'","''")                        'double quote all single quotes
a$ = strRep$(a$,";"+chr$(13),"),"+chr$(13)+"(")  'place ) at tne and ( at begining
a$ = left$(a$,len(a$)-4)                         ' get rid of extra end stuff

sqliteconnect #mem, ":memory:"                   ' create example SQLite file with 4 variables
sql$    = "CREATE TABLE dta (a text,b text,c text,d text)"
#mem execute(sql$)                              

a$ = "INSERT INTO dta VALUES(";a$;")"            ' insert all the CSV records into the DB
#mem execute(a$)

end                                              ' thats all folks

' --------------------------------
' string replace rep str with
' --------------------------------
FUNCTION strRep$(str$,rep$,with$)
ln = len(rep$)
ln1 = ln - 1
i = 1
while i <= len(str$)
if mid$(str$,i,ln) = rep$ then
strRep$ = strRep$ + with$
i = i + ln1
else
strRep$ = strRep$ + mid$(str$,i,1)
end if
i = i + 1
WEND
END FUNCTION


Hope this helps..

[sarossell]

If I recall correctly, the .extX (xlsx, docx, et al) are actually zipped files with the original data file inside. By changing the .extX extension to .zip, the file can be unpacked.

[Chris Iverson]

Correct, the Office Open XML formats are zipped containers, the contents of which are defined by standards.

See officeopenxml.com/index.php and docs.microsoft.com/en-us/office/open-xml/understanding-the-open-xml-file-formats.

[perfinit]

Thank you for the suggestions - i think I have more than enough to explore. My latest attempts have been successful.

[honkytonk]

Open the file with "Genumeric" (free)
Select all
To copy
Paste in the notepad
You get a ".txt" file exploitable by JB / LB

[Sver]

If you change it into a .zip, you can use this to unzip :

@echo off
setlocal
cd /d %~dp0
Call :UnZipFile "C:\your\Destination\folder" "C:\your\file.zip"
exit /b

:UnZipFile <ExtractTo> <newzipfile>
set vbs="%temp%\_.vbs"
if exist %vbs% del /f /q %vbs%
>%vbs% echo Set fso = CreateObject("Scripting.FileSystemObject")
>>%vbs% echo If NOT fso.FolderExists(%1) Then
>>%vbs% echo fso.CreateFolder(%1)
>>%vbs% echo End If
>>%vbs% echo set objShell = CreateObject("Shell.Application")
>>%vbs% echo set FilesInZip=objShell.NameSpace(%2).items
>>%vbs% echo objShell.NameSpace(%1).CopyHere(FilesInZip)
>>%vbs% echo Set fso = Nothing
>>%vbs% echo Set objShell = Nothing
cscript //nologo %vbs%
if exist %vbs% del /f /q %vbs%


Change the folder and zip file name, Copy the text to a .txt file and change this to a .bat file. Run the .bat file to unzip.

[Sver]

To delete the folder after using data (.xml file) make a new .bat file

@echo off
set backupDir="C:\your\Destination\folder"
rmdir /s /q %backupDir%

Of course you can make a .bat file by Liberty Basic, Then it is possible to use DefaultDir$