that taked too much time to recover information (the databases are about 26000 records...)
Sub FixedLengthFile2SQLite
Dim i
Dim p
Dim l
Dim f
Dim strFile
Dim lFileLen
Dim str
Dim strSQL
strFile = "C:\FixedTest.txt"
'drop the table if it exists already
strSQL = "DROP TABLE IF EXISTS TEST"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery
'create the SQLite table
strSQL = "CREATE TABLE TEST([FIELD1] TEXT, [FIELD2] TEXT, [FIELD3] TEXT)"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery
'create the insert SQL with the parameters
strSQL = "INSERT INTO TEST(FIELD1, FIELD2, FIELD3) VALUES(@value1, @value2, @value3)"
cmd.AddParameter("value1")
cmd.AddParameter("value2")
cmd.AddParameter("value3")
cmd.CommandText = strSQL
'get the file size in bytes
lFileLen = FileSize (strFile)
'open the text file
FileOpen (f, strFile, cRandom)
'wrap all the inserts in an transaction to make it a lot faster
con.BeginTransaction
'do all the inserts
Do While p * 9 < lFileLen
cmd.SetParameter("value1", FileGet(f, p * 9, 2))
cmd.SetParameter("value2", FileGet(f, p * 9 + 2, 3))
cmd.SetParameter("value3", FileGet(f, p * 9 + 5, 4))
cmd.ExecuteNonQuery
p = p + 1
Loop
'create an index on FIELD1
strSQL = "CREATE INDEX IDX_TEST_FIELD1 ON TEST(FIELD1)"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery
'finalize the transaction, so write the data to disc
con.EndTransaction
'close the text file
FileClose(f)
End Sub
When it's refered about looking for a piece of string not necesary at the beginning of the line, then it takes about 3 seconds: LIKE (%INA%) OR GLOB (*INA*)
I get a strange error: cmd.ExecuteNonQuery Error description: SQLite error no such column: VITAMIN Continue?
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?