Pull two columns of data from a csv

(0) Share

Report

Posted on by JPey

I have a csv with varying rows but 12 columns

Example:

h1|h2|h3|h4|…|12|
a |b |c |d |….|e |

g |h |x | |….|e |

D |f |j |x |….|e |

I only want the data in columns h2 and h3

In an array like b,h,f,c,x,j

is that possible?

Categories:

General topics

All responses (3)

Answers (1)

Sort by

Manish Solanki 15,169 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JPey

Here is the sample flow to get values of header 2 & header 3 and merge the values into single array.

I have used compose action to store the input csv data:

h1|h2|h3|h4|
a|b|c|d|
e|f|g|h|
i|j|k|l|

Now, add Select action to get the values of header 2. We will add expression in 'From' & 'Map' parameter to achieve this:

Expression used for 'From':

skip(split(outputs('Compose'),decodeUriComponent('%0A')),1)

decodeUriComponent('%0A') is used for new line '\n'. If in original input, you get carriage return '\r' then use decodeUriComponent('%0D'). If you get both then use decodeUriComponent('%0D%0A')

In Map parameter add the below expression. To enter the value in Map textbox, first click "Switch Map to text mode" button on the right side of it:

split(item(),'|')[1]

pls note that index starts from zero so to get value of second column we have used index 1 in the above expression.

Similarly, add another select action to fetch the values of 3rd column:

Expression used for 'From' in Select 2 action:

skip(split(outputs('Compose'), decodeUriComponent('%0A')), 1)

Expression used for 'Map' in Select 2 action:

split(item(), '|')[2]

Finally, to join both array we will add an expression in the compose action as shown below:

union(body('Select'),body('Select_2'))

The output of final compose action will look like this:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks
JPey 70 on at

Like
a
(0)

Report
Copy link

Link copied!

@ManishSolanki what you demonstrate is exactaly what I am looking for, however, it is not working. I am getting an error:
InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '@split(item(),'|')[1]' failed: 'The template language expression 'split(item(),'|')[1]' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

Here is a sample of the csv data:
"\"user_name\",\"password\",\"first_name\",\"last_name\",\"phone_1\",\"phone_2\",\"carrier_1\",\"email_2\",\"voice_1\",\"voice_2\",\"organization\",\"group_1\"\r\n\"testa\",\"\",\"D Test\",\"Mah\",\"1111111111\",\"\",\"Verizon\",\"\",\"2222222222\",\"\",\"test company\",\"Alert\"\r\n\"testb\",\"\",\"Test\",\"Edwards\",\"3333333333\",\"\",\"\",\"\",\"4444444444\",\"\",\"test\",\"Alert\"\r\n
Verified answer

Manish Solanki 15,169 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @JPey

It seems that there is empty string in the input array because of which it fails. Pls modify the flow as shown below:

Add "Filter array" action before Select action to filter out the empty string element:

From - skip(split(outputs('Compose'),decodeUriComponent('%0D%0A')),1)

Click "Edit in advanced mode" button on the bottom & enter the below query:

@not(equals(item(), ''))

Next, in the "From" parameter of Select action, pass the body of filter array action:

In Map parameter set the expression as

split(item(),',')[1]

Similarly, pass the body of filter array to the Select 2 action:

Expression for Map parameter

split(item(), ',')[2]

Finally, using union function merge the output of both Select action to get the desired array:

union(body('Select'),body('Select_2'))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks