How to Parse a CSV File

In this article we will show you how to parse CSV data files.

This code assumes that you are using a From Translator code to read your CSV files and push them onto the Iguana queue for processing (this is not addressed in this tutorial).

We recommend that you type in the code for each step, but we also include complete Sample Code if you prefer to paste it in and follow along.

Create the Channel [top]

  1. Create a Channel with the the following settings:
    • Source = From Translator
    • Destination = To Translator
    • Channel name = Parse CSV File
  2. Click the Add Channel button to create the channel.
    Ignore the red warning messages, see resolving the milestone configuration error.
  3. Open the Translator by clicking the Edit Script link at the bottom of the Destination tab.
  4. Download and import the Parse_CSV_File_To_Translator.zip project file.
    This file contains a skeleton project and 12 CSV sample messages.
  5. Iguana will load the project and data into the Translator, your screen should look like this:

Tutorial Instructions [top]

  1. Pass the message data to the script.
    • Iguana automatically passes the message data to the main() function
    • The message can be accessed using the Data parameter
    • No action is needed
  2. Parse the CSV message into a table.
    Add the following line of code to your script:
  3. Inspect the parsed data by clicking on the table annotation:
  4. Navigate through the other 11 sample CSV messages
    1. The first 9 messages contain valid CSV data
    2. The 10th message contains invalid data and raises an error
    3. The 11th and 12th messages use different delimiters
  5. Fix the error in the 10th message
    1. View the error:
    2. We can see that the issue is the un-escaped single quotes (“) before and after Speedie that both need to be escaped by changing them to two quotes (“”)
    3. Check the message data to be sure, click on the Data annotation to view the message:
    4. Viewing the message data confirms that we indeed have two un-escaped quotes (“) within a quoted field:
    5. Edit the current sample message by clicking the Edit Message icon on the toolbar:
    6. Escape the quotes around Speedie by changing them from single (“) to double (“”), then click Save.
      Speedie before editing:

      Speedie after editing:
    7. Now we are back in the Editor and the parsing works correctly:
    8. The correction we made means that our 9th and 10th messages are now exactly the same, you can confirm this be inspecting the messages using the sample data management screen.
  6. Parse the 11th message correctly by recognizing the tab delimiters
    1. Add the following line of code:
    2. View both parse results by clicking on their respective table annotations:
    3. Compare the two annotations, to confirm that the parse is working:
  7. Parse the 11th message correctly by recognizing the bar “|” delimiters
    1. Add the following line of code:
    2. Compare the annotations, to confirm that the parse is working (as we did above).

Complete Sample Code [top]

Here is the completed CSV parsing code that you can cut and paste into your script:

local csv = require 'csv'

function main(Data)
   -- 1) Parse a comma separated CSV file
   local Csv = csv.parseCsv(Data)       -- comma separated (default)
   
   -- 2) Parse a tab separated file
   --local Csv = csv.parseCsv(Data, '\t') -- tab separated (sample message 11)
   
   -- 3) Parse a bar "|" separated file
   --local Csv = csv.parseCsv(Data, '|')  -- bar separated (sample message 12)
   trace(Csv)
   
   
   -- Examples of what you can do:
   
   -- a) Use in a To Translator and add code
   --    for saving patients to a database
   
   -- b) Use in a Filter Component and map to
   --    XML/JSON/HL7/etc then queue for further processing
end

More Information [top]