Thank you for the replies. You’ve got me thinking, but perhaps not in the way you might think.
The first question that needs to be answered is where is the time being consumed on the import. It can only be in one of two places.
-
Reading the cell data from Excel
-
Writing the data to the Sqlite tables
So I first want to find out just where time can be saved.
As I stated before, this import is data driven by a table that has all the fields from all the tables that can be imported. It also has the data types for each field so it can correctly add it to the Sqlite tables. While there are literally hundreds of fields that the user can elect to import, they pick and choose the ones that they want to import and then structure their Excel file accordingly. Our application will generate an Excel template file with field names and types in the first few rows to assist them in populating their spreadsheet. Here’s a snippet of the code that populates the record before the “insert record” is executed.
while not last row_in_Excel
for x as integer = 1 to last_column_in_Excel
cValue = Excel.range(cell).value
cFieldname = get_from_data_dictionary
cFieldtype = get_from_data_dictionary
select case cFieldtype
case "TEXT"
rec.column(cFieldname) = cValue
case "DOUBLE"
rec.doublecolumn(cFieldname) = cdbl(cValue)
case "INTEGER"
rec.integercolumn(cFieldname) = cValue.val
case "LOGICAL"
rec.booleancolumn(cFieldname) = IIF(cValue = "T", True, False)
end
cell = Next_Cell()
next
INSERTRECORD
skip to next record in data_dictionary that was selected to import
wend
There’s more to it than the code above to handle various data, such as dates, that can be imported, but this gives you an idea of how little code is involved in importing the data right now because of the data dictionary. Export works more or less the same way, that is, it is data driven off the dictionary.
So I think the first step is to put some timing code in place to find out where the time is being spent… that is unless someone can see something that would make this more efficient.
Building the “insert into” SQL statement would be pretty easy except for the fact that I would have to save the cell values to variables so they could be included in the “insert into” statement.
With regards to doing ONE “insert into” statement, I don’t really see how this would be possible without first adding the data to an in memory table and then inserting from it. Keep in mind that it’s NOT just one table. There are a number of tables that are updated from the Excel spreadsheet. The data dictionary tells you what table to add the data to and so the code above would be run multiple times - each time a new table is reached in the data dictionary, it has to only read the cells from the spreadsheet that belong in that table.