### Import from CSV file

'Import a simple csv file into a newly created database test.db and table test
'The header of the csv file is used to create the table columns from type text
'CSV file:
'ColumnA,ColumnB
'1,2
'A,B
'Requires the libraries jSQL, jStringUtils and external jar sqlite-jdbc
'Define Sub Process_Globals Private gSQL As SQL
`Sub SQLiteImportCSV
Dim su As StringUtils
Dim header As List : header.Initialize
Dim l As List = su.LoadCSV2(File.DirApp, "test.csv", ",", header)
'
File.Delete(File.DirApp, "test.db")
gSQL.InitializeSQLite(File.dirapp, "test.db", True)
Dim sqlcmd As String
Dim sb As StringBuilder
sb.Initialize
'Build the column statement from the header list
For i = 0 To header.Size -1
sb.Append(header.Get(i) & " TEXT")
If i < header.Size - 1 Then sb.Append(",")
Next
'CREATE the table if not exists
Log("[CREATE]")
Try
sqlcmd = "CREATE TABLE test (" & sb.ToString & ")"
Log(sqlcmd)
gSQL.ExecNonQuery(sqlcmd)
Catch
Log(LastException.Message)
Return
End Try
'INSERT
Log("[INSERT]")
For i = 0 To l.Size - 1
Dim sb As StringBuilder
sb.Initialize
Dim s() As String = l.Get(i)
For j = 0 To s.Length - 1
sb.Append("'"&header.Get(j)&" " & s(j) & "'")
If j < s.Length - 1 Then sb.Append(",")
Next
sqlcmd = "INSERT INTO test VALUES (" & sb.ToString & ")"
Log(sqlcmd)
Try
gSQL.ExecNonQuery (sqlcmd)
Catch
Log(LastException.Message)
Return
End Try
Next
'SELECT
Log("[SELECT]")
Dim cursor As ResultSet
Dim sb As StringBuilder
sb.Initialize
Try
sqlcmd = "SELECT * FROM test"
Log(sqlcmd)
cursor = gSQL.ExecQuery (sqlcmd)
Do While cursor.NextRow
For i = 0 To cursor.ColumnCount - 1
sb.Append(cursor.GetColumnName(i) & " = " & cursor.GetString2(i) & CRLF)
Next
Loop
Log(sb.ToString)
Catch
Log("ERROR: " & LastException.Message)
End Try
Log("Done...")
End Sub`
Output Example
Program started.
[CREATE]
CREATE TABLE test (ColumnA TEXT,ColumnB TEXT)
[INSERT]
INSERT INTO test VALUES ('ColumnA 1','ColumnB 2')
INSERT INTO test VALUES ('ColumnA A','ColumnB B')
[SELECT]
SELECT * FROM test
ColumnA = ColumnA 1
ColumnB = ColumnB 2
ColumnA = ColumnA A
ColumnB = ColumnB B
Done...

Changed: , Category: SQLite, File: T6C254M1828.htm, ID: 1828