SQLite Pt. 3

[stanl]

attached .csv for code. I wasted time trying to make FMT=TabDelimited (and other Googled variations) to work and settled on CSV. The SQLite create statement seems sound [ I added the first row values and .net type for information ]. SQLite doesn't seem to support INSERT INTO .... (SELECT FROM... )


but you can INSERT multiple VALUE clauses. If anyone reading this or remotely interested can come up with a workable tab-delimited connection string I will be eternally grateful!


P.S. FMT=TabDelimited will not error, but only returns 1 column (code has annoying Message() statements)

Code (WINBATCH) Select Expand



;Winbatch 2020A - Parsing csv for SQLite Structure
;Stan Littlefield, May 4, 2020
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
folder = dirscript()
file=folder:"WakeCounty.csv"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
cConn='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%folder%':';Extended Properties="TEXT;HDR=YES;FMT=Delimited"'
Message("Connection",cConn)


ObjectClrOption("useany","System.Data")
oConn = ObjectClrNew( 'System.Data.OleDb.OleDbConnection',cConn)
oConn.Open()
oCmd = ObjectClrNew( 'System.Data.OleDb.OleDbCommand')
cSQL = "SELECT * FROM ":file


oCmd.Connection = oConn
oCmd.CommandText = cSQL


oAdapter = ObjectClrNew( 'System.Data.OleDb.OleDbDataAdapter')
oTable = ObjectClrNew( 'System.Data.DataTable')                 
oAdapter.SelectCommand = oCmd
rowcount = oAdapter.Fill(oTable)
colcount = oTable.Columns.Count
Message("Columns",colcount)
oCols = oTable.Columns
cCreate = "Create Table Test (":@LF
ForEach col in oCols
   name = col.ColumnName
   colType = col.DataType.ToString()
   objRow = oTable.Rows.Item(0)
   val = objRow.Item(col)
   cCreate = cCreate:name:" ":cvt(colType):",":"[VALUE: %val% .net type %colType%]":@LF
Next


cCreate = cCreate:");"
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
Message("Create Statement",cCreate)
Exit


:WBERRORHANDLER
geterror()
Message("Error Encountered",errmsg)
Exit


;/////////////////////////////////////////////////////////////////////////////////////


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine cvt(colType)
retval = "BLOB"
If colType == "System.Boolean" Then  retval="INTEGER"
If colType == "System.Byte" Then  retval="INTEGER"
If colType == "System.Char" Then  retval="TEXT"
If colType == "System.DateTime" Then retval="TEXT"
If colType == "System.Decimal" Then retval="NUMERIC"
If colType == "System.Int32" Then retval="INTEGER"
If colType == "System.Int64" Then retval="REAL"
If colType == "System.Int16" Then retval="INTEGER"
If colType == "System.Object" Then retval="BLOB"
If colType == "System.Single" Then retval="REAL"
If colType == "System.String" Then retval="TEXT"
If colType == "System.Double" Then retval="REAL"
If colType == "System.Array" Then retval="BLOB"
If colType == "System.UInt16" Then retval="INTEGER"
If colType == "System.UInt32" Then retval="REAL"
If colType == "System.UInt64" Then retval="REAL"
If colType == "System.IntPtr" Then retval="TEXT"


Return(retval)
#EndSubRoutine


Return




[JTaylor]

Create a "schema.ini" file in the same directory and give the header the file name and use "TabDelimited" as the FMT.

[wakecounty.txt]
ColNameHeader=False
Format=TabDelimited

You only need to be grateful until next Tuesday though  ;)

Jim

[stanl]

The schema.ini route is a little lame, especially if you are extracting the tab-delimited data on the fly. The issue with Delimited is that commas within columns are encased with ". Probably just replace TAB with "|"

[JTaylor]

Agreed.  But was the only way I saw.   Should be able to create the ini file on the fly though.   Could use the CSV Winbatch functions to convert it to CSV from TAB.

Jim

[stanl]

Agreed.  But was the only way I saw.   Should be able to create the ini file on the fly though.   Could use the CSV Winbatch functions to convert it to CSV from TAB.

Jim

I believe the old Jet 4.0 provider could handle tab or | delimited w/out schema.ini.  Think I might try the VB .NET textparser class


https://www.c-sharpcorner.com/blogs/read-csv-file-and-get-record-in-datatable-using-textfieldparser-in-c-sharp1