How to import and export the database sqlite to the file

khang0001 · March 2, 2011

I have the database sqlite

name       a         b          c          d       e
khang      1        2          3          4       5
tuan       6        7          8          9       10
minh       11      12         13         14     15
toan       16      17         18         19     20
tan        21      22         23         24     25

and the code

#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
FileDelete("Database.db")
Dim $hQuery , $aRow
_SQLite_Startup ()
$DatabaseH = _SQLite_Open("Database.db")
_SQLite_Exec (-1, "CREATE TABLE iTABLE (name,a,b,c,d,e);")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('khang','1','2','3','4','5');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('tuan','6','7','8','9','10');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('minh','11','12','13','14','15');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('toan','16','17','18','19','20');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('tan','21','22','23','24','25');")
$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
 FileWrite("1.txt",$iRval)

how can i import the database in the file 1.txt

I try to use the command filewrite but not have any thing. help me. and how can I export the database to the my sqlite same the code _SQLite_Exec

thanks you for read my topic. and why forum not have button thanks. i Want to thanks the reply

AutoBert · March 2, 2011

I have the database sqlite

name       a         b          c          d       e
khang      1        2          3          4       5
tuan       6        7          8          9       10
minh       11      12         13         14     15
toan       16      17         18         19     20
tan        21      22         23         24     25

and the code

#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
FileDelete("Database.db")
Dim $hQuery , $aRow
_SQLite_Startup ()
$DatabaseH = _SQLite_Open("Database.db")
_SQLite_Exec (-1, "CREATE TABLE iTABLE (name,a,b,c,d,e);")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('khang','1','2','3','4','5');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('tuan','6','7','8','9','10');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('minh','11','12','13','14','15');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('toan','16','17','18','19','20');")
_SQLite_Exec (-1, "INSERT INTO iTABLE (name,a,b,c,d,e) VALUES ('tan','21','22','23','24','25');")
$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
 FileWrite("1.txt",$iRval)

how can i import the database in the file 1.txt

I try to use the command filewrite but not have any thing. help me. and how can I export the database to the my sqlite same the code _SQLite_Exec

thanks you for read my topic. and why forum not have button thanks. i Want to thanks the reply

just an simple Exp

#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <array.au3>
FileDelete(@ScriptDir&  "\Database.db")
FileDelete(@ScriptDir&  "\test.txt")
Local $aResult, $iRows, $iColumns
_SQLite_Startup ()
$DatabaseH = _SQLite_Open("Database.db")
_SQLite_Exec (-1, "CREATE TABLE persons (name,a,b,c,d,e);")
_SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('khang','1','2','3','4','5');")
_SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('tuan','6','7','8','9','10');")
_SQLite_Exec (-1, "INSERT INTO persons (name,a,b,c,d,e) VALUES ('minh','11','12','13','14','15');")
_SQLite_Exec (-1, "INSERT INTO persons (name,a,b,c,d,e) VALUES ('toan','16','17','18','19','20');")
_SQLite_Exec (-1, "INSERT INTO persons (name,a,b,c,d,e) VALUES ('tan','21','22','23','24','25');")
$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult)
ConsoleWrite($iRows & " " & $iColumns & @CRLF)
for $iR = 1 to $iRows
    $sText = ""
    for $iC = 0 to $iColumns -1
        $sText &= $aResult[$iR][$iC]
        if $iC < $iColumns -1 Then $sText &= @TAB
    Next
    FileWriteLine(@ScriptDir & "\test.txt", $sText)
Next

ort Example:

jchd · March 2, 2011

SQLite support in AutoIt doesn't offer functions to import/export data in text form. The closest function available is _SQLite_Display2DResult, which you can direct to the console or a variable.

Typically you would want to have .CSV import/export, but the problem is "what .CSV rules do you want"?

The issues with .CSV are plenty, even if one refers to the rare proposals for standardization (e.g. RFC4180 & al.). In practice, there are _way_ too many conflicting "standards" that everyone will claim is _the_ right one to use.

