This is a bug with the Excel ODBC driver. http://support.microsoft.com/kb/288343
If you wish not to have the first row regarded as fieldnames then you will have to switch to OLE-DB (you can use ADOmate for this).
how to get data from an excel file?
Re: how to get data from an excel file?
I may look like a mule, but I'm not a complete ass.
- Michael Vogel
- Addict
- Posts: 2666
- Joined: Thu Feb 09, 2006 11:27 pm
- Contact:
Re: how to get data from an excel file?
This could be interesting as well for allowing to read excel file data without having installed Excel: Github Link
Re: how to get data from an excel file?
You can simply unzip the file and read the desired cell's content.
if the content is calculated via formula it get's a bit more tricky, though as you basically need to backtrack and calculate the value yourself.
Only works for xlsx files, though.
if the content is calculated via formula it get's a bit more tricky, though as you basically need to backtrack and calculate the value yourself.
Only works for xlsx files, though.
- Michael Vogel
- Addict
- Posts: 2666
- Joined: Thu Feb 09, 2006 11:27 pm
- Contact:
Re: how to get data from an excel file?
A lot of files are given in the *.xls format, so parsing the complex BIFF structure would be needed here. Anyhow a parser to get a specific cell value from the xml content could be also a valuable help in some cases.Derren wrote:You can simply unzip the file and read the desired cell's content.
if the content is calculated via formula it get's a bit more tricky, though as you basically need to backtrack and calculate the value yourself.
Only works for xlsx files, though.
- doctorized
- Addict
- Posts: 854
- Joined: Fri Mar 27, 2009 9:41 am
- Location: Athens, Greece
Re: how to get data from an excel file?
MakeConnection fails. PB 5.62 x86, win 10 x64.Paul wrote:You can download some sample code and a sample excel sheet here, which uses PureBasic's built in database commands...
http://www.reelmedia.org/pureproject/FILES/readxls.zip
This is the code in the ZIP file:Code: Select all
#ODBC_ADD_DSN =1 #Database =1 #Library =1 Procedure MakeConnection(Driver.s,strAttributes.s) If OpenLibrary(#Library,"ODBCCP32.DLL") Buffer=AllocateMemory(Len(strAttributes)) CopyMemory(@strAttributes,Buffer,Len(strAttributes)) For x=0 To Len(strAttributes)-1 If PeekB(Buffer+x)=Asc(";"):PokeB(Buffer+x,0): EndIf Next result=CallFunction(#Library,"SQLConfigDataSource",0,#ODBC_ADD_DSN,@Driver,Buffer) FreeMemory(Buffer) CloseLibrary(#Library) EndIf ProcedureReturn result EndProcedure file$="my stuff.xls" If MakeConnection("Microsoft Excel Driver (*.xls)","DSN=ConnectXLS;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";") If UseODBCDatabase() If OpenDatabase(#Database,"ConnectXLS","","",#PB_Database_ODBC) If DatabaseQuery(#Database,"select * from [my data$]") While NextDatabaseRow(#Database) id=0 col1 =GetDatabaseLong(#Database,id):id+1 col2$=GetDatabaseString(#Database,id):id+1 Debug Str(col1)+"..."+col2$ Wend EndIf CloseDatabase(#Database) EndIf EndIf EndIf
The code works fine with the excel in the zip but not working with every other simple xls file.