How to dynamically convert CSV to XML using Groovy

Document created by Adam Arrowsmith Employee on Oct 13, 2015Last modified by Adam Arrowsmith Employee on Apr 18, 2016
Version 3Show Document
  • View in full screen mode
This script performs a dynamic conversion of a CSV flat file to a "flat" XML structure. A separate XML document is created for each row in the flat file. The script assumes the first row contains column headers. The column headers are used as the XML element names, after sanitizing by replacing all non-alphanumeric characters with underscores. The XML contains a "records" root element under which the "record" element is added to allow the documents to be easily combined later in the process if desired.

 

The script assumes properly-escaped CSV source format (comma delimited, double quote text-qualified) and handles embedded line breaks and double quotes.

 

Script params (set at top of script):
  • param_forceEmptyTag - By default empty values in the CSV are not output in the XML. Set to true to force empty tags.

 

Example input:
First Name, Bio
Betty, "Great travel companion, good with navigation, and
always looking for adventure."
Michael, "Basketball player with ""amazing"" shooting accuracy."

 

Example output:

Document 1:
<records>
  <record>
    <First_Name>Betty</First_Name>
    <Bio>Great travel companion, good with navigation, and 
always looking for adventure.<Bio>
  </record>

Document 2:

<records>
  <record>
    <First_Name>Michael</First_Name>
    <Bio>Basketball player with "amazing" shooting accuracy.<Bio>
  </record>
</records>
  1. Add a Data Process shape to your process
  2. Within the Data Process shape, add a "Custom Scripting" processing step.
  3. Copy and paste the attached Groovy script into the script editor, replacing the default contents.
  4. Run test data through the script to capture the resulting XML document.
  5. Save the output to a local file.
  6. Create a new XML Profile component and import the saved example XML.
3 people found this helpful

Outcomes