excel file reading utilities and libraries to use with freebasic

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

excel file reading utilities and libraries to use with freebasic

Post by andykmv »

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: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

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

Post by jj2007 »

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: 556
Joined: Mar 10, 2007 15:44
Location: Ohio, USA
Contact:

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

Post by bcohio2001 »

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
jj2007
Posts: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

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

Post by jj2007 »

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: 3455
Joined: Jun 16, 2005 9:45
Location: Netherlands
Contact:

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

Post by marcov »

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'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: 3373
Joined: Sep 25, 2005 21:54

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

Post by srvaldez »

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

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

Post by bcohio2001 »

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

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

Post by jj2007 »

@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: 58
Joined: Feb 12, 2015 9:50

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

Post by andykmv »

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: 58
Joined: Feb 12, 2015 9:50

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

Post by andykmv »

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: 58
Joined: Feb 12, 2015 9:50

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

Post by andykmv »

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: 58
Joined: Feb 12, 2015 9:50

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

Post by andykmv »

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: 3455
Joined: Jun 16, 2005 9:45
Location: Netherlands
Contact:

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

Post by marcov »

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.
Post Reply