Sign in to follow this  
Followers 0
t0nZ

"universal" spreadsheet to sqlLite converter

2 posts in this topic

#1 ·  Posted (edited)

Hi to all.

I wrote a simple script with GUI to convert an openoffice calc sheet into a SqlLite table.

The script open an OpenOffice calc sheet file (or a Excel .Xls file, you have BTW to have openoffice installed)

and you have to input folder of the sqllite DB, name of the DB and table name.

The script aims to be an "universal" converter, the condition is

that the sheet has to be organized in orizontal data rows (records in DB)

and the columns of the first row are the names of the fiels.

Important: Column limit on the right side and row limit on the down side

has to be esplicited with the string "endend" in the cells

So you can use the program to quickly populate varius tables of the same DB with many sheets.

Warnings:

Launch the script with the sheet (openoffice) closed, and close it after every import.

The script don't control if a table already exist, in this case this gives an sql error but the records are added.

I include the script and a test xls file formatted for import.

I include a preview of the gui.

I include also the OOoCom.udf used (and slightly modified) by me ( thanx to user Leagnus) , because there are on the street several udf.

Comments and advices are welcome !

Edit : re-uploaded the source - some bugfix for errors with sheets with many hundreds of rows.

Edit : new version and now you can also set right and bottom limits from the gui with numeric values, and fixed problem with numeric value zero in cells

xls2sqllite.zip

post-43241-12850804887375_thumb.jpg

Edited by t0nZ

Share this post


Link to post
Share on other sites



Hi,

great script !

... but i have an error :

U:mooviideo_devautoit_webtempouniversal spreadsheet to sqlLite converterxls2sqlliteOOoCom_UDF_nsc.au3 (196) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

Return $row[$sColumn]

Return ^ ERROR

->00:50:56 AutoIT3.exe ended

My sheet is 13colx499row. Too big for your UDF ?

Thks for the answer.

Regards

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0