Jim;
Script creates xml output from .net DataTable (created from .csv) - could easily be modified for different file or db table and there are schema options for the writexml() method. Should be easily consumed by your Extender.
;Winbatch 2020A
;Parsing csv with VisualBasic Text Parser
;output as xml
;Text Parser should be included with .NET
;Stan Littlefield 2/13/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.xml"
tblName = "DOW"
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',tblName)
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
BoxText("Creating XML Output")
cWriter = ObjectClrNew('System.IO.StringWriter')
oTable.WriteXml(cWriter,bTrue)
FilePut(cFile,cWriter.ToString())
oTable=0
oParser.Close()
oParser.Dispose()
cReader.Dispose
oGC = ObjectClrNew('System.GC')
oGC.Collect(0)
BoxShut()
If FileExist(cFile) Then Pause("XML 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
Cool. Thanks.
...and with everything combined now one could even go directly to SQLite with one command and process that route as well. Good to have options. Appreciate all your work contributions.
Jim
Output:
<DocumentElement>
<DOW>
<Date>09/19/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Mon</DOW>
<Endate>09/25/2011</Endate>
<Range>Sep19-Sep25</Range>
</DOW>
<DOW>
<Date>09/20/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Tue</DOW>
<Endate>09/26/2011</Endate>
<Range>Sep20-Sep26</Range>
</DOW>
<DOW>
<Date>09/21/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Wed</DOW>
<Endate>09/27/2011</Endate>
<Range>Sep21-Sep27</Range>
</DOW>
<DOW>
<Date>09/22/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Thu</DOW>
<Endate>09/28/2011</Endate>
<Range>Sep22-Sep28</Range>
</DOW>
<DOW>
<Date>09/23/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Fri</DOW>
<Endate>09/29/2011</Endate>
<Range>Sep23-Sep29</Range>
</DOW>
<DOW>
<Date>09/24/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sat</DOW>
<Endate>09/30/2011</Endate>
<Range>Sep24-Sep30</Range>
</DOW>
<DOW>
<Date>09/25/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sun</DOW>
<Endate>10/01/2011</Endate>
<Range>Sep25-Oct01</Range>
</DOW>
<DOW>
<Date>09/26/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Mon</DOW>
<Endate>10/02/2011</Endate>
<Range>Sep26-Oct02</Range>
</DOW>
<DOW>
<Date>09/27/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Tue</DOW>
<Endate>10/03/2011</Endate>
<Range>Sep27-Oct03</Range>
</DOW>
<DOW>
<Date>09/28/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Wed</DOW>
<Endate>10/04/2011</Endate>
<Range>Sep28-Oct04</Range>
</DOW>
<DOW>
<Date>09/29/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Thu</DOW>
<Endate>10/05/2011</Endate>
<Range>Sep29-Oct05</Range>
</DOW>
<DOW>
<Date>09/30/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Fri</DOW>
<Endate>10/06/2011</Endate>
<Range>Sep30-Oct06</Range>
</DOW>
<DOW>
<Date>10/01/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sat</DOW>
<Endate>10/07/2011</Endate>
<Range>Oct01-Oct07</Range>
</DOW>
<DOW>
<Date>10/02/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sun</DOW>
<Endate>10/08/2011</Endate>
<Range>Oct02-Oct08</Range>
</DOW>
<DOW>
<Date>10/03/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Mon</DOW>
<Endate>10/09/2011</Endate>
<Range>Oct03-Oct09</Range>
</DOW>
<DOW>
<Date>10/04/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Tue</DOW>
<Endate>10/10/2011</Endate>
<Range>Oct04-Oct10</Range>
</DOW>
<DOW>
<Date>10/05/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Wed</DOW>
<Endate>10/11/2011</Endate>
<Range>Oct05-Oct11</Range>
</DOW>
<DOW>
<Date>10/06/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Thu</DOW>
<Endate>10/12/2011</Endate>
<Range>Oct06-Oct12</Range>
</DOW>
<DOW>
<Date>10/07/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Fri</DOW>
<Endate>10/13/2011</Endate>
<Range>Oct07-Oct13</Range>
</DOW>
<DOW>
<Date>10/08/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sat</DOW>
<Endate>10/14/2011</Endate>
<Range>Oct08-Oct14</Range>
</DOW>
</DocumentElement>
I will have to make sure I save this somewhere I can find it again. Thanks.
Jim
I'm getting more comfortable with .Net DataSets/DataTables, and even the VBParser has worth.
If I modify script with
cWriter = ObjectClrNew('System.IO.StringWriter')
oWriter = ObjectClrNew('System.Data.XmlWriteMode',0)
oTable.WriteXml(cWriter,oWriter)
FilePut(cFile,cWriter.ToString())
I get
<NewDataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="DOW" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="DOW">
<xs:complexType>
<xs:sequence>
<xs:element name="Date" type="xs:string" minOccurs="0" />
<xs:element name="cMonth" type="xs:string" minOccurs="0" />
<xs:element name="Qtr" type="xs:string" minOccurs="0" />
<xs:element name="DOW" type="xs:string" minOccurs="0" />
<xs:element name="Endate" type="xs:string" minOccurs="0" />
<xs:element name="Range" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<DOW>
<Date>09/19/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Mon</DOW>
<Endate>09/25/2011</Endate>
<Range>Sep19-Sep25</Range>
</DOW>
<DOW>
<Date>09/20/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Tue</DOW>
<Endate>09/26/2011</Endate>
<Range>Sep20-Sep26</Range>
</DOW>
<DOW>
<Date>09/21/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Wed</DOW>
<Endate>09/27/2011</Endate>
<Range>Sep21-Sep27</Range>
</DOW>
<DOW>
<Date>09/22/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Thu</DOW>
<Endate>09/28/2011</Endate>
<Range>Sep22-Sep28</Range>
</DOW>
<DOW>
<Date>09/23/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Fri</DOW>
<Endate>09/29/2011</Endate>
<Range>Sep23-Sep29</Range>
</DOW>
<DOW>
<Date>09/24/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sat</DOW>
<Endate>09/30/2011</Endate>
<Range>Sep24-Sep30</Range>
</DOW>
<DOW>
<Date>09/25/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sun</DOW>
<Endate>10/01/2011</Endate>
<Range>Sep25-Oct01</Range>
</DOW>
<DOW>
<Date>09/26/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Mon</DOW>
<Endate>10/02/2011</Endate>
<Range>Sep26-Oct02</Range>
</DOW>
<DOW>
<Date>09/27/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Tue</DOW>
<Endate>10/03/2011</Endate>
<Range>Sep27-Oct03</Range>
</DOW>
<DOW>
<Date>09/28/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Wed</DOW>
<Endate>10/04/2011</Endate>
<Range>Sep28-Oct04</Range>
</DOW>
<DOW>
<Date>09/29/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Thu</DOW>
<Endate>10/05/2011</Endate>
<Range>Sep29-Oct05</Range>
</DOW>
<DOW>
<Date>09/30/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Fri</DOW>
<Endate>10/06/2011</Endate>
<Range>Sep30-Oct06</Range>
</DOW>
<DOW>
<Date>10/01/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sat</DOW>
<Endate>10/07/2011</Endate>
<Range>Oct01-Oct07</Range>
</DOW>
<DOW>
<Date>10/02/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sun</DOW>
<Endate>10/08/2011</Endate>
<Range>Oct02-Oct08</Range>
</DOW>
<DOW>
<Date>10/03/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Mon</DOW>
<Endate>10/09/2011</Endate>
<Range>Oct03-Oct09</Range>
</DOW>
<DOW>
<Date>10/04/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Tue</DOW>
<Endate>10/10/2011</Endate>
<Range>Oct04-Oct10</Range>
</DOW>
<DOW>
<Date>10/05/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Wed</DOW>
<Endate>10/11/2011</Endate>
<Range>Oct05-Oct11</Range>
</DOW>
<DOW>
<Date>10/06/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Thu</DOW>
<Endate>10/12/2011</Endate>
<Range>Oct06-Oct12</Range>
</DOW>
<DOW>
<Date>10/07/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Fri</DOW>
<Endate>10/13/2011</Endate>
<Range>Oct07-Oct13</Range>
</DOW>
<DOW>
<Date>10/08/2011</Date>
<cMonth>2011/10</cMonth>
<Qtr>4Q2011</Qtr>
<DOW>Sat</DOW>
<Endate>10/14/2011</Endate>
<Range>Oct08-Oct14</Range>
</DOW>
</NewDataSet>