Parsing a CSV that contains embedded commas

[MelissaReed]

I have a CSV file that has embedded commas -- that is the commas are part of the "column" value:

The text of the CSV file looks like this  -- columns containing commas are "wrapped" in double-quotes:

Service Type,Tier,Agency,Installation Address,Sub Contractor,
Cable Modem,6,Supreme,"913 South Church St, Smithfield, VA 23430",Charter 
Cable Modem,6,Supreme,"1020 Pretlow St 1st floor Franklin, VA 23851",Charter 
Cable Modem,6,Supreme,"17000 Josiah Parker Circle, Isle of Wight, VA 23397",Charter 

Split() does not differentiate between true delimiters vs comma-as-value.

Is there some other function I should be using to transform the CSV content to an array in a way that respects the doublequote marks (like Excel does when you open a CSV)?

I can't just treat it as additional columns -- different rows have different numbers of commas in their addresses.

[Amit_Sharma]

Hi @MelissaReed,

Could you please show me your flow?

[v-liwei-msft]

Hi @MelissaReed 

Could you please tell me:

Do you want to convert the CSV file content into an array.
1. Split the CSV text with line breaks.
2.Apply to each traverses each line after division
(use skip in order to skip the first row, because the first row is the column name)
(Use Condition to determine whether the line is empty, because a blank line will appear after splitting with a newline character)
3. The first, second, third, and fifth columns do not contain commas and can be separated by commas.
4. Use indexOf and lastIndexOf to get the data in double quotes.

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

Best Regards,

Levi

[MelissaReed]

That's the problem -- I'm asking what the flow should look like to do this.

I can't just do a split(XXXXX   ',') because some of the commas are values (part of the string) and not delimiters.  Illustrated in my example above in bold.

[MelissaReed]

Thanks for your reply.  Let me respond to your response line by line

Do you want to convert the CSV file content into an array.   - YES

1. Split the CSV text with line breaks.    Yes

json( uriComponentToString( 

replace( replace( 

uriComponent( 

    split( base64ToString( body('Get_file_content_using_path')?'$content'] ), 

            outputs('Newline') ) 

), '%5Cr', ''), '%EF%BB%BF', '') ) )

         And in my next step I filter out the blank line (you do it with an IF inside your loop) 
2.Apply to each traverses each line after division
(use skip in order to skip the first row, because the first row is the column name)  -- YES

(Use Condition to determine whether the line is empty, because a blank line will appear after splitting with a newline character) -- YES (done with a filter in earlier step)
3. The first, second, third, and fifth columns do not contain commas and can be separated by commas.

--- This is where you've lost me.   

At this point -- inside the loop "item()" looks like this: 

Cable Modem,6,Supreme,"913 South Church St, Smithfield, VA 23430",Charter Communications,6/21/2016, .....

If I do a Split(), it will return 

Cable Modem

6

Supreme

"913 South Church St

Smithfield

VA 23430"

Charter Communications

6/21/2016, ....

because of the commas within the text.    Col 4 should parse to the full address in a single cell.  That's what I can't get to.
4. Use indexOf and lastIndexOf to get the data in double quotes.

--- After I've already split it out by commas?   What am I missing here?

[v-liwei-msft · Accepted solution]

Hi @MelissaReed 

The operation of split(xxx, ',') is not required when using slice.

Best Regards,

Levi

[grantjenkins]

@MelissaReed What do you want as your final output? Are you trying to convert a CSV to Excel, convert CSV to a JSON array, or something else?

[MelissaReed]

Sorry for the delay -- holiday break.

Final output is a CSV file.

What I'm doing is reading in the data to reformat it -- put the columns in the correct order, transform some of the values.   The crazy thing is I don't even need the address information.  But the content of the column (the embedded commas)  are messing up the parsing for the columns I do need.

It just seems like such a common situation I figured someone else had already solved the problem -- either a function I didn't know about or a technique I hadn't thought of.

[grantjenkins · Accepted solution]

@MelissaReed I've been playing around with this and ended up combining/re-writing a couple of the sample Office Scripts. I took the Convert CSV to Excel Table and Return Data as JSON sample scripts and now have a script that takes in CSV data and returns it as a JSON array.

