Print Page - Text Parser: Not for Prime Time

Title: Text Parser: Not for Prime Time
Post by: stanl on May 07, 2020, 05:18:47 PM

I had high hopes to overcome the Ace Provider not able to support multiple delimiters w/out schema.ini by going the CLR Visual Basic Text parser. Unfortunately a dead end. I Bing'd both success and failure stories, and sided with the failures even after a simple Powershell script failed. The failure is that you cannot access the Microsoft.VisualBasic.FileIO Namespace even though I have 4 copies of the Visual.Basic.core.dll installed via Visual Studio or .Net

Simple failure

Code (Winbatch) Select



ObjectClrOption("useany","System")
ObjectClrOption("useany","Microsoft.VisualBasic") ;loads


ObjectClrOption("useany","Microsoft.VisualBasic.FileIO") ; fails CLR could not load assembly
oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser("%file%")') ; fails


;no use trying any further
;oParser.TextFieldType = 0
;delim= ObjectClrType("System.String","|")
;oParser.Delimiters = delim

I tried Appbase with WB script and Add-Type in PS to directly include the .dll. I haven't tried to load the .dll into the GAC but wouldn't think I would have to... dunno. Suggestions welcome :-\

Title: Re: Text Parser: Not for Prime Time
Post by: JTaylor on May 07, 2020, 07:49:35 PM

Sorry. It tells me I don't have that DLL so guessing I can't be much help. I would just create the ini on the fly and delete it when done :)

Jim

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 08, 2020, 06:57:52 AM

Kind of a Bummer, because I know ObjectClrNew('Microsoft.VisualBasic.FileIO.FileSystem') works as illustrated in Tech Database
https://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsrch.web+~+TechHome#

too bad... the Microsoft.VisualBasic.Core.dll couldn't be loaded into GAC [as if that mattered]. Probably just something for Visual Studio .NET VB developers.

Title: Re: Text Parser: Not for Prime Time
Post by: td on May 08, 2020, 08:14:09 AM

I know you know better but "Microsoft.VisualBasic.FileIO" is a namespace and not an assembly. Namespaces are used as part of a class's name. See below.
You were also passing the class constructor parameter incorrectly. See below.

Code (winbatch) Select


ObjectClrOption("useany","System")
ObjectClrOption("useany","Microsoft.VisualBasic") ;loads

;ObjectClrOption("useany","Microsoft.VisualBasic.FileIO") ; Not an assembly.
oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser', 'c:\logs\Analizer.log') ; This is how you pass a parameter to a contructor.

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 08, 2020, 11:55:57 AM

Tony,

Can't say I haven't tried every possible combination I could think of. And even yours gives [see attached].

Title: Re: Text Parser: Not for Prime Time
Post by: td on May 08, 2020, 12:54:55 PM

All I can tell you is that scriptlet I posted works on my system with the text file constructor parameter "c:\logs\Analizer.log" and the script you posted will never work for the reasons stated.

Title: Re: Text Parser: Not for Prime Time
Post by: td on May 08, 2020, 01:01:52 PM

This also works on my system:

Code (winbatch) Select

ObjectClrOption("useany","System")
ObjectClrOption("useany","Microsoft.VisualBasic") ;loads


oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser', 'c:\logs\Analizer.log')
oParser.TextFieldType = ObjectClrType('Microsoft.VisualBasic.FileIO.FieldType',0) ;fixed width

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 10, 2020, 04:07:56 AM

Thanks. Attached my | delimited file again. Actually TextFieldType of 0 is delimited, 1 is fixed width. Only reading 2 lines of data. There is a ReadFields() method that returns an array... something to work on.

Code (WINBATCH) Select



;Winbatch 2020A - Parsing csv with VisualBasic Text Parser
;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")
ObjectClrOption("useany","System")
ObjectClrOption("useany","Microsoft.VisualBasic")
oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser',file)
oParser.TextFieldType = ObjectClrType('Microsoft.VisualBasic.FileIO.FieldType',0)
;delim= ObjectClrType("System.String","|")  ;not really needed
oParser.SetDelimiters("|")


cRow=""
n=1
While ! oParser.EndOfData
   cRow=cRow:oParser.ReadLine():@CRLF
   n=n+1
   If n>2 Then Break
EndWhile
Message("Data",cRow)
oParser.Close()
oParser.Dispose()
                           
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


Return

