I have number of Excel sheets that need to be imported into a database for a Xojo app, but the Excel sheet are not the right structure and there is a lot of data manipulation that needs to be done before I get them into my app.
I have determined the best way to do this is to suck them into Xojo and write some Xojo code and let Xojo move the records.
My question is, does anyone know a MySql utility that will suck in Excel in such a manner that I done have to manually create the table and fields. In other word, one that will use the title row of the Excel sheet and create the tables/fields for me?
Navicat will import from Excel and allows field mapping at import time but it is very expensive. Using ODBC you can also treat the spreadsheet as a flat file database, columns=fields, rows=records.
xlsx files are not complicated to handle yourself with some xojo methods, it’s only a folder of xml zipped files.
xls file are trickier…
I saw some open source xlsx writers for xojo I don’t remember where
and I did not find xlsx readers so I made my own.
you will never find an app that will let you reorder the cells the way you want to store in a database
you need xojo to program it yourself (and so the methods to read a cell in an xlsx file)