CSV Helper
Read, manipulate and write CSV files from urls or ingested via email
CSV Helper - Read, manipulate and write CSV files from URLs or ingested via email
The CSV Helper has two actions, one to read CSV files and one to write CSV files:
- 1.The read action can read a CSV string from a remote FTP location or HTTP URL location, including authorization tokens in headers.
- 2.The write 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.
Authentication on the CSV-Helper will be deprecated by end of 2021. Please use a REST Helper instead to fetch a file and then read it via CSV Helper.
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:[
[1, "Mike", "Germany"],
[3, "Akito", "Russia"],
[123, "Ivan", "Japan"],
[9867534, "Dennis", "China"]
]
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 |
The write CSV 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 }}].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
In order to a CSV file, you have to use the Dict Helper that reads in the CSV file and connect them like the below (Names you can see here are edited to better describe the particular flow action).
Once the two are connected with the arrow, select the Action "Filter a list e.g. CSV". Next, select the reference like in the example csv1. In this case, we use the reference to the previous connector without curly brackets {{ }}. Within the file condition, you can use standard Python filtering language, while each column is referenced by the word item in combination with a number, starting at zero. So in essence item[0] references the first column, item[1] the 2nd one, and so on.
The above example filters a CSV for all rows that have "Product A" or "Product B" in the 2nd column.
The below list of operators work:
<
<=
>
>=
==
!=
in
not in
Here are more examples:
item[1][:1] == 'A' or item[1][:1] == 'B'
This will return all rows that start with A or B in column 1. So you can use standard Python string references for beginning, end of or within a text string.
" Test 123" --> item[1][:3] --> Returns first 3 letters like: "Tes"
" Test 123" --> item[1][1:4] --> Returns letter 2 to 4 like: "est"
" Test 123" --> item[1][4:] --> Returns letter 5 till the end like: " 123"
" Test 123" --> item[1][-3:] --> Returns letter the last three letters till the end like: "123"
" Test 123" --> item[1][-4:-1] --> Returns 3 letters before the last letter like: " 12"
Alternatively, you could also use the below to filter for a string starting or ending with, which is just a different notation as in the block above:HTML
item[1].startswith("Test”)
item[1].endswith("123”)
For more examples, search for "Python string slicing"
As for the querying language with the boolean operators AND OR you can combine them as you wish as you can see below in a more complex example. For more details search for "Python boolean operators".
item[1][:5] == 'Prod1' or ( item[1][:5] != 'Prod3' and item[4][:3] == 'USA' )
Filters rows to all those that have Prod1 standing in column 2 or have Prod3 standing in column 2 AND column 5 starts with USA. So as you see,
Often times, 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 need 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.The connector or helper passing the object
- 2.A looper that changes something in every object
- 3.and a dict_helper that changes a particular field in a dict
- 4.and a date helper that formats data in a particular way
Last modified 8mo ago