CSV: Difference between revisions

From Commander4j
← Older edit
No edit summary
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
[[Category: Middleware4j]]
=CSV Map Configuration=
=CSV Map Configuration=
'''config.xml'''
'''config.xml'''
<syntaxhighlight lang="xml">
<syntaxhighlight lang="xml">
  <map id="Map04" enabled="Y" description="GEN Comma Separated Variables">
      <map>
        <input id="in1" description="Read GEN Comma Separated Variables">
        <id>Map04</id>
            <type>CSV</type>
        <enabled>true</enabled>
            <path>./interface/input/403 (GEN Comma Separated Variables)</path>
        <description>CSV to XML</description>
            <mask/>
        <enableEmailNotifications>true</enableEmailNotifications>
            <pollingInterval>1000</pollingInterval>
        <connectors>
            <XSLT>CSV_to_XML_Example.xsl</XSLT>
            <input>
            <optionDelimeter>^</optionDelimeter>
              <id>in1</id>
            <csvOptions>separator=,^quote="</csvOptions>
              <description>Read CSV</description>
        </input>
              <type>CSV</type>
        <output id="out1" enabled="Y" description="Write XML Example">
              <url>
            <path>./interface/output/403 (GEN XML)</path>
                  <path>./interface/input/Map04 CSV to XML</path>
            <type>XML</type>
                  <mask/>
            <XSLT/>
                  <prefix/>
        </output>
                  <pollingInterval>1000</pollingInterval>
    </map>
              </url>
              <csv>
                  <optionDelimeter>^</optionDelimeter>
                  <csvOptions>separator=,^quote=none</csvOptions>
              </csv>
            </input>
            <output>
              <id>out1</id>
              <enabled>true</enabled>
              <description>Write XML</description>
              <type>XML</type>
              <url>
                  <path>./interface/output/Map04 CSV to XML</path>
                  <prefix/>
                  <fileExtension/>
                  <use83GUID>false</use83GUID>
              </url>
              <xsl>
                  <XSLT>Map04_CSV_to_XML.xsl</XSLT>
                  <XSLTPath/>
              </xsl>
            </output>
        </connectors>
      </map>
</syntaxhighlight>
</syntaxhighlight>


Line 35: Line 58:
In the <csvOptions>separator=,^quote="</csvOptions> element you will see that 2 values are set, '''separator''' and '''quote'''. These 2 settings are separated by a delimiter which happens to be **^** in this example. However if by some unfortunate coincedence your CSV file uses that character as a value separater then you might need to use a different delimited in the configuration - which is where the `<optionDelimeter>^</optionDelimeter>` can be used.
In the <csvOptions>separator=,^quote="</csvOptions> element you will see that 2 values are set, '''separator''' and '''quote'''. These 2 settings are separated by a delimiter which happens to be **^** in this example. However if by some unfortunate coincedence your CSV file uses that character as a value separater then you might need to use a different delimited in the configuration - which is where the `<optionDelimeter>^</optionDelimeter>` can be used.


[[file:delimiter.png|400px]]
[[file:delimiter.png|link=|400px]]


NOTE that if you don't want a quote character this should be represented as shown below  
NOTE that if you don't want a quote character this should be represented as shown below  
Line 46: Line 69:
=CSV Output Configuration=
=CSV Output Configuration=
<syntaxhighlight lang="xml">
<syntaxhighlight lang="xml">
    <map id="map211i" enabled="Y"
      <map>
        description="Commander4j Production Declaration to CSV">
        <id>Map12</id>
        <input id="in1" description="Read C4J XML File">
        <enabled>true</enabled>
            <type>XML</type>
        <description>XML to CSV</description>
            <path>./interface/input/C4J Production Declaration</path>
        <enableEmailNotifications>true</enableEmailNotifications>
            <mask/>
        <connectors>
            <pollingInterval>1000</pollingInterval>
            <input>
            <XSLT>C4J_PRODDEC_XML_to_CSV.xsl</XSLT>
              <id>in1</id>
        </input>
              <description>Read XML File</description>
        <output id="out1" enabled="Y" description="Write CSV">
              <type>XML</type>
            <path>./interface/output/CSV Output</path>
              <url>
            <type>CSV</type>
                  <path>./interface/input/Map12 XML to CSV</path>
            <optionDelimeter>^</optionDelimeter>
                  <mask/>
            <csvOptions>separator=,^quote="</csvOptions>
                  <prefix/>
            <outputFileExtension>csv</outputFileExtension>
                  <pollingInterval>1000</pollingInterval>
            <prefix>X</prefix>
              </url>
            <use83GUID>Y</use83GUID>
              <xsl>
             <XSLT/>
                  <XSLTPath/>
        </output>
                  <XSLT>Map12_XML_to_CSV.xsl</XSLT>
    </map>
              </xsl>
            </input>
            <output>
              <id>out1</id>
              <enabled>true</enabled>
              <description>Write CSV</description>
              <type>CSV</type>
              <url>
                  <path>./interface/output/Map12 XML to CSV</path>
                  <prefix>X</prefix>
                  <fileExtension>csv</fileExtension>
                  <use83GUID>true</use83GUID>
              </url>
              <csv>
                  <optionDelimeter>^</optionDelimeter>
                  <csvOptions>separator=,^quote=none</csvOptions>
              </csv>
             </output>
        </connectors>
      </map>
