excel file reading utilities and libraries to use with freebasic

External libraries (GTK, GSL, SDL, Allegro, OpenGL, etc) questions.
andykmv
Posts: 34
Joined: Feb 12, 2015 9:50

excel file reading utilities and libraries to use with freebasic

Postby andykmv » Jul 07, 2018 6:01

I have a need to read MS Excel Files, convert them to CSV files and transfer the data into a postgres database.
i want to use freebasic so i am looking for some libaries and/or third party tools to read the excel file content, or convert the excel files to csv format.

My target OS's are windows (7,8x,10) with no microsoft office installed, and i am writing a mix of win gui and console apps to process this data.
My end target is a postgres database as the storehouse of all my raw data where i will do queries to extract datasets as the basis of preparing utilisation reports.

So far i have found these tools and have started testing a few of the CLi tools
with mixed results.

libraries & CLI tools:
http://www.softinterface.com/Convert-XLS; ConvertXLS.exe
https://www.altova.com/convert-csv;h
https://www.whitetown.com/excel-library/h;
https://www.xls-converter.com/; Advanced XLS Converter
http://www.ozgrid.com/file-conversion/c ... o-csv.htmh
https://github.com/tidyverse/readxl
http://www.libxl.com/home.html

examples
https://stackoverflow.com/questions/451 ... ing-libxls

the readxl & tidyxl tools look interesting - has anyone here used these in FB ?
Last edited by andykmv on Jul 10, 2018 13:15, edited 1 time in total.
jj2007
Posts: 945
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: excel file reading utilities and libraries to use with freebasic

Postby jj2007 » Jul 07, 2018 8:02

andykmv wrote:My target OS's are windows (7,8x,10) with no microsoft office installed
There is the free Microsoft Excel viewer. If your only problem is getting the raw data, this is an option. It even understands good ol' DDE.
bcohio2001
Posts: 497
Joined: Mar 10, 2007 15:44
Location: Ohio, USA
Contact:

Re: excel file reading utilities and libraries to use with freebasic

Postby bcohio2001 » Jul 07, 2018 21:51

Just did a quick search … remembered seeing something on here that might help you out.
Not sure if is still maintained, but might get you started.

https://www.freebasic.net/forum/viewtopic.php?f=8&t=13298&hilit=excel+wrapper
jj2007
Posts: 945
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: excel file reading utilities and libraries to use with freebasic

Postby jj2007 » Jul 08, 2018 6:47

Interesting thread, but a bit confused. I wish there was a simple example showing how to get raw data from active sheet, RxCx:RyCy.
marcov
Posts: 2650
Joined: Jun 16, 2005 9:45
Location: Eindhoven, NL
Contact:

Re: excel file reading utilities and libraries to use with freebasic

Postby marcov » Jul 08, 2018 11:34

bcohio2001 wrote:Just did a quick search … remembered seeing something on here that might help you out.
Not sure if is still maintained, but might get you started.

https://www.freebasic.net/forum/viewtopic.php?f=8&t=13298&hilit=excel+wrapper


That's a Excel com wrapper. It instruments Excel, so it must be installed, something the OP excluded.

That means you need to get some excel reading library (readxl, libxl) and use that. (though C++ ones will be hard).
srvaldez
Posts: 1764
Joined: Sep 25, 2005 21:54

Re: excel file reading utilities and libraries to use with freebasic

Postby srvaldez » Jul 08, 2018 12:28

using fbfrog https://github.com/dkl/fbfrog I translated the libxl header and posted some examples here viewtopic.php?p=233695#p233695
bcohio2001
Posts: 497
Joined: Mar 10, 2007 15:44
Location: Ohio, USA
Contact:

Re: excel file reading utilities and libraries to use with freebasic

Postby bcohio2001 » Jul 10, 2018 1:04

@marcov
Thanks for pointing that out to me.
I didn't read the full post. Just the first two lines.
jj2007
Posts: 945
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: excel file reading utilities and libraries to use with freebasic

Postby jj2007 » Jul 10, 2018 2:14

@OP: Do you really just need the raw data? If yes, why don't you just do copy & paste using the free Excel viewer? That would give you tab-delimited format on the clipboard - very easy to handle.

Please explain a bit more your context.
andykmv
Posts: 34
Joined: Feb 12, 2015 9:50

