Apzo 1 Posted November 16, 2010 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 () All the pop3 functions.Rsync your files on your USB key (or anywhere else) Share this post Link to post Share on other sites
ldub 0 Posted November 17, 2010 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 Share this post Link to post Share on other sites