</syntaxhighlight>
</syntaxhighlight>


Latest revision as of 15:31, 30 December 2024

CSV Map Configuration

config.xml 

      <map>
         <id>Map04</id>
         <enabled>true</enabled>
         <description>CSV to XML</description>
         <enableEmailNotifications>true</enableEmailNotifications>
         <connectors>
            <input>
               <id>in1</id>
               <description>Read CSV</description>
               <type>CSV</type>
               <url>
                  <path>./interface/input/Map04 CSV to XML</path>
                  <mask/>
                  <prefix/>
                  <pollingInterval>1000</pollingInterval>
               </url>
               <csv>
                  <optionDelimeter>^</optionDelimeter>
                  <csvOptions>separator=,^quote=none</csvOptions>
               </csv>
            </input>
            <output>
               <id>out1</id>
               <enabled>true</enabled>
               <description>Write XML</description>
               <type>XML</type>
               <url>
                  <path>./interface/output/Map04 CSV to XML</path>
                  <prefix/>
                  <fileExtension/>
                  <use83GUID>false</use83GUID>
               </url>
               <xsl>
                  <XSLT>Map04_CSV_to_XML.xsl</XSLT>
                  <XSLTPath/>
               </xsl>
            </output>
         </connectors>
      </map>

The CSV input connector uses the OpenCSV library and requires very little configuration. The are a pair of settings which you should review 

 <optionDelimeter>^</optionDelimeter>

and 

 <csvOptions>separator=,^quote="</csvOptions>

In the <csvOptions>separator=,^quote="</csvOptions> element you will see that 2 values are set, separator and quote. These 2 settings are separated by a delimiter which happens to be **^** in this example. However if by some unfortunate coincedence your CSV file uses that character as a value separater then you might need to use a different delimited in the configuration - which is where the `<optionDelimeter>^</optionDelimeter>` can be used.

NOTE that if you don't want a quote character this should be represented as shown below with _none_ used as the value. 

 <csvOptions>separator=,^quote=none</csvOptions>

CSV Output Configuration

      <map>
         <id>Map12</id>
         <enabled>true</enabled>
         <description>XML to CSV</description>
         <enableEmailNotifications>true</enableEmailNotifications>
         <connectors>
            <input>
               <id>in1</id>
               <description>Read XML File</description>
               <type>XML</type>
               <url>
                  <path>./interface/input/Map12 XML to CSV</path>
                  <mask/>
                  <prefix/>
                  <pollingInterval>1000</pollingInterval>
               </url>
               <xsl>
                  <XSLTPath/>
                  <XSLT>Map12_XML_to_CSV.xsl</XSLT>
               </xsl>
            </input>
            <output>
               <id>out1</id>
               <enabled>true</enabled>
               <description>Write CSV</description>
               <type>CSV</type>
               <url>
                  <path>./interface/output/Map12 XML to CSV</path>
                  <prefix>X</prefix>
                  <fileExtension>csv</fileExtension>
                  <use83GUID>true</use83GUID>
               </url>
               <csv>
                  <optionDelimeter>^</optionDelimeter>
                  <csvOptions>separator=,^quote=none</csvOptions>
               </csv>
            </output>
         </connectors>
      </map>
