How to read CSV file using Power Automate

We will see how to read CSV files using Power Automate and already a post is published to create a CSV file using power automate. This is one of the mandatory use cases in the Office 365 environment, either we will get the data from Email or from an external application or to read the data from a specified location.

In this scenario, we will get the CSV file from one drive and In the “ComposeNewline” action add a new line

In Capture CSV action, Split the CSV data and push it into an array for extraction.

Split Action

split(outputs('Get_file_content')?['body'], outputs('ComposeNewLine'))

We have to get the dynamic header values from CSV using the “Compose” action to map them in the “Select” action.

//Action Name - ComposeGetFieldNames
split(first(outputs('Capture_CSV')),',')

//Input - ['Header1,Header2','Data1,Data2']
//Output - ['Header1,Header2']

Next, we have to use the “Select” action to map the label to values.

//Select Action Expressions

From => skip(outputs('ComposeGetFieldNames'), 1)// To igonore the header row
Map Label => outputs('ComposeGetFieldNames')[0]
Map Value => split(item(), ',')?[0]

Pass the data into the “Create Item” action. It will automatically create a “Apply to each” action because the “Select” action returns as an array.

But the only problem is if the values have a comma in them like below, as you have seen a similar example in the “Create CSV” post.

//CSV Output

//CSV output from Get file Content
[
'Header1,Header2',
'"Data1,Value1",Data2'
]

//Select Output if there is no comma
[
{
"Header1":"Data1",
"Header2":"Data2"
}
]

//Select Output if it has comma - Wrong Output
[
{
"Header1":"Data1",
"Header2":"Value1"
}
]

To tackle this situation we need to replace the “,” with some other special characters like “#|#” from the CSV output before passing it into the Split expression.

To do this we need to loop each column in a row and replace the comma separator with some other special character. There might many solutions to this problem, We followed the following approach please comment if there is any better way to do it.

Pseudocode of the logic

//Initialize proper variables
Intialize mergetext = "";
Intialize newline = "";
Initialize IsSplit = false;
Initialize arrContent = [];

Apply to each 4 - output of capture csv // Loop each row

Split fields - split(items('Apply_to_each_4'), ',')

Set newline = "";
Set mergetext = "";

Apply to each 5 - output of split fields // Loop each column

//Checking first char begins with "
Compose: firstChar -
  if (equals(length(items('Apply_to_each_5')), 1), equals(variables('IsSplit'), 'false'),
startsWith(items('Apply_to_each_5'), '"'))

//Checking last char ends with "
Compose: lastChar -
    if (equals(length(items('Apply_to_each_5')), 1), equals(variables('IsSplit'), 'true'), endsWith(items('Apply_to_each_5'), '"'))
      

//condition to check the current value has comma in it 
If(output / firstchar = true) {
  if (output / firstchar = true and output / lastchar = true) {
    append to "newline" - replace(items('Apply_to_each_5'), '"', '') + #|#;
    set "mergedtext"
 to "newline";
  } else {
    append to "newline" - replace(items('Apply_to_each_5'), '"', '')
    set "mergedtext"
 to "newline";
    set "IsSplit"
 to true;
  }
}
else {
  if ("newline"
 is empty) {
    append to "newline" - items('Apply_to_each_5') + #|#;
    set "mergedtext"
 to "newline";
  } else {
    If(output / lastchar = true) {
      Set "newline"
 to "mergedText" + replace(items('Apply_to_each_5'), '"', '') + #|#;
      Set "mergedText"
 to "newline";
      Set "IsSplit"
 to false;
    }
    else {
      if (IsSplit = true) {
        Set "newline"
 to "mergedText" + replace(items('Apply_to_each_5'), '"', '');
        Set "mergedText"
 to "newline";
      } else {
        Set "newline"
 to "mergedText" + items('Apply_to_each_5') + #|#;
        Set "mergedText"
 to "newline";
      }
    }
  }
}
end of Apply to each 5
Append newline to arrContent
end of apply to each 4

The above Pseudocode should be translated to Power Automate actions. The above process is a simple string operation with the use of nested loops. The target is to replace the comma with some special characters and should not touch the commas inside the text. This “arrContent” variable should be passed to the “Capture CSV” action

The above comma-separated expressions should be changed as follows:

//Action Name - ComposeGetFieldNames
split(first(outputs('Capture_CSV')),'#|#')



//Select Action Expressions
From => skip(outputs('ComposeGetFieldNames'), 1)// To igonore the header row
Map Label => outputs('ComposeGetFieldNames')[0]
Map Value => split(item(), '#|#')?[0]

Whatever logic we can do in coding can be implemented in Power Automate with the help of expressions. We will see more use-cases related to expression.

Please post your queries in the comment section. Happy Building 🙂