Title: Text Parser: Not for Prime Time - Postscript
Post by: stanl on May 10, 2020, 05:40:32 AM

I mistakenly assumed VisualBasic.FileIO was an assembly because I thought it was part of the core.dll and not Visual.Basic
When I first tried Tony's code I got the failure
I updated VS Community 2019 with a Nuget package for Visual Basic
Then it worked.

I really like the TextParser now. Should be able to produce a workable script to place a text file into a .NET Datatable.

Title: Re: Text Parser: Not for Prime Time
Post by: td on May 10, 2020, 08:07:07 AM

You are correct.

For MSFT's documentation for the Microsoft.VisualBasic.FileIO.FieldType enumeration:
Delimited 0 - Indicates that the fields are delimited.
FixedWidth 1 - Indicates that the fields are fixed width.

I keep VS 2019 updated in all of my development environments. Should have mentioned something to that effect.

Title: Re: Text Parser: Not for Prime Time
Post by: td on May 11, 2020, 09:05:32 AM

Took a quick look at MSFT's dotBurgerflipper documentation for 'Microsoft.VisualBasic.FileIO.TextFieldParser' and it appears that the class has been part of the .Net Framework going back to at least version 2.0. However, the class was not a part of .Net Core until version 3.0. That would mean that the class should be available on most systems without any extra downloads unless the system does not have the full Framework installed.

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 11, 2020, 01:37:13 PM

Main goal was to move from delimited text to datatable [and what comes after]. Script, below, works with .csv I uploaded earlier. Real easy, but kind of a bummer as all columns are interpreted as text. Unlike ADO, but understood.... Again, been fun

Code (WINBATCH) Select



;Winbatch 2020A - Parsing csv with VisualBasic Text Parser To DataTable
;Stan Littlefield, May 4, 2020
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
delim="|"
folder = dirscript()
file=folder:"WakeCounty.csv"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
ObjectClrOption("useany","System")
ObjectClrOption("useany","System.Data")
ObjectClrOption("useany","Microsoft.VisualBasic")
oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser',file)
oParser.TextFieldType = ObjectClrType('Microsoft.VisualBasic.FileIO.FieldType',0)
oParser.SetDelimiters(delim)
oTable = ObjectClrNew( 'System.Data.DataTable')


cFields = oParser.ReadFields()
ForEach f in cFields
  oTable.Columns.Add(f)
Next


While ! oParser.EndOfData
   cFields = oParser.ReadFields()
   oTable.Rows.Add(cFields)
EndWhile


rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count
Message("Columns: ":colcount,"Rows: ":rowcount)
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:");"
oTable=0
oParser.Close()
oParser.Dispose()


