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)
;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
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
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 "|"
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
Quote from: JTaylor on May 04, 2020, 03:52:56 PM
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 (https://www.c-sharpcorner.com/blogs/read-csv-file-and-get-record-in-datatable-using-textfieldparser-in-c-sharp1)