Jump to content

SQLite performing a .import


Jewtus
 Share

Go to solution Solved by MHz,

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

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)

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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')
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...