SQLite with command line parameters for import

[Jfish]

All:

I just started using SQLite and think it is awesome. I got to a point in my project where I desire an "import data" function that will pull information directly into SQLite. I have read the forum postings and figured out the process for doing this from the command line:

SQLite> testimport.db

SQLite> .separator ,

SQLite> .import importdata.csv testdata

This works. I now wish to call the SQlite executable with those parameters and automate the process (I can do it with the send function but prefer to avoid that if possible). I came across the $cmdLine array and was able to set the above parameters in Scite (not sure how to do it without Scite). However, it is not working. Here is what I did:

ShellExecute(@ScriptDir&"\sqlite3.exe",$cmdLine[1] & $cmdLine[2] & $cmdLine[3],@ScriptDir)

I am attaching the JPG of the parameters in Scite.

Any help you could offer would be greatly appreciated.

JFish

[rvn]

try this

ShellExecute(@ScriptDir&"\sqlite3.exe",$cmdLine[1] & ' ' & $cmdLine[2] & ' ' & $cmdLine[3],@ScriptDir)

[Jfish]

try this

ShellExecute(@ScriptDir&"\sqlite3.exe",$cmdLine[1] & ' ' & $cmdLine[2] & ' ' & $cmdLine[3],@ScriptDir)

Thanks. I tried that but got the same result as when I tried it without the spaces. The script runs to completion but there is no data in the database. Are my parameters properly formatted (they appear in the jpg on my posting).

[rvn]

sorry for my rapid stupid answer

now i see what u want,,, btw, i dont look at your picture before,,, my shame...

how bout send multiple time using send,,, something like this:

winactivate("SQLlite window title")
winwaitactive("SQLlite window title")
send ($parameter1)
sleep(2000)
send ($parameter2)
sleep(2000)
send ($parameter3)
sleep(2000)

cos sometime,,, lame code is what we need

[Jfish]

sorry for my rapid stupid answer

now i see what u want,,, btw, i dont look at your picture before,,, my shame...

how bout send multiple time using send,,, something like this:

winactivate("SQLlite window title")
winwaitactive("SQLlite window title")
send ($parameter1)
sleep(2000)
send ($parameter2)
sleep(2000)
send ($parameter3)
sleep(2000)

cos sometime,,, lame code is what we need

LOL. Yes, I already got it to work with some lame code - similar to your post. However, I was hoping to avoid using the Send function because it is not always reliable without the appropriate wait times etc. I think it would be much cleaner to be able to send multiple parameters to the SQLite exe. I can sucessfully send the database name as a parameter. It appears to ignore the next two parameters after database for some reason.

[willichan]

I now wish to call the SQlite executable with those parameters and automate the process (I can do it with the send function but prefer to avoid that if possible).

Have you considered just reading in the CSV, and inserting each line into the database from the script? You should be able to do it with a simple loop.

[Jfish]

Have you considered just reading in the CSV, and inserting each line into the database from the script? You should be able to do it with a simple loop.

@willchan

Yes, I did consider that. I would think it would likely be slower with large amounts of data though I have not tested that theory. I can get data in with other approaches. I know I can get the data in with your suggestion or with the "lame" sleep method approach referenced above. However, I was hoping to learn how to leverage the command line import function of SQLite. If it can't be done that way then I will resign myself to one the other approaches that I know will work.

[GEOSoft]

Have you considered looking at the SQlite functions in the help file?

Try the _SQLite_Exec() With the Insert Into. Look at the example for that function.

[Jfish]

Have you considered looking at the SQlite functions in the help file?

Try the _SQLite_Exec() With the Insert Into. Look at the example for that function.

@GEOSoft

Thank you for your reply. Yes, I did read the help file - and that example as well. Please correct me if I am wrong but that is using the SQL command to insert values into a table. In that case the values are hard coded (or they could be from somewhere within the Au3 script). I am trying to import a file with values into an existing table. I realize that I could read the file into my application in an array an use insert with the _SQLite_Exec() function. However, I am trying to figure out if I can just load the file directly with the .import function that SQLite offers from the commandline by passing parameters to the command line that include the items in my first post. Is that possible? Also, am I miscontruing your point on the help file?

