Some xml for DOM Extender

Started by stanl, February 13, 2021, 06:15:11 AM

Previous topic - Next topic

stanl

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.
Code (WINBATCH) Select


;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



JTaylor

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

stanl

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>

JTaylor

I will have to make sure I save this somewhere I can find it again.  Thanks.

Jim

stanl

I'm getting more comfortable with .Net DataSets/DataTables, and even the VBParser has worth.

stanl

If I modify script with
Code (WINBATCH) Select


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>