Read a csv file into a table in iPaaS

Hi Chuck, here is my go-to logic for parsing CSVs.

Once you've run a step to get iPaaS the CSV file (e.g. Windows File Explorer, SFTP)...

Replace new line characters with carriage return characters (to get the ↲, hold Shift and press Enter)

Create a list of the rows, splitting on the ↲.  This give a list of the individual lines in the CSV.

Use a for loop, starting at 2 (to account for the header row) and End = length of the list created in Step 2.

The first step of the for loop is making another split list to separate the individual items of the row, splitting on the delimiting character (also replace quotation marks with empty string just to be safe).

From here, when referencing the delimited list for the single CSV row, you'd use the indexes to reference the various columns (e.g. Column 1 index = 1)

Now, if you have commas in the data itself (which would mess with the columns of the CSV), what I would do is:

1. Create a xlsx of the data

2. Replace commas with a character that is not in the data (e.g. ` or | )

3. Convert it to a CSV

4. In the CSV, replace commas with another character that is not in the data

5. Replace the character used in Step 2 with a comma to put the original commas back into the data

6. Have the final step above use the character from Step 4 as the delimiter

Example:

Excel File:

Replace commas:

Convert to CSV: Test1,Test2|Test3,Test4

Replace commas: Test1`Test2|Test3`Test4

Replace | with , : Test1`Test2,Test3`Test4

Now use ` as your delimiter in iPaaS

Hopefully that all makes sense and is useful in your case. Of course let me know if you have any questions.

Cheers,
Mike Ligouri
Technical Support Consultant