[willichan]

I would think it would likely be slower with large amounts of data though I have not tested that theory.

With large amounts of data, writing each line separately would probably be slower. You could encapsulate blocks of data in "BEGIN" and "COMMIT" statements, however, and see an increase in performance.

For example:

_SQLite_Exec($sSQLiteDB, "BEGIN;")
...
_SQLite_Exec($sSQLiteDB, "INSERT INTO " & $sMyTableName & " VALUES " & $MyData[$i])
...
_SQLite_Exec($sSQLiteDB, "COMMIT;")

If you are going to be importing very large amounts of data, try splitting your data up into blocks of 50-100 lines of data per transaction block.

Edit: Since the command line requires multiple commands, I do not see a way that you can do it with the command line without using the method referred to above as the lame sleep method.

Edited July 26, 2010 by willichan

[Yoriz]

SQLite> testimport.db

SQLite> .separator ,

SQLite> .import importdata.csv testdata

=

_SQLite_SQLiteExe($sDatabaseFilePath, ".separator ," & @CRLF & ".import '" & FileGetShortName($hImportFilePath) & "' TableName" & @CRLF, $sOutputFile)

expandcollapse popup

#include <File.au3>
#include <SQLite.au3>

_CreateDatabaseFile()
_ImportTxtToSql()
_ReadArrayToSql()

Func _CreateDatabaseFile()
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($hFile) Then Return
    Local $iArraysize = 1000, $aArray[$iArraysize]
    For $i = 0 To $iArraysize - 1 Step 1
        $aArray[$i] = $i & "-One," & $i & "-Two," & $i & "-Three," & $i & "-Four"
    Next
    _FileWriteFromArray($hFile, $aArray)
EndFunc   ;==>_CreateDatabaseFile

Func _ImportTxtToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    _SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);", $sOutputFile)
    Local $iTimmer = TimerInit()
    _SQLite_SQLiteExe($sDatabaseFile, ".mode csv" & @CRLF & ".import '" & FileGetShortName($hFile) & "' test" & @CRLF, $sOutputFile)
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to import file" & @CR)
EndFunc   ;==>_ImportTxtToSql

Func _ReadArrayToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    Local $aArray
    _FileReadToArray($hFile, $aArray)
    _SQLite_Startup()
    _SQLite_Open($sDatabaseFile)
    _SQLite_Exec(-1, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);")
    Local $iTimmer = TimerInit()
    _SQLite_Exec(-1, "Begin")
    For $i = 1 To $aArray[0] Step 1
        _SQLite_Exec(-1, "INSERT INTO test values ('" & StringReplace($aArray[$i], ",", "','") & "');")
    Next
    _SQLite_Exec(-1, "END ;")
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to read & insert array" & @CR)
    _SQLite_Close()
    _SQLite_Shutdown()
EndFunc   ;==>_ReadArrayToSql

158.833791645834 :Time to import file

334.608682332621 :Time to read & insert array

[Jfish]

SQLite> testimport.db

SQLite> .separator ,

SQLite> .import importdata.csv testdata

=

_SQLite_SQLiteExe($sDatabaseFilePath, ".separator ," & @CRLF & ".import '" & FileGetShortName($hImportFilePath) & "' TableName" & @CRLF, $sOutputFile)

expandcollapse popup

#include <File.au3>
#include <SQLite.au3>

_CreateDatabaseFile()
_ImportTxtToSql()
_ReadArrayToSql()

Func _CreateDatabaseFile()
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($hFile) Then Return
    Local $iArraysize = 1000, $aArray[$iArraysize]
    For $i = 0 To $iArraysize - 1 Step 1
        $aArray[$i] = $i & "-One," & $i & "-Two," & $i & "-Three," & $i & "-Four"
    Next
    _FileWriteFromArray($hFile, $aArray)