I'm not sure how familiar you are with Office Scripts, but they offer a lot of functionality that you can't do natively within Power Automate. To create an Office Script, you need to open any Excel file in the browser and click on the Automate tab. You can then create a script that will be saved to your OneDrive Documents library in a folder called Office Scripts (the folder will be automatically created when you save your first script).

Below is how you would create the script that will cater for your question.

Open an Excel file online and click on the Automate tab, then click on New Script.

In the script window that appears, give the script a name (I've named mine Return CSV Data to JSON), then paste in the script below. Finally, click on Save script. This will then allow you to select the script from Power Automate. The nice thing about this script is that it will take in any CSV data and return the JSON for you, so you can reuse it across multiple Power Automates. One caveat is that because it's stored in your OneDrive you will need to share the script file with anyone else before they can use it in Power Automate.

/**
 * Convert incoming CSV data and return as JSON array.
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
  // Split each line into a row.
  let rows = csv.split("\r\n");
  if (csv.search(/\r\n/) > - 1) {
    rows = csv.split("\r\n");
  } 
  else if (csv.search(/\n/) > - 1) {
    rows = csv.split("\n");
  } 
  else if (csv.search(/\r/) > - 1) {
    rows = csv.split("\r");
  }

  // For each row, match the comma-separated sections.
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g

  // Create a 2D array with one row.
  let data: string[][] = [];

  rows.forEach((value, index) => {
    if (value.length > 0) {
      let row = value.match(csvMatchRegex);

      // Check for blanks at the start of the row.
      if (row[0].charAt(0) === ',') {
        row.unshift("");
      }

      // Remove the preceding comma and any start/end quotes.
      row.forEach((cell, index) => {
        let result: string = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
        result = result.indexOf("\"") === 0 ? result.substr(1) : result;
        result = result.lastIndexOf("\"") === result.length - 1 ? result.substr(0, result.length - 1) : result;

        row[index] = result;

      });

      data.push(row);
    }
  });

  // Create an array of JSON objects that match the row structure and return to Power Automate.
  return returnObjectFromValues(data);
}

// This function converts a 2D array of values into a generic JSON object.
function returnObjectFromValues(values: string[][]): TableData[] {
  let objectArray: TableData[] = [];
  let objectKeys: string[] = [];
  for (let i = 0; i < values.length; i++) {
    if (i === 0) {
      objectKeys = values[i];
      continue;
    }

    let object = {};
    for (let j = 0; j < values[i].length; j++) {
      object[objectKeys[j]] = values[i][j];
    }

    objectArray.push(object as TableData);
  }

  return objectArray;
}

interface TableData {}

Ok, now onto the Power Automate.

For this example, I'm using the following CSV file (includes the sample data you provided).

The full flow is below. I'll go into each of the actions.

Get file content retrieves the CSV data.

Run script will call the script we created by passing in the CSV data (Body from Get file content). Note that you can use any Excel file in this instance as we need an Excel file to run an Office Script This script won't modify the Excel file in any way. In this case I've just used a sample Excel file called scripts.xlsx. It will return our JSON in a property called result which we can use for the rest of our flow including Select, Create CSV table, Create HTML table, etc.

If we ran the flow now, we would see our JSON data as so:

Select uses the result from our Run script and extracts the properties. You can see that I've reordered the last two properties in this instance. The expressions used are below:

//From (input)
body('Run_script')?['result']

//Property Values
item()?['Service Type']
item()?['Tier']
item()?['Sub Contractor']
item()?['Agency']

And the output of the Select:

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[MelissaReed]

Wow -- this is amazing.    Thanks so much for such a thorough explanation.   Explaining HOW to use OfficeScripts was as valuable to me as the script you shared addressing my CSV problem.

I wound up going at it a different way, but I hope to make use of this approach in some of my other work where I will likely run into some of the same issues (dealing with embedded commas).  Thanks again!

M.

[Chaffy]

Thank you for posting this @grantjenkins!  I've been playing with a few csv files to build a report and I couldn't figure out why it kept failing to parse through one of the files.  It ended up being a field with multiple values that were separated by commas.  D'oh!  With your script, my report is being generated without issues.  Literally an easy button for me, I can't thank you enough! 

[JasonFan]

@grantjenkins  Thanks very much for your sharing, I'm a beginner with power automate and I want convert the CSV to an excel file with the same name. May you do me a favor how I can make it based on this script. Thanks