Even a "simple" RFC4180 implementation is far from trivial. Now there are a huge number of variations and additions commonly found in the wild.

The bottom line is that the best way to have it done (SQLite being out of the discussion at this stage) is to write your own import/export routines suited for your actual needs.

As the answer already posted shows, AutoIt doesn't have advanced builtin functions to read or write 2D arrays, so you have to write these functions yourself, using the conventions you want to see enforced. This coding step isn't very difficult by itself, once you decide what are the conventions you need.

As a sidenote, the command-line SQLite.exe utility offers a way to import and export results of a select query, but it is somehow primitive and certainly won't fit everyone's need.

khang0001 · March 2, 2011

just an simple Exp

#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <array.au3>
FileDelete(@ScriptDir&  "\Database.db")
FileDelete(@ScriptDir&  "\test.txt")
Local $aResult, $iRows, $iColumns
_SQLite_Startup ()
$DatabaseH = _SQLite_Open("Database.db")
_SQLite_Exec (-1, "CREATE TABLE persons (name,a,b,c,d,e);")
_SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('khang','1','2','3','4','5');")
_SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('tuan','6','7','8','9','10');")
_SQLite_Exec (-1, "INSERT INTO persons (name,a,b,c,d,e) VALUES ('minh','11','12','13','14','15');")
_SQLite_Exec (-1, "INSERT INTO persons (name,a,b,c,d,e) VALUES ('toan','16','17','18','19','20');")
_SQLite_Exec (-1, "INSERT INTO persons (name,a,b,c,d,e) VALUES ('tan','21','22','23','24','25');")
$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult)
ConsoleWrite($iRows & " " & $iColumns & @CRLF)
for $iR = 1 to $iRows
    $sText = ""
    for $iC = 0 to $iColumns -1
        $sText &= $aResult[$iR][$iC]
        if $iC < $iColumns -1 Then $sText &= @TAB
    Next
    FileWriteLine(@ScriptDir & "\test.txt", $sText)
Next

ort Example:

thanks you very much, and how can i export the 1.txt to my database. if my database not have any data. what the command i can use

PaulBerry · March 2, 2011

thanks you very much, and how can i export the 1.txt to my database. if my database not have any data. what the command i can use

You mean how can you import your text/csv file into an SQLite databse?

Try downloading and installing SQLite Database Browser app, available at: http://sqlitebrowser.sourceforge.net/

Then create a database file with File > New Database (or open an existing database)

Finally use File > Import > Table from CSV file

jchd · March 2, 2011

There are a number of SQLite DB managers, among which SQLite Expert, my hands up favorite. You can download a freeware version here.

saywell · March 2, 2011

There are SQlite tutorials etc on the web - eg this one. These are directed at the command propmt user, but easily modified for use within autoit - see the see the _SQLite_Exec command.

I gave an example of importing text to sqlite using autoit recently -

The relevant bit is below - change the names et to suit your requirements.

Func database()
Dim $aRecords

If Not _FileReadToArray(@ScriptDir & "\printers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading file to Array     error:" & @error)
   Exit
EndIf
;_ArrayDisplay ($aRecords,"$aRecords")

Local $hQuery,$aRow
_SQLite_Startup()
; next bit creates database
_SQLite_Open(@ScriptDir & "\Printers.sqlite")
; and then adds data from the array:
_SQLite_Exec(-1,"Create table printers (IP CHAR NOT NULL,CompNo CHAR, Loc CHAR); " )
For $x = 1 to $aRecords[0]
    Local $sRecord = "'" & $aRecords[$x] & "'"
    $sRecord = StringReplace ($sRecord, " ", "'")
    $sRecord = StringReplace ($sRecord, ",", "','")
    ;Msgbox(0,'Record:' & $x, $sRecord)
    _SQLite_Exec(-1,"INSERT INTO printers (IP, CompNo, Loc) VALUES ("& $sRecord & ") ; " )
Next
_SQLite_Close()
_SQLite_Shutdown()

 EndFunc ;   ==>database

