How to write expression to get the output of 2nd row on certain column in csv file?

Question

I am trying to copy sets of csv files between two states (ex. Arizona and Utah) based on the output shown on the second row/first row (after header) of csv files (where it has "State" column).

I have bunches of csv files that belong to Arizona.

I

And, I have bunches of csv files that belong to Utah.

I guess in SQL, it would be something like this:

select distinct top 1 State

from [Table] order by State desc

Now, I am trying to possibly use this type of flow (Lookup --> Set variable) to get the desired value, then, I will copy files into two different folders.

I created a new dataset (delimited) and created a parameter called "FirstRow".

What would be an expression inside Lookup?

Also, would Lookup work for going thru pool of multiple csv files or do I have to use ForEach?

Thanks.

Answer 1

Hi, yes you would need a foreach loop, that runs in sequential mode. Inside for loop, have a look up activity and set variable.

1. In the data set that you use for lookup, no need to have a parameter for first row (as you have shown in the ss). So remove the dataset parameter. In your case probably you will need a parameter for current filename that tthe loop brings.
2. Make sure to check first row as header in the same dataset
3. Now in the look up activity, check First row only check box (tis is like select top 1 * in sql)
4. Next to look up have a set variable, use the below expression

@activity('Lookup1').output.firstRow.State

This would give you the state information in each file stored on a variable. (Based on your use case for the variable value, can avoid it and make the loop run parallel too. So let us know what you do with the variable value)

Please let us know if this helps.

Thanks.