Re: excel file reading utilities and libraries to use with freebasic

Postby andykmv » Jul 10, 2018 11:22

jj2007 wrote:Interesting thread, but a bit confused. I wish there was a simple example showing how to get raw data from active sheet, RxCx:RyCy.


i have a bit of working code now using the libxl library - nice library, a bit of work though having to figure out utilisation of the library as there are only a few example bits of code in C++, but i am starting the get the swing of it. i am doing this as i have a steady stream of data being extracted from a production database by a third party product in the form of excel files and i need to consolidate and deduplicate the data before uploading new records into a database so that i can extract stats from the data set.

i have three versions of excel file formats used, from two different systems, in two different excel file formats with 3 distinct data structures for one type of data only (there are other data types in differently formatted files also to be extracted but thats another separate task) so rather than try to manually modify the data into a standard format (as the data was growing more quickly than i could manually process it) i am writing a tool to read the raw data using freebasic and transform each file format type into a standardised data structure. then i can load up a database and have some fun with sql and excel pivot tables etc.

the libxl library has the tools to extract the cell data by row, col but in it's unregistered/demo state it only allows a limited number of rows to be extracted - you have to pay to get the full functionality, so at the moment i am testing the library's functionality to see whether i want to buy it, or consider a different library or third party utility.

i'll upload some code samples when i get a little more of the library functions working (but my code is going to be pretty awful and inefficient so no laughin)
andykmv
Posts: 34
Joined: Feb 12, 2015 9:50

Re: excel file reading utilities and libraries to use with freebasic

Postby andykmv » Jul 10, 2018 11:34

this is a work in progress and is just a few snippets really to just read out some cell values...the main code sample i used was from the libxl website & some ideas off stackflow.

Code: Select all

#include "libxl.bi"
#inclib "xl"

Declare Function xlwrite () As long
Declare Function xlread () As long
Declare function xlreaddata() as long

dim as long ii,jj,kk=0
dim d as integer

'========================================
'MAIN PROGRAM
'========================================

Print "The purpose of this program is to demonstrate the use of the libxl library to extract the excel data "
print

'call function xlwrite()
'this function creates a simple excel spreadsheet and hworks fine.
'ii=xlwrite()
'print "xlwrite() returned ";ii
'print

'call function xlread()
'some of the library calls in this function i havent got working properly yet (not getting data in the right format)
jj=xlread()
print "xlread() returned ";jj
print

' call function xlreaddata())
'library calls in this function i are mostly working (a couple of lines to convert from c++ still in the select case section)
kk=xlreaddata()
print "xlread() returned ";kk
print

sleep
END
'END MAIN


'==================================================================================================
' SUBS & FUNCTIONS
'==================================================================================================

'--------------------------------------------------------------------------------------------------
function xlwrite() as long
'--------------------------------------------------------------------------------------------------
   print
   print "Function xlwrite()"
   print
   Dim as BookHandle book = xlCreateBook()         'use this if XLS workbook
   'Dim as BookHandle book = xlCreateXMLBook()         'use this if xlsx XML workbook
   if book then
      Dim as SheetHandle sheet = xlBookAddSheet(book, "Sheet1", NULL)
      if sheet then
         xlSheetWriteStr(sheet, 2, 1, wstr("Hello, World !"), NULL)
         xlSheetWriteNum(sheet, 3, 1, 1000, NULL)
      end if
      xlBookSave(book, wstr("example.xls"))
      xlBookRelease(book)
   end if
   return 10
end function

'--------------------------------------------------------------------------------------------------
function xlread() as long
'--------------------------------------------------------------------------------------------------
   print
   print "Function xlread()"
   print
   'Dim as BookHandle book = xlCreateXMLBook()         'use this if xlsx XML workbook
   Dim as BookHandle book = xlCreateBook()         'use this if XLS workbook
   if book then
      print "bookhandle=";book
      
      'declare function xlBookBiffVersionA(byval handle as BookHandle) as long
      'declare function xlBookBiffVersion alias "xlBookBiffVersionA"(byval handle as BookHandle) as long

      dim  bb as long
      bb = xlBookBiffVersion(book)
      print "bookbiffversion=";*bb
      bb = xlBookBiffVersionA(book)
      print "bookbiffversionA=";*hbb
      bb = xlBookVersionA(book)
      print "xlBookVersionA=";bb
      'xlBookSheetCountA(byval handle as BookHandle) as long
      bb = xlBookSheetCountA(book)
      print "xlBookSheetCountA=";bb
      bb = xlBookSheetCount(book)
      print "xlBookSheetCount=";bb


      if xlBookLoad(book, "example.xls") then
         print "book loaded"
         Dim as SheetHandle sheet = xlBookGetSheet(book, 0)
         if sheet then
            dim d as double