William

Edited March 2, 2011 by saywell

khang0001 · March 2, 2011

export 1.txt to database it mean

when i have the 1.txt with data is

name       a         b          c          d       e
khang      1        2          3          4       5
tuan       6        7          8          9       10
minh       11      12         13         14     15
toan       16      17         18         19     20
tan        21      22         23         24     25

and i want to code to add to my database sqlite auto .

ex : open the 1.txt, read data in 1.txt. make database with name is data.db. create the data into data.db. with the command _SQLite_Exec or any command.

sorry for my english not good.

PaulBerry · March 2, 2011

export 1.txt to database it mean

when i have the 1.txt with data is

name       a         b          c          d       e
khang      1        2          3          4       5
tuan       6        7          8          9       10
minh       11      12         13         14     15
toan       16      17         18         19     20
tan        21      22         23         24     25

and i want to code to add to my database sqlite auto .

ex : open the 1.txt, read data in 1.txt. make database with name is data.db. create the data into data.db. with the command _SQLite_Exec or any command.

sorry for my english not good.

Read the csv file into an array (look up _FileReadToArray in AutoIt Help file)

Convert the array into SQLite inserts as per saywell's script above.

saywell · March 2, 2011

You'll also need to factor in the first line of the csv file, which is the headers.

Simplest would be to add the headers manually, and ignore the first line in the array.

Or you could use that first line from the array to create the headers by code.

If it's only 5 or so, and they will remain constant, then just change this line from the code above:

_SQLite_Exec(-1,"Create table printers (IP CHAR NOT NULL,CompNo CHAR, Loc CHAR); " )

William

Edited March 2, 2011 by saywell

Zedna · March 2, 2011

For export to CSV you can use_SQLite_GetTable2d() and my _ArrayToString2D()

Edited March 2, 2011 by Zedna

khang0001 · March 4, 2011

my test.txt have data

name       a         b          c          d       e
khang      1        2          3          4       5
tuan       6        7          8          9       10
minh       11      12         13         14     15
toan       16      17         18         19     20
tan        21      22         23         24  25

and my code is

