I’m trying to import any tab delimited text file of various columns and headings into an SQLite database
eg:
First Name Last Name Email User Name *Employee ID
Paul Blogs paul.Blogs@email.compaul.blogs@email.com 123
Rodney Smith rodney.smith @email.au rodney.smith @email.au 456
The database is created with the correct columns and the databaseRecord looks ok in the debug window but I get the error message -
(this one appears once)
Database error: near “*”: syntax error
(then this one repeats for each row)
Database error: near “)”: syntax error
I realise the files have spaces in the field names and there is star characters but it doesn’t seem to make any difference to creating the table. It only fails on entering the rows.
[code] dim txtin as TextInputStream
dim s as String
dim lines() as string
txtin = TextInputStream.Open(dataFile) //
while not txtin.EOF
lines.append( txtin.ReadLine )
wend
txtin.close
dim i,count As integer
'Create table
if db.Connect then
db.SQLExecute ("CREATE TABLE main (ID INTEGER NOT NULL, PRIMARY KEY(ID));")
'Create Headings
dim columnCount as integer = lines(0).CountFields(chrb(9))
for i = 0 to columnCount - 1
dim strHeading as string = lines(0).NthField(chrb(9),i+1)
dim pstrSQL as string = "ALTER TABLE main ADD COLUMN '" + strHeading + "' Varchar"
db.SQLExecute (pstrSQL)
next
db.Commit
'Enter data
dim intRows as integer =lines.Ubound
Dim rs As RecordSet = db.FieldSchema("main")
for i=1 to intRows
dim c as integer
dim intFieldCount as integer = rs.FieldCount - 1
// Make database record
dim row as new DatabaseRecord
for c = 1 to intFieldCount
dim strFieldText as string = lines(i).NthField(chrb(9),c)
rs.MoveNext // start with second column (not id)
dim colName As String = rs.IdxField(1).StringValue
row.Column(colName) = strFieldText
next
db.InsertRecord("main",row)
if DB.Error Then
MsgBox("Database Error: " + db.ErrorMessage)
end if
next
db.commit
else
msgbox db.ErrorMessage
end if[/code]
db.InsertRecord("main",row)
if DB.Error Then
MsgBox("Database Error: " + db.ErrorMessage)
end if
I don’t have much control over characters and spaces in the field names as the users create anything they like in Excel which is acceptable then export to tab delimited text.
The star error is on the first record only, then the bracket error for every record after that.
Removing the star from the text file still produces the bracket errors.
This is NOT tested, and for ILLUSTRATION ONLY… but it is a much better method… there may be some syntax errors, as I took your original code and altered it … up to you to decide if you want to go this way, and to make the required changes to make it fit your process.
Dim txtin As TextInputStream
Dim s As String
Dim lines() As String
dim ColumnCount as integer
txtin = TextInputStream.Open(dataFile) //
s=txtin.readall
s=ReplaceLineEndings(s,EndOfLine.UNIX)
lines.Split(s,EndOfLine.UNIX)
txtin.close
Dim i,count As Integer
Dim SQL As String
'Create table
If db.Connect Then
SQL="CREATE TABLE main (ID INTEGER NOT NULL, PRIMARY KEY(ID)")
'Create Headings
columnCount = lines(0).CountFields(ChrB(9))
For i = 0 To columnCount - 1
Dim strHeading As String = lines(0).NthField(ChrB(9),i+1)
strHeading=ReplaceAll(strHeading," ","_")
strHeading=ReplaceAll(strHeading,"*","")
SQL=SQL+ "," + strHeading + " Varchar"
Next
SQL=SQL+")"
db.SQLExecute (SQL)
db.Commit
//
//Enter data
//
Dim intRows As Integer =lines.Ubound
For i=1 To intRows
Dim c As Integer
// Make database record
SQL="INSERT INTO main VALUES("
For c = 1 To columnCount
Dim strFieldText As String = lines(i).NthField(ChrB(9),c)
sql=sql+"'"+strFieldText+"'"
if c<columnCount then sql=sql+","
Next
sql=sql+")"
db.SQLExecute (SQL)
Next
db.commit
End If
[quote=298804:@Craig Grech]Thanks I got it to work, with a few alterations.
One question about using
"INSERT INTO main VALUES( ...."
If the first field is my primary key how do skip it or make it auto-fill without specifying the name of all the other fields and their assigned value?[/quote]
you’re correct… I just typed that off the top of my head
it would be more along the lines of “INSERT INTO main (”+") VALUES( …
it could be built at the same time you were building the CREATE TABLE statement
I ditched the primary key for now.
It works ok but extremely slow on large files (8000 lines)
[code] 'Enter data
dim intRows as integer =lines.Ubound
for i=1 to intRows
dim c as integer
// Make database record
SQL="INSERT INTO main VALUES("
For c = 1 To columnCount
Dim strFieldText As String = lines(i).NthField(ChrB(9),c)
sql=sql+"'"+strFieldText+"'"
if c<columnCount then sql=sql+","
Next
sql=sql+")"
db.SQLExecute (SQL)
if DB.Error Then
MsgBox("Database Error: " + db.ErrorMessage)
end if
Next
db.commit
msgbox "Data imported"[/code]
Using a 10,000 row file of random data records matching your example,
inserted 5 times for 50,000 total rows (with primary key) takes less than 1 second.
you can also group the “insert” commands into one sentence (with endofline between and “;”)
then you send a string with 1000 “insert” inside to the database.
this really speeds things up.
Or: INSERT INTO ‘tablename’
SELECT ‘data1’ AS ‘column1’, ‘data2’ AS ‘column2’
UNION ALL SELECT ‘data1’, ‘data2’
UNION ALL SELECT ‘data1’, ‘data2’
UNION ALL SELECT ‘data1’, ‘data2’
as recommeded here . This trick appears to be working with 500 records maximum though. UNION ALL is used here for maximum speed. You may want to use UNION if you need to skip duplicates.