Message("Create Statement: Based on Column 1",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

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 15, 2020, 03:17:57 AM

Now trying to assign data types to text parser. Script still uses the WakeCounty.csv file [which I again attached]. I hard-coded 3 of the text headers when converting to System.DataTable DataColumns - which I wanted to be Int32. Initially got errors because the 'accts' text column has null values. Added what I thought was a line of code: oColumn.AllowDBNull = bTrue to accept nulls but still get the failure. Most of what I read about AllowDBNull was code that had it assigned before the column was added to the datatable, but I haven't been able to figure out. Time for some Tony magic

Code (WINBATCH) Select



;Winbatch 2020A - Parsing csv with VisualBasic Text Parser To DataTable
;Stan Littlefield, May 4, 2020
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
delim="|"
folder = dirscript()
file=folder:"WakeCounty.csv"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
ObjectClrOption("useany","System")
ObjectClrOption("useany","System.Data")
ObjectClrOption("useany","Microsoft.VisualBasic")
oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser',file)
oParser.TextFieldType = ObjectClrType('Microsoft.VisualBasic.FileIO.FieldType',0)
oParser.SetDelimiters(delim)
oTable = ObjectClrNew('System.Data.DataTable')
bTrue = ObjectType( "BOOL", -1 )


oType = ObjectClrNew('System.Type')


cFields = oParser.ReadFields()
ForEach f in cFields
  If Strindex("Zip|ACode",f,1,@FWDSCAN)
  ;If Strindex("Zip|ACode|accts",f,1,@FWDSCAN) ;this will fail
     cType = oType.GetType("System.Int32")
     oColumn = oTable.Columns.Add(f,cType)
     oColumn.AllowDBNull = bTrue
  Else
     oTable.Columns.Add(f)
  Endif
Next


While ! oParser.EndOfData
   cFields = oParser.ReadFields()
   oTable.Rows.Add(cFields)
EndWhile


rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count
Message("Columns: ":colcount,"Rows: ":rowcount)
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:");"
oTable=0
oParser.Close()
oParser.Dispose()
oGC = ObjectClrNew('System.GC')
oGC.Collect(0)
Message("Create Statement: Based on Column 1",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

Title: Re: Text Parser: Not for Prime Time
Post by: td on May 15, 2020, 12:00:36 PM

Have no idea if this is of any help but based on usage examples here is a stab at it.

Code (winbatch) Select

ForEach f in cFields

  If Strindex("Zip|ACode",f,1,@FWDSCAN)
  ;If Strindex("Zip|ACode|accts",f,1,@FWDSCAN) ;this will fail
     cType = oType.GetType("System.Int32")
     ; Create the column first and then add it to the table in a separate step.
     tmpCol = ObjectClrNew("System.Data.DataColumn", f, cType)
     tmpCol.AllowDBNull = bTrue
     oTable.Columns.Add(tmpCol)
  Else
     oTable.Columns.Add(f)
  Endif
Next
tmpCol = 0

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 15, 2020, 12:59:22 PM

Nope. Still gives the error when including accts column which has null values. [attached]

Title: Re: Text Parser: Not for Prime Time
Post by: td on May 15, 2020, 01:44:45 PM

I guess you are on your own. I am not sure how you are getting that error but the documentation for AllowDBNull states that it defaults to True except for key columns so you shouldn't have to set it. My understanding of the property is that setting it means that ADO will place the default value for the datatype of the column in the column when necessary. It also prevents you from deliberately setting a column to a "System.Null" value when it is set to False. But my understanding is limited so I could be missing the mark completely.

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 16, 2020, 05:11:39 AM

Quote from: td on May 15, 2020, 01:44:45 PM
I guess you are on your own.

Yeah... self-quarantine: This kludge works and I think I can make it a little more generic...

First, early in the script

Code (WINBATCH) Select



cFields = oParser.ReadFields()
nFields = ArrInfo(cFields,1)

then

Code (WINBATCH) Select



While ! oParser.EndOfData                
   cFields = oParser.ReadFields()
   ForEach f in cFields
      For i=0 To nFields-1
      If cFields[i] == "" & Strindex("Zip|ACode|accts",f,1,@FWDSCAN)>0  Then  cFields[i] = 0
      ;If cFields[i] == "" & oTable.Columns[i].DataType == cType Then cFields[i] = 0
      Next
   Next
   oTable.Rows.Add(cFields)
EndWhile

EDIT: There is an Ordinal property in datacolumn so probably try a foreach on the columns collection and if ordinal == i and DataType == cType I can set the null to 0

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 16, 2020, 01:19:04 PM

Best I could do for now.

Code (WINBATCH) Select



;Winbatch 2020A - Parsing csv with VisualBasic Text Parser To DataTable
;Stan Littlefield, May 4, 2020
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
delim="|"
folder = dirscript()
file=folder:"WakeCounty.csv"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
ObjectClrOption("useany","System")
ObjectClrOption("useany","System.Data")
ObjectClrOption("useany","Microsoft.VisualBasic")
oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser',file)
oParser.TextFieldType = ObjectClrType('Microsoft.VisualBasic.FileIO.FieldType',0)
oParser.SetDelimiters(delim)
oTable = ObjectClrNew('System.Data.DataTable')
bTrue = ObjectType( "BOOL", -1 )
bFalse = ObjectType( "BOOL", 0 )
oType = ObjectClrNew('System.Type')
intCols = "Zip|ACode|accts"
cFields = oParser.ReadFields()
nFields = ArrInfo(cFields,1)
ForEach f in cFields
   If Strindex(intCols,f,1,@FWDSCAN) ;this will fail
     cType = oType.GetType("System.Int32")
     oColumn = ObjectClrNew("System.Data.DataColumn", f, cType)     
      oTable.Columns.Add(oColumn)
  Else
     oTable.Columns.Add(f)
  Endif
Next
oColumn=0
oCols = oTable.Columns

colMatch = cType.ToString()
While ! oParser.EndOfData                
   cFields = oParser.ReadFields()
   For i=0 To nFields-1
      If cFields[i] == "" 
         ForEach col in oCols 
            colType = col.DataType.ToString()          
            If col.Ordinal == i & colType == colMatch
               cFields[i] = 0
               Break
            Endif
         Next
      Endif
   Next
   oTable.Rows.Add(cFields)
EndWhile
rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count
Message("Columns: ":colcount,"Rows: ":rowcount)
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:");"
oTable=0
oParser.Close()
oParser.Dispose()
oGC = ObjectClrNew('System.GC')
oGC.Collect(0)
Message("Create Statement: Based on Column 1",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

Title: Re: Text Parser: Not for Prime Time
Post by: stanl on May 17, 2020, 04:40:59 AM

This completes the basics. Attached the file used in the script as tab-delimited. Added a UDF to return default values for nulls in any column type [ covers text files would mostly contain char, numbers, dates ]. Going to gather a range of files with different delimiters and create a pre-parsing dialog to choose file, indicate delimiter, assign column types - default to System.String... in the mold of the schema.ini Jim suggested for parsing with ACE Provider. Oh, and Jim, if you are reading this I'll get to your SQLite Extender work.

Code (WINBATCH) Select



;Winbatch 2020A - Parsing csv with VisualBasic Text Parser To DataTable
;Stan Littlefield, Updated: May 17, 2020
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
;delim="|"
delim=num2char(9)
folder = dirscript()
file=folder:"WakeCounty.csv"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
BoxOpen("Processing ":file,"Setting Up...")
ObjectClrOption("useany","System")
ObjectClrOption("useany","System.Data")
ObjectClrOption("useany","Microsoft.VisualBasic")
oParser = ObjectClrNew('Microsoft.VisualBasic.FileIO.TextFieldParser',file)
oParser.TextFieldType = ObjectClrType('Microsoft.VisualBasic.FileIO.FieldType',0)
oParser.SetDelimiters(delim)
oTable = ObjectClrNew('System.Data.DataTable')
bTrue = ObjectType( "BOOL", -1 )
bFalse = ObjectType( "BOOL", 0 )
oType = ObjectClrNew('System.Type')
intCols = "Zip|ACode|accts"
cFields = oParser.ReadFields()
nFields = ArrInfo(cFields,1)
ForEach f in cFields
  If Strindex(intCols,f,1,@FWDSCAN) 
     cType = oType.GetType("System.Int32")
     oColumn = ObjectClrNew("System.Data.DataColumn", f, cType)     
     oTable.Columns.Add(oColumn)
  Else
     oTable.Columns.Add(f)
  Endif
Next
oColumn=0
oCols = oTable.Columns
While ! oParser.EndOfData                
   cFields = oParser.ReadFields()
   BoxText("Parsing Line ":oParser.LineNumber)
   For i=0 To nFields-1
      If cFields[i] == "" 
         ForEach col in oCols 
            colType = col.DataType.ToString()          
            If col.Ordinal == i
               cFields[i] = ifNull(colType)
               If cFields[i]=="err" Then Continue ;row will not be read
               Break
            Endif
         Next   
      Endif
   Next
   oTable.Rows.Add(cFields)
EndWhile


rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count
BoxText("Converted To DataTable":@LF:"Columns: ":colcount:@LF:"Rows: ":rowcount)
TimeDelay(2)
BoxShut()
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:");"
oTable=0
oParser.Close()
oParser.Dispose()
oGC = ObjectClrNew('System.GC')
oGC.Collect(0)
Message("Create Statement: Based on Column 1",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


#DefineSubRoutine ifNull(colType)
retval = "err"
If colType == "System.Boolean" Then  retval=0
If colType == "System.Byte" Then  retval=0
If colType == "System.Char" Then  retval=cFields[1]
If colType == "System.DateTime" Then retval="1/1/1900"
If colType == "System.Decimal" Then retval=0
If colType == "System.Int32" Then retval=0
If colType == "System.Int64" Then retval=0
If colType == "System.Int16" Then retval=0
If colType == "System.Single" Then retval=0
If colType == "System.String" Then retval=cFields[1]
If colType == "System.Double" Then retval=0
If colType == "System.UInt16" Then retval=0
If colType == "System.UInt32" Then retval=0
If colType == "System.UInt64" Then retval=0
If colType == "System.IntPtr" Then retval=cFields[1]
Return(retval)
#EndSubRoutine




Return

WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: stanl on May 07, 2020, 05:18:47 PM