#include <sqlite.au3>
#include <sqlite.dll.au3>
#include <file.au3>
#include <Array.au3>
Local $aResult, $iRows, $iColumns
Dim $aArray,$a
_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open () ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf
$e=1
$b = 1
$i=1
If Not _SQLite_Exec (-1, "CREATE TEMP TABLE persons (name1,name2,name3,name4,name5,name6);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
for $b = 1 to 5
_FileReadToArray(@ScriptDir & "\test.txt",$aArray)
$a =  StringSplit($aArray[$b]," ")
     for $e = 1 to 6     
     $i=$i+1     
If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ("&$a[$i]&");") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
     Next
 $i=1
Next

_SQLite_GetTable (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)


    _ArrayDisplay($aResult, "Query Result")
    
    _SQLite_Close ()
_SQLite_Shutdown ()

I want to import test.txt to database sqlite and show in screen . by command _ArrayDisplay but my code not run, help me

Edited March 5, 2011 by khang0001

JoHanatCent · March 4, 2011

I want to export test.txt to database sqlite and show in screen . by command _ArrayDisplay but my code not run, help me

Ooops Double!

Edited March 4, 2011 by JoHanatCent

JoHanatCent · March 4, 2011

In your script above you are creating a tempory database.

Based on AutoBert's simple export you can do:

#include <sqlite.au3>
#include <sqlite.dll.au3>
#include <file.au3>
#include <Array.au3>
Local $aResult, $iRows, $iColumns
Dim $aArray,$a
_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open () ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf
$e=1
$b = 1
$i=1
If Not _SQLite_Exec (-1, "CREATE TEMP TABLE Persons (name,a,b,c,d,e);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
_FileReadToArray(@ScriptDir & "\test.txt",$aArray)
for $b = 2 to $aArray[0]
$a = StringStripWS($aArray[$b],4)
$a =  StringSplit($a," ")
    If Not _SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('"&$a[1]&"','"&$a[2]&"','"&$a[3]&"','"&$a[4]&"','"&$a[5]&"','"&$a[6]&"');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
Next
$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM Persons;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult)
    _SQLite_Close ()
_SQLite_Shutdown ()

If you want a permanent database study AutoBert's script again!

Spellings

Edited March 4, 2011 by JoHanatCent

khang0001 · March 5, 2011

Based on AutoBert's simple export you can do:

#include <sqlite.au3>
#include <sqlite.dll.au3>
#include <file.au3>
#include <Array.au3>
Local $aResult, $iRows, $iColumns
Dim $aArray,$a
_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open () ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf
$e=1
$b = 1
$i=1
If Not _SQLite_Exec (-1, "CREATE TEMP TABLE Persons (name,a,b,c,d,e);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
_FileReadToArray(@ScriptDir & "\test.txt",$aArray)
for $b = 2 to $aArray[0]
$a = StringStripWS($aArray[$b],4)
$a =  StringSplit($a," ")
    If Not _SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('"&$a[1]&"','"&$a[2]&"','"&$a[3]&"','"&$a[4]&"','"&$a[5]&"','"&$a[6]&"');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
Next
$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM Persons;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult)
    _SQLite_Close ()
_SQLite_Shutdown ()

If you want a permanent database study AutoBert's script again!

Spellings

And sorry I want to import not export, sorry I forget it

C:\Documents and Settings\abc\Desktop\1111\test.au3 (27) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: If Not _SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('"&$a[1]&"','"&$a[2]&"','"&$a[3]&"','"&$a[4]&"','"&$a[5]&"','"&$a[6]&"');") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) If Not _SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('"&$a[1]&"','"&^ ERROR

you code is error. I don`t know problem is mean. please help me. Import test.txt to database

Edited March 5, 2011 by khang0001

JoHanatCent · March 5, 2011

And sorry I want to import not export, sorry I forget it

C:\Documents and Settings\abc\Desktop\1111\test.au3 (27) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: If Not _SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('"&$a[1]&"','"&$a[2]&"','"&$a[3]&"','"&$a[4]&"','"&$a[5]&"','"&$a[6]&"');") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) If Not _SQLite_Exec (-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('"&$a[1]&"','"&^ ERROR

you code is error. I don`t know problem is mean. please help me. Import test.txt to database

That example is an "Import"!

Please post your script that produced this error. Can you also attach your test.txt file here?

khang0001 · March 6, 2011

edit. thanks. my code to works

test.txt

Edited March 6, 2011 by khang0001

JoHanatCent · March 6, 2011

edit. thanks. my code to works

This is working with your file:

#include <sqlite.au3>
#include <sqlite.dll.au3>
#include <file.au3>
#include <Array.au3>
Local $aResult, $iRows, $iColumns
Dim $aArray, $a
_SQLite_Startup()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open() ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit -1
EndIf
$b = 1
If Not _SQLite_Exec(-1, "CREATE TEMP TABLE persons (name,a,b,c,d,e);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
_FileReadToArray(@ScriptDir & "\test.txt", $aArray);<<< Moved this here
For $b = 2 To $aArray[0]; <<< Using the array value you can let AI do the counting work
    ; Also start at 2 because the data we need starts at that ellement!
    $a = StringStripWS($aArray[$b], 4);<<<  Leave this in
    $a = StringSplit($a, " ")
        If Not _SQLite_Exec(-1, "INSERT INTO Persons (name,a,b,c,d,e) VALUES ('" & $a[1] & "','" & $a[2] & "','" & $a[3] & "','" & $a[4] & "','" & $a[5] & "','" & $a[6] & "');") = $SQLITE_OK Then _
            MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
Next
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Persons;", $aResult, $iRows, $iColumns); Use this for an Array
_ArrayDisplay($aResult, "Query Result")
_SQLite_Close()
_SQLite_Shutdown()

How to import and export the database sqlite to the file

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members