Page 1 of 1
Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 9:42 pm
by midwest
From the research I have done (not much out there on this subect) it does not look like it is possible to use the revExecuteSQL for importing (.import) data into a SQLite table from a csv file. Is it possible to use the shell command to .import a csv file into a SQLite table and will it work on a stand-alone? If the .import capability is off the table, what is the most efficeint way to insert data into a SQLite table? I am assuming I will have to use a loop structure but maybe I am missing something. I want to accomplish the process within Livecode script.
thx
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 10:07 pm
by Mark
Hi,
If you log in on Sqlite3 from the shell, using the terminal or DOS prompt, you can .import a csv file. It is also possible to write a script in LiveCode that imports a csv file record by record (or perhaps even column by column).
Kind regards,
Mark
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 10:10 pm
by Klaus
Hi midwest,
1. welcome to the forum!
2. I deleted the double posting from the beginners section, here is the right place.
3. What Mark said
Best
Klaus
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 10:29 pm
by midwest
Thanks. Yes, after I posted the first one I though the database forum would be better. Could you guys explain the shell portion in more detail. Is the .import action perform at the DOS prompt or within livecode shell. How would one go about inserting columns rather than per record. I have a fair amount of data transferring and would like to speed the processing time up if possible.
Thanks again
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 10:36 pm
by Mark
Hi,
Is this a one-time job, or should it be an automated process to be performed by the users of your software?
Mark
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 10:44 pm
by Mark
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 10:45 pm
by Mark
I had a quick look at the revDatabaseColumn* functions in LiveCode. It seems that you can read but not write by column.
Mark
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 10:55 pm
by midwest
What I am trying to accomplish: I have a MySQL (centeral database) with project information. I want to give mobile users the option to download and storage project information for use while they are offline (in remote areas). Therefore I am using the MySQL "outfile" to create the csv file then insert the information into a SQLite file for future Query. The current loop / insert structure I have in place is rather slow. I was hoping the .import would speed the process up or find another way of inserting the information that is much quicker.
thx
Re: Importing data into SQLite from csv
Posted: Tue Nov 11, 2014 11:00 pm
by Mark
Hi,
I don't think you can do .import on mobile devices if they're not rooted/jailbroken.
Kind regards,
Mark
Re: Importing data into SQLite from csv
Posted: Wed Nov 12, 2014 12:00 am
by midwest
Okay, so .import is dead. What would be the most efficient loop structure to insert data into a SQLite table. Below is a snippet of my current structure, edited for space.
put url("file:" & tvPath) into tvMySQLFileArr
split tvMySQLFileArr by row
put the number of lines of (the keys of tvMySQLFileArr) into tvCountLines
put 0 into y
put "4" into tvDiv
put tvCountLines div tvDiv into tvDivCounter
put tvCountLines mod tvDiv into tvModCounter
repeat tvDivCounter times
put y into yy
put yy+1 into y
set itemdelimiter to tab
put the item 1 of tvMySQLFileArr[line 1 of y] into tvValuesA1
..... etc
put the item 1 of tvMySQLFileArr[line 1 of y] into tvValuesB1
..... etc
put the item 1 of tvMySQLFileArr[line 1 of y] into tvValuesC1
..... etc
put the item 1 of tvMySQLFileArr[line 1 of y] into tvValuesD1
..... etc
put "INSERT into drawdata VALUES ("&tvValuesA1&",.... etc into tvInsertSQLiteA
put "INSERT into drawdata VALUES ("&tvValuesB1&",.... etc into tvInsertSQLiteB
put "INSERT into drawdata VALUES ("&tvValuesC1&",.... etc into tvInsertSQLiteC
put "INSERT into drawdata VALUES ("&tvValuesD1&",.... etc into tvInsertSQLiteD
revExecuteSQL tvSQLiteID, tvInsertSQLiteA
revExecuteSQL tvSQLiteID, tvInsertSQLiteB
revExecuteSQL tvSQLiteID, tvInsertSQLiteC
revExecuteSQL tvSQLiteID, tvInsertSQLiteD
Then loop for the remainder in tvModCounter
Thanks again
Re: Importing data into SQLite from csv
Posted: Sat Nov 15, 2014 11:49 am
by AxWald
Hi,
I might be missing the point, but I do it this way:
(evil pseudo-code!)
Code: Select all
put "INSERT INTO 't_Orders' ('CST', 'PCT', 'SEC', 'ART', 'APR', 'AMG','ORV') VALUES " into MyStr //Starting an SQL string
// ... now using a loop to populate the values; and closing the ready SQL string
put revDataFromQuery(",",,MyDB, MyStr) into MyResult // whoosh, all is in MyDB ;-)
Hope this helps!
Re: Importing data into SQLite from csv
Posted: Tue Dec 16, 2014 9:47 pm
by sturgis
If you are doing inserts 1 at a time for each row of data, it can be pretty slow since sqlite opens and closes the transaction and opens and closes a journal file for each insert.
YOu can wrap a bunch of inserts inside a begin/commit structure. Look here:
http://forums.livecode.com/viewtopic.ph ... ransaction