Jump to content
Sign in to follow this  
sigil

Entering 2D array w/ headers into SQLite

Recommended Posts

sigil

I have a 2D array, 400 x 400. The first row is headers for the data (I pulled it from an Excel sheet). I want to create a SQLite table with the header row for column names, then insert the rest of the rows into the table as values. Has anyone built a UDF that handles this job? I'm pretty sure I know how to write it: going through the array with a For..Next loop and concatenating a lot of single-quote marks and commas to create the SQLite statements. Just wondering if anyone has already built a function that handles the problem of passing arrays into SQLite.

Edited by sigil

Share this post


Link to post
Share on other sites
PsaltyDS

It's likely too situation-specific for a UDF, but not hard to code. One recommendation would be to code the loop to execute "BEGIN", loop through all the "ADD" lines, then execute "GO" to commit them. That makes SQLite can run that sequence internally MUCH faster than fully committing one "ADD" line at a time.

:mellow:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
jchd

I've not much to add to what our venerable pinguin already said, except this: 400 columns stinks like a doubtful design.

Anyway, try something and come back if you run into problems.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
sigil

I've not much to add to what our venerable pinguin already said, except this: 400 columns stinks like a doubtful design.

Anyway, try something and come back if you run into problems.

It's working fine so far.

What better design would you recommend? My goal is to have a table of zip code distances, so that given any two zip codes, I can look up the distance between them.

Share this post


Link to post
Share on other sites
PsaltyDS

It's working fine so far.

What better design would you recommend? My goal is to have a table of zip code distances, so that given any two zip codes, I can look up the distance between them.

Egad! Where did you get the data table source for that? Is that air miles or trucking kilometers? Does it take into account major air corridors or highway routes?

:mellow:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
jchd

If your target is inside US, I believe that factual federal data has to be public domain. The NGIS has lots of stuff online but also look Geonames.org. Now if you need bird distances, you should use the Rtree extension and even possibly Spaliatlite (builds on SQLite) for such computations and/or representations. As always, Google is ...

BTW is you 400x400 table your version of inter-cities distances? If so, I hope it's a precomputed road distance table. If it's bird distance, that doesn't make sense to store such a large table. Accessing random columns within large rows is likely not to be faster than computing actual (bird) distance knowing coordinates of both ends.

In any case, such cartesian product is not the right choice, as it won't scale and there's a limit in the number of column supported, in SQLite and in many other DBMS.

The right way to model a many-to-many relationship is having a table of cities (in your case) with Id as IPK (integer primary key) and a table of distances city_1_Id, city_2_Id, distance.

Such a scheme scales correctly.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.