SQLite performing a .import

[Jewtus]

I'm having some trouble figuring this out.

I already have the cmd script to do this import function, but I don't want to have to put the sqlite3.exe or the commands outside of the compiled autoit script.

This is the sqlite cmd line:

.import AllOptions.csv NormalizeStage

This runs very fast and I'd like to keep it that way, so I am trying to use the SQLite.au3 script:

_SQLite_SQLiteExe($SQLiteDB, ".import '" & $Filepath & "' NormalizeStage;")

This errors out, and I think its because I'm not calling the correct UDF to do this command. I was looking at _SQLite_Exec but that seems to only perform sql queries and not the import functions.

Anyone know what I need to do to get this to work inside autoit? I'm trying to avoid constructing a loop to insert the array into the table since it will take much longer.

[Jewtus]

More info:

I tried to do a couple different things and I'm stuck. I'm able to get the line to write to the console that I want to execute, but it still doesn't actually perform the steps. Here is my code:

$sqlitepath = FileOpenDialog("Select SQLITE3.EXE", @ScriptDir & "\", "All Files(*.exe)")
        $commands = ".import "&'"'&StringReplace($Filepath,"\","/")&'"'&" NormalizeStage"
        $tempfile = @ScriptDir&"\Temp.txt"
        FileWrite($tempfile,$commands)
        $executecommand='"'&$sqlitepath&'"'&' '&'"'&$SQLiteDB&'"'&'< '&'"'&$tempfile&'"'
        ConsoleWrite($executecommand)
        RunWait(@ComSpec & " /c " & $executecommand)
        FileDelete($tempfile)

when I run the line that is output with Consolewrite to a command line, it seems to work without issue. I also see the command prompt pop up when I run the script, but it doesn't hesitate long enough for me to figure out what the error is. There are no autoit errors, so I need to see what is actually executing in the command window to see what is going wrong. Again, when I try to execute the command line manually, it works without issue.

[jchd]

Reminder: the dot-commands offered by the CLI (command-line interface) are specific to this utility and are no way part of SQL.

To see what happens using the /k switch might be helpful.

[Jewtus]

Not sure I totally understand what you mean....

When I do this command line:

sqlite3.exe database.db < commands.txt

(program       file                    functions to perform)

it pulls the commands from the text file (which is why I structured it this way). I'm only putting the . command inside the text file, but when I run the exact same console output on a cmd prompt, it works without any issues.

I did however try the /k switch, and it does kick up the console, but it says it says the path is invalid (the path contains a space).

[MHz]

You mentioned CMD script. This may help you to show the difference between the command and how to apply it with AutoIt.

Here a a CMD script to built a test.db and do some tasks.

@echo off
setlocal enabledelayedexpansion

set db=test.db
if exist "%db%" del "%db%"

echo. & echo Creating the database.
sqlite3 "%db%" "begin;create table NormalizeStage(ta, tb, tc);insert into NormalizeStage values(1,2,3),(4,5,6);commit;"
echo. & echo Exporting contents of the table NormalizeStage to a csv file then delete table content.
sqlite3 -cmd ".mode csv" -cmd ".once test.csv" "%db%" "select * from NormalizeStage;delete from NormalizeStage;"
echo. & echo Display contents of csv file.
type test.csv
pause

echo. & echo Display table NormalizeStage to show contents. Expect empty return.
sqlite3 "%db%" "select * from NormalizeStage;"
pause

echo. & echo Import csv file into the table NormalizeStage.
sqlite3 -csv -cmd ".import test.csv NormalizeStage" "%db%" ""
pause

echo. & echo Display table NormalizeStage to show contents with headers on.
sqlite3 -cmd ".headers on" "%db%" "select * from NormalizeStage;"
pause

endlocal
goto :eof

Note how the script exports with mode csv. Using an external command to import seems to need mode csv to be active for a successful import.

For reference if not known, "Sqlite3 --help" will give you external help commands while "Sqlite3 .help" internally give you the internal dot commands. This gives you all the options.

After running the CMD script above, a database file exists. I tested AutoIt with this script on that database and it adds the csv content to the existing entries in the NormalizeStage table.

; import csv into test.db. It may add to the existing entries.
$pid = Run('sqlite3 -csv -cmd ".import ''test.csv'' NormalizeStage" test.db "select * from NormalizeStage;"', '', @SW_MINIMIZE, 6)
; Wait for finish and get exit code.
ProcessWaitClose($pid)
$exitcode = @extended
; Read the streams.
$stdoutput = StdOutRead($pid)
$erroutput = StdErrRead($pid)

If $exitcode Then
    ; if exit code, exit with it.
    Exit @extended
ElseIf $erroutput Then
    ; if error output, show it.
    MsgBox(0x40030, @ScriptName, $erroutput)
Else
    ; Show the std output.
    MsgBox(0x40000, @ScriptName, $stdoutput)
EndIf

All seems to be working and hopefully give you some ideas. I have not tried _SQLite_SQLiteExe() on this test yet though it will probably do it.

[Jewtus]

That's the track I needed to be led down! This is ultimately what I used:

;Set SQLite3 and import file path
                $filepath=FileOpenDialog("Select the file to import",@ScriptDir,"CSV Files(*.csv)",0,@ScriptDir)
                $sqlitepath=FileSelectFolder("Select the SQLite folder","",0,@ScriptDir)
            ;Import the data to the normalizestage
                FileCopy($filepath,$sqlitepath&'\tempfile.csv')
                $pid = Run($sqlitepath&'\sqlite3.exe' & ' -cmd ".import tempfile.csv NormalizeStage" "'&$SQLiteDB&'"', $sqlitepath, @SW_MINIMIZE, 6)
                ; Wait for finish and get exit code.
                ProcessWaitClose($pid)
                $exitcode = @extended
                ; Read the streams.
                $erroutput = StdErrRead($pid)
                If $exitcode Then
                    ; if exit code, exit with it.
                    Exit @extended
                ElseIf $erroutput Then
                    ; if error output, show it.
                    MsgBox(0x40030, @ScriptName, $erroutput)
                EndIf
                FileDelete($sqlitepath&'\tempfile.csv')