CSV: Difference between revisions

From Commander4j
← Older editNewer edit →
No edit summary
No edit summary
Line 30: Line 30:
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|200px]]
[[file:delimiter.png|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  

Revision as of 18:32, 24 August 2024

CSV Map Configuration

config.xml 

  <map id="Map04" enabled="Y" description="GEN Comma Separated Variables">
       <input id="in1" description="Read GEN Comma Separated Variables">
           <type>CSV</type>
           <path>./interface/input/403 (GEN Comma Separated Variables)</path>
           <mask/>
           <pollingInterval>1000</pollingInterval>
           <XSLT>CSV_to_XML_Example.xsl</XSLT>
           <optionDelimeter>^</optionDelimeter>
           <csvOptions>separator=,^quote="</csvOptions>
       </input>
       <output id="out1" enabled="Y" description="Write XML Example">
           <path>./interface/output/403 (GEN XML)</path>
           <type>XML</type>
           <XSLT/>
       </output>
   </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="map211i" enabled="Y"
       description="Commander4j Production Declaration to CSV">
       <input id="in1" description="Read C4J XML File">
           <type>XML</type>
           <path>./interface/input/C4J Production Declaration</path>
           <mask/>
           <pollingInterval>1000</pollingInterval>
           <XSLT>C4J_PRODDEC_XML_to_CSV.xsl</XSLT>
       </input>
       <output id="out1" enabled="Y" description="Write CSV">
           <path>./interface/output/CSV Output</path>
           <type>CSV</type>
           <optionDelimeter>^</optionDelimeter>
           <csvOptions>separator=,^quote="</csvOptions>
           <outputFileExtension>csv</outputFileExtension>
           <prefix>X</prefix>
           <use83GUID>Y</use83GUID>
           <XSLT/>
       </output>
   </map>

| 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"?>

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

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=1007’