System Keys
Element Value Description
path Path Specifies where output files are saved.
type CSV Specify the output file format
optionDelimeter char See earlier description.
csvOptions var=val See earlier description.
outputFileExtension Blank or ext File extension to use for output file.
prefix String Option to force all output files to begin with a specific string prefix
use83GUID Y or N For backwards compatibility with legacy apps which don't support long filenames. Generates a filename in 8.3 format
XSLT filename Not used

CSV Example Input

GEN Comma Separated Variables 1.csv 

01050213060215701000123780990210133980001,150213061500000017,15,15000,0210133,5021306021570,1008369,LS 2D  BLU,1000,00123780
01050213060215701000123780990210133980002,150213061500000017,15,15000,0210133,5021306021570,1008369,LS 2D  BLU,1000,00123780
01050213060215701000123780990210133980003,150213061500000017,15,15000,0210133,5021306021570,1008369,LS 2D  BLU,1000,00123780
01050213060215701000123780990210133980004,150213061500000017,15,15000,0210133,5021306021570,1008369,LS 2D  BLU,1000,00123780
01050213060215701000123780990210133980005,150213061500000017,15,15000,0210133,5021306021570,1008369,LS 2D  BLU,1000,00123780

CSV to XML Intermediate Format

 <?xml version="1.0" encoding="UTF-8"?>
 <data cols="10" filename="GEN Comma Separated Variables 1.csv" rows="5" type="CSV">
    <row id="1">
        <col id="1">01050213060215701000123780990210133980001</col>
        <col id="2">150213061500000017</col>
        <col id="3">15</col>
        <col id="4">15000</col>
        <col id="5">0210133</col>
        <col id="6">5021306021570</col>
        <col id="7">1008369</col>
        <col id="8">LS 2D BLU</col>
        <col id="9">1000</col>
        <col id="10">00123780</col>
    </row>
    <row id="2">
        <col id="1">01050213060215701000123780990210133980002</col>
        <col id="2">150213061500000017</col>
        <col id="3">15</col>
        <col id="4">15000</col>
        <col id="5">0210133</col>
        <col id="6">5021306021570</col>
        <col id="7">1008369</col>
        <col id="8">LS 2D BLU</col>
        <col id="9">1000</col>
        <col id="10">00123780</col>
    </row>
    <row id="3">
        <col id="1">01050213060215701000123780990210133980003</col>
        <col id="2">150213061500000017</col>
        <col id="3">15</col>
        <col id="4">15000</col>
        <col id="5">0210133</col>
        <col id="6">5021306021570</col>
        <col id="7">1008369</col>
        <col id="8">LS 2D BLU</col>
        <col id="9">1000</col>
        <col id="10">00123780</col>
    </row>
    <row id="4">
        <col id="1">01050213060215701000123780990210133980004</col>
        <col id="2">150213061500000017</col>
        <col id="3">15</col>
        <col id="4">15000</col>
        <col id="5">0210133</col>
        <col id="6">5021306021570</col>
        <col id="7">1008369</col>
        <col id="8">LS 2D BLU</col>
        <col id="9">1000</col>
        <col id="10">00123780</col>
    </row>
    <row id="5">
        <col id="1">01050213060215701000123780990210133980005</col>
        <col id="2">150213061500000017</col>
        <col id="3">15</col>
        <col id="4">15000</col>
        <col id="5">0210133</col>
        <col id="6">5021306021570</col>
        <col id="7">1008369</col>
        <col id="8">LS 2D BLU</col>
        <col id="9">1000</col>
        <col id="10">00123780</col>
    </row>
 </data>

XML Example Output from XSLT

 <?xml version="1.0" encoding="UTF-8"?>
 <test>
   <barcode>01050213060215701000123780990210133980001</barcode>
   <barcode>01050213060215701000123780990210133980002</barcode>
   <barcode>01050213060215701000123780990210133980003</barcode>
   <barcode>01050213060215701000123780990210133980004</barcode>
   <barcode>01050213060215701000123780990210133980005</barcode>
 </test>
Retrieved from ‘https://wiki.commander4j.com/index.php?title=CSV&oldid=1573’