how to get data from an excel file?

Just starting out? Need help? Post your questions and find answers here.
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Re: how to get data from an excel file?

Post by srod »

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).
I may look like a mule, but I'm not a complete ass.
User avatar
Michael Vogel
Addict
Addict
Posts: 2666
Joined: Thu Feb 09, 2006 11:27 pm
Contact:

Re: how to get data from an excel file?

Post by Michael Vogel »

This could be interesting as well for allowing to read excel file data without having installed Excel: Github Link
User avatar
Derren
Enthusiast
Enthusiast
Posts: 313
Joined: Sat Jul 23, 2011 1:13 am
Location: Germany

Re: how to get data from an excel file?

Post by Derren »

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.
User avatar
Michael Vogel
Addict
Addict
Posts: 2666
Joined: Thu Feb 09, 2006 11:27 pm
Contact:

Re: how to get data from an excel file?

Post by Michael Vogel »

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.
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.
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: how to get data from an excel file?

Post by doctorized »

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

MakeConnection fails. PB 5.62 x86, win 10 x64.
The code works fine with the excel in the zip but not working with every other simple xls file.
Post Reply