NewtonSoft Json Serialize Test

[stanl]

The script below works with attached text file [same data that has been tossed around in the Json Dates are Evil Thread]. It does require that you download Newtonsoft.Json.dll (I got it from NuGet).

Code (WINBATCH) Select Expand



;Winbatch 2020 - Using Newtonsoft To Create Json output
;Requires Newtonsoft.Json.dll in script folder
;works with Ace Provider and dowj.txt file,
;but could work with db tables or Excel
;Stan Littlefield 2/4/2021
;/////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
folder = dirscript()


file=folder:"dowj.txt"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
BoxOpen("NewtonSoft Json Serialization","Opening ":file)
cFile = Dirscript():"dowj.json"
cConn='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%folder%':';Extended Properties="TEXT;HDR=YES;Imex=1;ImportMixedTypes=Text"'
ObjectClrOption('Appbase', Dirscript())
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
BoxText("Creating .NET Data Table")
oAdapter = ObjectClrNew( 'System.Data.OleDb.OleDbDataAdapter')
oTable = ObjectClrNew( 'System.Data.DataTable')                 
oAdapter.SelectCommand = oCmd
oAdapter.Fill(oTable)
ObjectClrOption('use', 'Newtonsoft.Json')
oJson = ObjectClrNew('Newtonsoft.Json.JsonConvert')
BoxText("Converting to Json")
oString = ObjectType("BSTR","")


oString = oJson.SerializeObject(oTable);
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
oJson=0
oString = StrReplace(oString,'},{','}':@LF:'{')
FilePut(cFile,oString)
BoxShut()
If FileExist(cFile) Then Pause("Json File Created",cFile)
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


[ChuckC]

Take a look at:

https://www.newtonsoft.com/json/help/html/M_Newtonsoft_Json_JsonConvert_SerializeObject_5.htm

and

https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_JsonSerializerSettings.htm


You can modify the DateFormatHandling and DateFormatString property values to ensure that the serialize & deserialize operations are using the format that you explicitly specify instead of relying on the default values.

[stanl]

Thanks. It was just bare-bones gitter done...

[stanl]

added some date formatting. I try to upload these test scripts so most user can test ( maybe w/out the latest WB release but at least with 2013 release ).

Code (WINBATCH) Select Expand



;Winbatch 2020 - Using Newtonsoft To Create Json output
;Requires Newtonsoft.Json.dll in script folder
;works with Ace Provider and dowj.txt file,
;but could work with db tables or Excel
;Stan Littlefield 2/4/2021
;[edit]: added Json Date Format Setting "MM/dd/yyyy"   
;        added 'title' so data under 'items' array
;        some perfunctory reformatting output
; 2/7/2021
;/////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
folder = dirscript()


file=folder:"dowj.txt"
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
title = '{ "items": ':@LF
BoxOpen("NewtonSoft Json Serialization","Opening ":file)
cFile = Dirscript():"dowj.json"
cConn='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%folder%':';Extended Properties="TEXT;HDR=YES;Imex=1;ImportMixedTypes=Text"'
ObjectClrOption('Appbase', Dirscript())
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
BoxText("Creating .NET Data Table")
oAdapter = ObjectClrNew( 'System.Data.OleDb.OleDbDataAdapter')
oTable = ObjectClrNew( 'System.Data.DataTable')                 
oAdapter.SelectCommand = oCmd
oAdapter.Fill(oTable)
ObjectClrOption('use', 'Newtonsoft.Json')
oJson = ObjectClrNew('Newtonsoft.Json.JsonConvert')
oSettings = ObjectClrNew('Newtonsoft.Json.JsonSerializerSettings')
oSettings.DateFormatString = "MM/dd/yyyy"
BoxText("Converting to Json")
oString = ObjectType("BSTR","")


oString = oJson.SerializeObject(oTable,oSettings);
oAdapter.Dispose()
oTable=0
oCmd=0
oConn=0
oJson=0


oString = StrReplace(oString,'},{','},':@LF:'   {')
oString = StrReplace(oString,'","','",':@LF:'   ':'"')
oString = title:oString:"}"
FilePut(cFile,oString)
BoxShut()
If FileExist(cFile) Then Pause("Json File Created",cFile)
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]

If anyone is interested and do not have or cannot use the Ace OLEDB Provider and alternative is the .NET VisualBasic Text Parser. Uses the dowj.txt file previously attached in another thread but included here.

Code (WINBATCH) Select Expand



;Winbatch 2020A
;Parsing csv with VisualBasic Text Parser
;Using Newtonsoft To Create Json output
;Requires Newtonsoft.Json.dll in script folder
;Text Parser should be included with .NET
;Stan Littlefield 2/8/2021
;/////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
delim=","   ;for tab-delimited use num2char(9)
folder = dirscript()
file=folder:"dowj.txt"
cFile = Dirscript():"dowj.json"
title = '{ "items": ':@LF
If ! FileExist(file) Then Terminate(@TRUE,"Cannot Continue",file:" is missing")
BoxOpen("Processing ":file,"Setting Up...")
ObjectClrOption('Appbase', Dirscript())
ObjectClrOption("useany","System")
ObjectClrOption("useany","System.Data")
ObjectClrOption("useany","Microsoft.VisualBasic")
;load file into Text Parser and create .NET Data Table
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')
cFields = oParser.ReadFields()
nFields = ArrInfo(cFields,1)
ForEach f in cFields
   oTable.Columns.Add(f)
Next
oColumn=0
oCols = oTable.Columns
While ! oParser.EndOfData               
   cFields = oParser.ReadFields()
   BoxText("Parsing Line ":oParser.LineNumber)
   oTable.Rows.Add(cFields)
EndWhile


;lod Json serializer and create output from DataTable
ObjectClrOption('use', 'Newtonsoft.Json')
oJson = ObjectClrNew('Newtonsoft.Json.JsonConvert')
oSettings = ObjectClrNew('Newtonsoft.Json.JsonSerializerSettings')
oSettings.DateFormatString = "MM/dd/yyyy"
BoxText("Converting to Json")
oString = ObjectType("BSTR","")
oString = oJson.SerializeObject(oTable,oSettings);


oTable=0
oParser.Close()
oParser.Dispose()
oJson=0
oGC = ObjectClrNew('System.GC')
oGC.Collect(0)


oString = StrReplace(oString,'},{','},':@LF:'   {')
oString = StrReplace(oString,'","','",':@LF:'   ':'"')
oString = title:oString:"}"
FilePut(cFile,oString)
BoxShut()
If FileExist(cFile) Then Pause("Json File Created",cFile)
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