EndFunc   ;==>_CreateDatabaseFile

Func _ImportTxtToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    _SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);", $sOutputFile)
    Local $iTimmer = TimerInit()
    _SQLite_SQLiteExe($sDatabaseFile, ".mode csv" & @CRLF & ".import '" & FileGetShortName($hFile) & "' test" & @CRLF, $sOutputFile)
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to import file" & @CR)
EndFunc   ;==>_ImportTxtToSql

Func _ReadArrayToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    Local $aArray
    _FileReadToArray($hFile, $aArray)
    _SQLite_Startup()
    _SQLite_Open($sDatabaseFile)
    _SQLite_Exec(-1, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);")
    Local $iTimmer = TimerInit()
    _SQLite_Exec(-1, "Begin")
    For $i = 1 To $aArray[0] Step 1
        _SQLite_Exec(-1, "INSERT INTO test values ('" & StringReplace($aArray[$i], ",", "','") & "');")
    Next
    _SQLite_Exec(-1, "END ;")
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to read & insert array" & @CR)
    _SQLite_Close()
    _SQLite_Shutdown()
EndFunc   ;==>_ReadArrayToSql

Thank you! That is exactly what I was looking for. I am going to test it tonight.

[Jfish]

Thank you! That is exactly what I was looking for. I am going to test it tonight.

Okay, I could not wait - already tested it. It works. It may be me but I could not figure out how to do that from reading the examples in the help file. I really appreciate the help.

[Yoriz]

Your welcome , One thing to note when importing, watch out for any of the data having coma's it will throw the delimeter all out of wack.

[Jfish]

Your welcome , One thing to note when importing, watch out for any of the data having coma's it will throw the delimeter all out of wack.

That makes sense. Thanks for the tip and thanks again for taking the time to post the solution.

[ParoXsitiC]

Does this require the beta?

I dont think it's working for me.

expandcollapse popup

#include <File.au3>
#include <SQLite.au3>


Local $hFile = @ScriptDir & "\Test.Txt"
Local $iArraysize = 1000, $aArray[$iArraysize]
For $i = 0 To $iArraysize - 1 Step 1
    $aArray[$i] = $i & "-One," & $i & "-Two," & $i & "-Three," & $i & "-Four"
Next
_FileWriteFromArray($hFile, $aArray)

Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
Local $hFile = @ScriptDir & "\Test.Txt"
If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
_SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);", $sOutputFile)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') :(' & @MIN & ':' & @SEC & ':' & @MSEC & ') $sOutputFile = ' & $sOutputFile & '   :   Error code: ' & @error & '    Extended code: ' & @extended & @CRLF) ;### Debug Console
Local $iTimmer = TimerInit()
_SQLite_SQLiteExe($sDatabaseFile, ".mode csv" & @CRLF & ".import '" & FileGetShortName($hFile) & "' test" & @CRLF, $sOutputFile)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') :(' & @MIN & ':' & @SEC & ':' & @MSEC & ') $sOutputFile = ' & $sOutputFile & '   :   Error code: ' & @error & '    Extended code: ' & @extended & @CRLF) ;### Debug Console
ConsoleWrite(TimerDiff($iTimmer) & " :Time to import file" & @CR)


_SQLite_Startup()
_SQLite_Open($sDatabaseFile)



Local $aResult, $iRows, $iColumns, $iRval

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM test;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf



Exit

I am getting

@@ Debug(45) :(12:33:443) $sOutputFile = : Error code: 1 Extended code: 0

@@ Debug(48) :(12:33:443) $sOutputFile = : Error code: 1 Extended code: 0

0.327353336865404 :Time to import file

! SQLite.au3 Error

--> Function: _SQLite_Query

--> Query: SELECT * FROM test;

--> Error: no such table: test

+>02:12:34 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 3.390

Seems the database is never created? Edited August 16, 2010 by ParoXsitiC

[jchd]

Place _SQlite_Startup() before the import code. This is necessary with the current release, but will be corrected in the next one (when is another question entirely).