'            dim as string s = xlSheetReadStr(sheet, 3, 1, NULL)
'            dim as string s = xlSheetReadStr(sheet, 3, 1, NULL)
            'dim as const zstring ptr s
            dim as const zstring ptr s => xlSheetReadStr(sheet, 0, 0, NULL)

            print "printing ; s =";s
            print "0,0 ";xlSheetReadStr(sheet, 0, 0, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "1,0 "; xlSheetReadStr(sheet, 1, 0, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "2,0 "; xlSheetReadStr(sheet, 2, 0, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "0,1 "; xlSheetReadStr(sheet, 0, 1, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "1,1 "; xlSheetReadStr(sheet, 1, 1, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "2,1 "; xlSheetReadStr(sheet, 2, 1, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "0,2 "; xlSheetReadStr(sheet, 0, 2, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "1,2 "; xlSheetReadStr(sheet, 1, 2, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "2,2 "; xlSheetReadStr(sheet, 2, 2, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "3,1 "; xlSheetReadStr(sheet, 3, 1, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            print "4,0 "; xlSheetReadStr(sheet, 4, 0, NULL); " Sizeof s=";SizeOf(s);" Length of s=";Len(s)
            
            dim as long cc
            print "xlsheetcelltype 0,0 "; xlSheetCellTypeA(Sheet, 0, 0)
            print "xlsheetcelltype 1,0 "; xlSheetCellTypeA(Sheet, 1, 0)
            print "xlsheetcelltype 2,0 "; xlSheetCellTypeA(Sheet, 2, 0)
            print "xlsheetcelltype 3,0 "; xlSheetCellTypeA(Sheet, 3, 0)
            print "xlsheetcelltype 4,0 "; xlSheetCellTypeA(Sheet, 4, 0)
            print "xlsheetcelltype 5,0 "; xlSheetCellTypeA(Sheet, 5, 0)
            print "xlsheetcelltype 6,0 "; xlSheetCellTypeA(Sheet, 6, 0)
            print "xlsheetcelltype 7,0 "; xlSheetCellTypeA(Sheet, 7, 0)

            print "xlsheetcelltype 0,1 "; xlSheetCellTypeA(Sheet, 0, 1)
            print "xlsheetcelltype 1,1 "; xlSheetCellTypeA(Sheet, 1, 1)
            print "xlsheetcelltype 2,1 "; xlSheetCellTypeA(Sheet, 2, 1)
            print "xlsheetcelltype 3,1 "; xlSheetCellTypeA(Sheet, 3, 1)
            print "xlsheetcelltype 4,1 "; xlSheetCellTypeA(Sheet, 4, 1)
            print "xlsheetcelltype 5,1 "; xlSheetCellTypeA(Sheet, 5, 1)
            print "xlsheetcelltype 6,1 "; xlSheetCellTypeA(Sheet, 6, 1)
            print "xlsheetcelltype 7,1 "; xlSheetCellTypeA(Sheet, 7, 1)

            'declare function xlSheetReadNumA(byval handle as SheetHandle, byval row as long, byval col as long, byval format as FormatHandle ptr) as double
            'dim as double dd = xlSheetReadNumA(sheet, row,  col, FormatHandle ptr)
            

            'if(s) wprintf(L"%s\n", s) then
               'd = xlSheetReadNum(sheet, 3, 1, NULL)
               'printf("%g\n", d)
            'endif
         endif   
      endif
       xlBookRelease(book)
   endif
   return 100
end function

'--------------------------------------------------------------------------------------------------
function xlreaddata() as long
' Reading Excel Data.  using namespace libxl;
' This example reads data from all cells of sheet, detects type of cells and prints theirs values.
'--------------------------------------------------------------------------------------------------
   print
   print "Function xlreaddata()"
   print
   Dim as BookHandle book = xlCreateBook()         'use this if XLS workbook
   Dim as integer row,col,x=0
   dim as long rr
'   if xlBookLoad(book, "example.xls") then
   if xlBookLoad(book, "sch342.xls") then
      print "bookhandle=";book
      Dim as SheetHandle sheet = xlBookGetSheet(book, 0)
      if sheet then
         dim as long row=xlSheetFirstRow(Sheet)
         print "xlSheetFirstRow=";row
         dim as long col=xlSheetFirstCol(Sheet)
         print "xlSheetFirstCol=";col
         dim as long lastrow=xlSheetLastRow(Sheet)
         print "xlSheetLastRow containing data=";lastrow-1
         dim as long lastcol=xlSheetLastCol(Sheet)
         print "xlSheetlastCol containing data=";lastcol-1
         
         for row = 0 to xlSheetLastRow(Sheet)-1      
            x=row
            print "row=";row;" ";
            for col = 0 to xlSheetLastCol(Sheet)-1
               dim as CellType cellType = xlSheetCellType(sheet, row, col)
               if xlSheetIsFormula(sheet, row, col) then
                  dim as const zstring ptr s => xlSheetReadFormula(sheet, row, col, NULL)
                  print "formula=[";s;"]"
               else
                  Select case cellType   'type long
                     case 0   'CELLTYPE_EMPTY
                        print !"\"\"";
                        exit select
                     case 1   'CELLTYPE_NUMBER
                        dim as double d = xlSheetReadNum(sheet, row, col, NULL)
                        print d;
                        exit select
                     case 2   'CELLTYPE_STRING
                        dim as const zstring ptr s = xlSheetReadStr (sheet, row, col, NULL)
                        print *s;
                        exit select
                     case 3   'CELLTYPE_BOOLEAN
'                        dim as bool b = sheet=>readBool(row, col)
'                        print b;
                        exit select
                     case 4   'CELLTYPE_BLANK
                        print !"\"\#BLNK\"";
                        exit select
                     case 5   'CELLTYPE_ERROR
                        print !"#ERR";
                        exit select
                  end select
               endif
               if col<lastcol-1 then
                  print ",";
               else
                  print;
               endif   
            next col
            print
         next row
      endif
   endif
   xlBookRelease(book)
   print "end - row x =";x
   rr=cast(Long,x)
   return rr
end function



the data contains date/time stamps in a long format which excel cannot handle when it comes to sorting, lookup tables and pivot tables. i'll have to write some code to convert the date/time stamp into separate date, time columns before i can deduplicate it or load it into the database
Last edited by andykmv on Jul 10, 2018 13:06, edited 2 times in total.
andykmv
Posts: 34
Joined: Feb 12, 2015 9:50

Re: excel file reading utilities and libraries to use with freebasic

Postby andykmv » Jul 10, 2018 12:45

srvaldez wrote:using fbfrog https://github.com/dkl/fbfrog I translated the libxl header and posted some examples here viewtopic.php?p=233695#p233695


that's the thread i first looked at and why i dug in to look at libxl! nice work on the header conversion - i have been using the bi file in conjunction with sample code to work things out too.
andykmv
Posts: 34
Joined: Feb 12, 2015 9:50

Re: excel file reading utilities and libraries to use with freebasic

Postby andykmv » Jul 10, 2018 12:50

marcov wrote:.... you need to get some excel reading library (readxl, libxl) and use that. (though C++ ones will be hard).


i am not sure where to start with readxl - have you used it yourself ? has anyone converted the headers ?
marcov
Posts: 2650
Joined: Jun 16, 2005 9:45
Location: Eindhoven, NL
Contact:

Re: excel file reading utilities and libraries to use with freebasic

Postby marcov » Jul 10, 2018 16:15

andykmv wrote:
marcov wrote:.... you need to get some excel reading library (readxl, libxl) and use that. (though C++ ones will be hard).


i am not sure where to start with readxl - have you used it yourself ? has anyone converted the headers ?


No, I had it on the todo list, but then fpspreadsheet appeared in the FPC/Lazarus world, and I have used that since. Because it has no library requirements.

Return to “Libraries”

Who is online

Users browsing this forum: No registered users and 1 guest