excel file reading utilities and libraries to use with freebasic
excel file reading utilities and libraries to use with freebasic
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 ?
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.
Re: excel file reading utilities and libraries to use with freebasic
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.andykmv wrote:My target OS's are windows (7,8x,10) with no microsoft office installed
-
- Posts: 556
- Joined: Mar 10, 2007 15:44
- Location: Ohio, USA
- Contact:
Re: excel file reading utilities and libraries to use with freebasic
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.
viewtopic.php?f=8&t=13298&hilit=excel+wrapper
Not sure if is still maintained, but might get you started.
viewtopic.php?f=8&t=13298&hilit=excel+wrapper
Re: excel file reading utilities and libraries to use with freebasic
Interesting thread, but a bit confused. I wish there was a simple example showing how to get raw data from active sheet, RxCx:RyCy.
Re: excel file reading utilities and libraries to use with freebasic
That's a Excel com wrapper. It instruments Excel, so it must be installed, something the OP excluded.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.
viewtopic.php?f=8&t=13298&hilit=excel+wrapper
That means you need to get some excel reading library (readxl, libxl) and use that. (though C++ ones will be hard).
Re: excel file reading utilities and libraries to use with freebasic
using fbfrog https://github.com/dkl/fbfrog I translated the libxl header and posted some examples here viewtopic.php?p=233695#p233695
-
- Posts: 556
- Joined: Mar 10, 2007 15:44
- Location: Ohio, USA
- Contact:
Re: excel file reading utilities and libraries to use with freebasic
@marcov
Thanks for pointing that out to me.
I didn't read the full post. Just the first two lines.
Thanks for pointing that out to me.
I didn't read the full post. Just the first two lines.
Re: excel file reading utilities and libraries to use with freebasic
@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.
Please explain a bit more your context.
Re: excel file reading utilities and libraries to use with freebasic
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.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 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)
Re: excel file reading utilities and libraries to use with freebasic
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.
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
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
Last edited by andykmv on Jul 10, 2018 13:06, edited 2 times in total.
Re: excel file reading utilities and libraries to use with freebasic
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.srvaldez wrote:using fbfrog https://github.com/dkl/fbfrog I translated the libxl header and posted some examples here viewtopic.php?p=233695#p233695
Re: excel file reading utilities and libraries to use with freebasic
i am not sure where to start with readxl - have you used it yourself ? has anyone converted the headers ?marcov wrote:.... you need to get some excel reading library (readxl, libxl) and use that. (though C++ ones will be hard).
Re: excel file reading utilities and libraries to use with freebasic
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.andykmv wrote:i am not sure where to start with readxl - have you used it yourself ? has anyone converted the headers ?marcov wrote:.... you need to get some excel reading library (readxl, libxl) and use that. (though C++ ones will be hard).