Sign in to follow this  
Followers 0
khang0001

How to import and export the database sqlite to the file

18 posts in this topic

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

Share this post


Link to post
Share on other sites



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:

Share this post


Link to post
Share on other sites

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.


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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


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)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by saywell

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by saywell

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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 by khang0001

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by JoHanatCent

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 by JoHanatCent

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by khang0001

Share this post


Link to post
Share on other sites

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?


Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

edit. thanks. my code to works

test.txt

Edited by khang0001

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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
Sign in to follow this  
Followers 0