Importing data into SQLite from csv

[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

[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

[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

[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

[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

[Mark]

Here's some information about importing csv with the .import command form the DOS shell.

Mark

[Mark]

I had a quick look at the revDatabaseColumn* functions in LiveCode. It seems that you can read but not write by column.

Mark

[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

[Mark]

Hi,

I don't think you can do .import on mobile devices if they're not rooted/jailbroken.

Kind regards,

Mark

[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

[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!

[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