Hello,
I’m working with an API that only returns CSV format in the response-body. Here’s an example of what I’m receiving:
I’ve separated the response into rows with the line break as a delimiter, and have labeled the column with the contents of the first row (which are the field headers), and the result is the following:
Now I want to separate it into different columns, but in the “Split delimited text into columns” action I have to give a manual name to each column, and that won’t work for other CSVs with different headers. And it doesn’t remove the quotes from the text, which I want it to do (only removes them from the first column).
So I tried saving this first column as a CSV file, and then importing it into EasyMorph. However, in this CSV more quotes have been created, and if I import the CSV and then check “Ignore quoting”, I have the information divided into columns but with a lot of extra quotes:
Ideally, what I need is an “Import delimited text file” action that doesn’t get the data from an external file, but from a cell in EasyMorph, in this case the response-body. Is there some way I can replicate this behaviour in EasyMorph, and avoid to download the file in badly formatted CSV that later has to be re-imported and having to do multiple actions in order to take all the quotes away?
Thanks very much,
Roberto
UPDATE:
I was able to do it by splitting the values into more columns than actual fields there are (to not lose any info), see action “Split delimited text into columns” in the screenshot. Then, I labeled the column with the contents of the first row. Once that was done, to delete all the extra columns, I derived the table twice: in one table I got the table metadata (column names), and only kept the columns that did not match a certain regex pattern (Column, Column(1), Column(2), etc.). In the other derived table, I used the action “Select Columns by Lookup” to only keep the relevant columns, which had been filtered in the Metadata table:
I attach a sample project with this solution + sample data:
Sample project treat CSV in Response-Body.morph (11.1 KB)
Sample Response-Body.xlsx (8.8 KB)
What do you think of this solution? Could there be a lighter way to do it, without creating all the extra columns to delete them later? By the way, is there a limit to the number of columns that can be created using the “Split delimited text into columns” action?
Thanks!