SQLite query to file

[Apzo]

Hello

I love SQLite Data Browser, but it can't export the result of a query.

This little script may be useful for that.

It's a simple GUI.

- Connect to an existing SQLite DB

- Type your query

- Get the result in an array

- Export it to .csv file (optional)

- Queep your favourite SQL queries, and call them back later

TO DO :

- Favourite deletion

- Disconnect button

- Automatic SQL history

- Other output formats than ";" separated (@TAB, |, ...)

Have fun !

Apzo

expandcollapse popup

#cs ----------------------------------------------------------------------------

 AutoIt Version: 3.3.6.1
 Author:         Apzo

 Script Function:
    - Display and export to .csv file an SQLite query
    - Queep your usual requests

 TODO :
    - Disconnect button, to swith databases
    - Automatic history

#ce ----------------------------------------------------------------------------

#region Includes & opts
#include <File.au3>
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>

Opt("GUIOnEventMode", 1)
Opt("MustDeclareVars", 1)
#endregion

#region Vars
Global $aResult, $iRows, $iColumns, $iRval  ; Usual SQLite stuff
Global $GUI                                 ; GUI handle
Global $X = 500                             ; GUI X size
Global $Y = 300                             ; GUI Y size
Global $ReqInput                            ; Input box for the query
Global $GoButton                            ; Go button
Global $AddButton                           ; Add to favorites button
Global $IniFile = "DbExport.ini"            ; Ini file
Global $ConnectButton                       ; Connect button
Global $ReqList                             ; Known query list
Global $ToReqButton                         ; List to query input button
Global $Title = "SQLite Export"             ; Window title
#endregion

; ------------------ GUI PART -----------------------------------------------------
#region GUI
$GUI = GUICreate($Title, $X, $Y, -1, -1)
$ReqInput = GUICtrlCreateEdit("", 5, 40, $X-10, $Y-90)
$ConnectButton = GUICtrlCreateButton("Connect to database", $X-150, $Y-40, 140, 30)
$GoButton = GUICtrlCreateButton("Go", 5, $Y-40, 50, 30)
$AddButton = GUICtrlCreateButton("> Favourites", 70, $Y-40, 80, 30)
$ReqList = GUICtrlCreateCombo("", 5, 8, 300, 30)
$ToReqButton = GUICtrlCreateButton("Use this query", 315, 5, 180, 30)

GUICtrlSetOnEvent($ConnectButton, "_Connect")
GUICtrlSetOnEvent($GoButton, "_OuputReq")
GUICtrlSetOnEvent($AddButton, "_FavAdd")
GUICtrlSetOnEvent($ToReqButton, "_UseReq")

GUICtrlSetState($GoButton, $GUI_DISABLE)
GUICtrlSetState($AddButton, $GUI_DISABLE)
GUICtrlSetState($ReqList, $GUI_DISABLE)
GUICtrlSetState($ToReqButton, $GUI_DISABLE)

_ListReq()   ; filling the combo with known queries

GUISetState(@SW_SHOW)
GUISetOnEvent($GUI_EVENT_CLOSE, "_Bye")
WinSetTitle($Title, "", $Title & " - Disconnected")
#endregion

; Start the SQLite engine
_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "Can't start SQLite, bye.", 10)
    Exit - 1
EndIf

While 1 ; Main loop, nothing more to do...
    Sleep(1000)
WEnd

; Reset and fill the query combo
Func _ListReq()
    GUICtrlSetData($ReqList, "")
    Local $lr = IniReadSection($IniFile, "SQL")
    If @Error Then Return
    Local $c = ""
    For $i = 1 To $lr[0][0]
        $c &= "|" & $lr[$i][0]
    Next
    GUICtrlSetData($ReqList, $c)
EndFunc

; Put a known query into the input area
Func _UseReq()
    Local $lc = GuiCtrlRead($ReqList)
    If $lc == "" Then Return
    Local $req = IniRead($IniFile, "SQL", $lc, "??")
    GuiCtrlSetData($ReqInput, StringReplace($req, "££", @CRLF))
EndFunc

; Put the current query into favourites
Func _FavAdd()
    Local $req = GuiCtrlRead($ReqInput)
    If StringStripWS($req, 8) == "" Then Return
    Local $l = InputBox("Favourites", "Wich name for this query ?", "", " M")
    If $l == "" Then Return
    IniWrite($IniFile, "SQL", $l, StringReplace($req, @CRLF, "££"))
    _ListReq() ; Update the favourites
    GUICtrlSetData($ReqList, $l)
EndFunc

; Choose the DB to connect
Func _Connect()
    Local $db = FileOpenDialog("DB file selection", @WORKINGDIR, "db file (*.db)", 1)
    If @Error Then Return
    _SQLite_Open($db)
    If @error Then
        MsgBox(16, "SQLite Error", "Can't open database, bye.", 10)
        Exit - 1
    EndIf
    ; Enable the controls
    GUICtrlSetState($ConnectButton, $GUI_DISABLE)
    GUICtrlSetState($GoButton, $GUI_ENABLE)
    GUICtrlSetState($AddButton, $GUI_ENABLE)
    GUICtrlSetState($ReqList, $GUI_ENABLE)
    GUICtrlSetState($ToReqButton, $GUI_ENABLE)
    ; and change the title
    WinSetTitle($Title, "", $Title & " - Connected")
EndFunc

; Compute the query and export it if needed
Func _OuputReq()
    Local $req = GuiCtrlRead($ReqInput)
    If StringStripWS($req, 8) == "" Then Return

    GUICtrlSetState($GoButton, $GUI_DISABLE) ; one query at a time, please
    $iRval = _SQLite_GetTable2d (-1, $req, $aResult, $iRows, $iColumns)
    If $iRval = $SQLITE_OK Then
        _ArrayDisplay($aResult)
    Else
        MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
        Exit -1
    EndIf
    GUICtrlSetState($GoButton, $GUI_ENABLE)

    If MsgBox(36, "Export", "Export to .csv file ?") == 6 Then
        Local $fname = FileSaveDialog("Save as :", @WORKINGDIR, "csv file (*.csv)|Text file (*.txt)")
        If @Error Then
            MsgBox(16, "Error", "Invalid file, bye.", 10)
            Exit - 1
        EndIf
        Local $fout = FileOpen($fname, 2)
        For $i = 0 To UBound($aResult) - 1
            For $j = 0 To UBound($aResult, 2) - 1
                FileWrite($fout, $aResult[$i][$j]&";") ; _2Darray_to_file() needed :s
            Next
            FileWrite($fout, @LF)
        Next
        FileClose($fout)
    EndIf
EndFunc

; This is the end, my friend...
Func _Bye()
    Exit
EndFunc

_SQLite_Close ()
_SQLite_Shutdown ()

[ldub]

It works well, thank you.

I've included a few lines of code into the export loop to remove @ CRLF that may exist in the memos. Thus, the CSV files are correct.

For $i = 0 To UBound($aResult) - 1
    For $j = 0 To UBound($aResult, 2) - 1
    
        ; Delete @CRLF in memo, if exist
        If StringInStr($aResult[$i][$j], @CRLF) > 0 Then 
            $Without_CRLF = StringReplace($aResult[$i][$j], @CRLF, "")
            FileWrite($fout, $Without_CRLF & ";") ; _2Darray_to_file() needed :s
        Else
            FileWrite($fout, $aResult[$i][$j] & ";") ; _2Darray_to_file() needed :s
        EndIf
    Next
    
    FileWrite($fout, @LF)
Next