Locoia
Ask or search…
K
Links

CSV Helper

Read, manipulate and write CSV files from urls or ingested via email

Overview

With the CSV Helper one can read, manipulate and write CSV files from URLs or other sources.

Actions

Authentication on the CSV-Helper was deprecated in 2021. Please use a REST Helper instead to fetch a file and then read it via CSV Helper.

1. Reading CSV file from URL location

This action reads a CSV string from a remote FTP location or HTTP URL location, including authorization tokens in headers.
Read produces a list (one entry for each row) of lists (one entry for each column), so the result of read_csv might look like this:
[
["Toyota", "2022", "Silver", "Camry"],
["Honda", "2021", "Blue", "Accord"],
["Ford", "2023", "Red", "Explorer"],
["Chevrolet", "2020", "Black", "Impala"]
]

2. Create CSV file

This action is used to create a CSV string from some previously generated data. The user input will contain a data reference - a ref ID pointing to a list of values, and a list of column field names. These column field names will be used when iterating through the data given by the ref ID to extract values from the list element. If the columns are empty, then the CSV will contain just one column, namely with the string representation of the corresponding line.
Configuration outcome matrix:
Print_headers
selected_columns
new_column_names
outcome
yes
Some valid columns
Some valid names
If selected and new names have different length -> error Use new column names
yes
Some valid columns
nothing
Use selected column names
yes
nothing
Some valid names
Expect new_colum_names to have ONE entry
yes
nothing
nothing
Error
no
Some valid columns
Some valid names
Info(Unused new names)
no
Some valid columns
nothing
Regular csv without headers
no
nothing
Some valid names
Info(Unused new names)
no
nothing
nothing
CSV with one column and no headers
This action produces a CSV string which can be written to a file on an FTP server, or sent as a mail attachment in a file.
The input needs to be either a list of dictionaries [{...}, {....}] or list of lists [[...], [...]]. If you only have one dictionary that you would like to insert, simply wrap it inside a list with the Dict Helper, e.g. [{{ my_dictionary }}].

3. Generate CSV from CSV text string

This action takes a string and converts it to CSV. For instance, you want to transform "John,[email protected],555-1234" to CSV.

Additional settings

Encoding

For all CSV actions an encoding can be selected which should be used to write/read the CSV file. This can be left at utf_8 for almost all cases. This is only needed if the CSV file was saved or is expected to be saved in a specific encoding.
This is especially relevant for umlaute and special characters.
If an encoding does not support characters used in the CSV (e.g. languages with incomplete coverage in latin-1), the step will result in an error.
Excerpt of encoding options

Filtering a CSV file

To filter a CSV file, use Filter List V2 from the Dict Helper
To add a filter:
  • Connect to a Dict Helper step
  • Select the Filter List V2 action.
  • Fill the List reference input with a CSV reference without the {{ }}
  • Set up your filter criteria

Modifying a whole column in a CSV or dictionary

Oftentimes, formats of e.g. dates are different in every system and need to be adjusted and modified to reflect the format of a different system.
Imagine a CSV with the column header datetime and the below three datetimestamps. That needs to be changed to only dates, like 2019-06-18
; datetime
; 2019-06-18 23:23:23.232736734
; 2019-04-12 23:23:23.232736798
; 2017-06-11 23:23:23.232736727
To do the above, you need to use a combination of:
  1. 1.
    The connector or helper passing the object
  2. 2.
    A looper that changes something in every object
  3. 3.
    and a dict_helper that changes a particular field in a dict
  4. 4.
    and a date helper that formats data in a particular way