Text Parser: Not for Prime Time

[stanl]

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 Expand



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 :-\

[JTaylor]

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

[stanl]

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.

[td]

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 Expand


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.


[stanl]

Tony,


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

[td]

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.

[td]

This also works on my system:

Code (winbatch) Select Expand

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


[stanl]

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 Expand



;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


[stanl]

   
• 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.

[td]

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.

[td]

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.

[stanl]

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 Expand



;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


[stanl]

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 Expand



;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


[td]

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

Code (winbatch) Select Expand

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


[stanl]

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

[td]

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.

[stanl]

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 Expand



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



then

Code (WINBATCH) Select Expand



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

[stanl]

Best I could do for now. 

Code (WINBATCH) Select Expand



;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


[stanl]

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 Expand



;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