CSV file to SharePoint List Best Way to Do This

[koneill2020]

All,
I'm trying to read a csv file and then place the values into a SharePoint list.

What is the best practice for doing this as I thought I needed to use the Get file content using path but in using this, I'm getting this error so looking for some guidance or examples on how best to do this:

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('Get_file_content_using_path')' is of type 'Object'. The result must be a valid array.

Note: in the below I was just trying to get from the csv file one column of the data into a SharePoint list:

[grantjenkins · Accepted solution]

If your Excel data doesn't contain any commas within the data (Example: "Jenkins, Grant", or 1,394) then you can split the data by comma to get an array of items you can loop over.

However, if there is the potential to have commas, then the best approach would be to use an Office Script where you can pass in your CSV and it would return a JSON array you can iterate over.

Here is an example using Office Scripts.

I have a SharePoint Document Library setup that contains all of my Office Scripts that I use across all my Power Automates. Inside here, I also have a blank Excel file as you need to select an Excel file when using Office Scripts.

Below is the actual Office Script I will use for this example. I've also attached a copy of the Office Script to this post so you can download then upload to your library. Note that I had to upload as a Zip, so you would just need to extract the script before uploading to your library.

/**
 * 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].trim();
    }

    objectArray.push(object as TableData);
  }

  return objectArray;
}

interface TableData {}

For this example, I'm using the following CSV data. Note that there are commas in the Campaign Owner values and some of the numbers.

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

Get file content using path retrieves our CSV data.

Run script from SharePoint library user my blank Excel file for the Workbook and the Office Script called Return CSV Data as JSON. This will display a single parameter where you select the File Content from Get file content using path (our CSV data).

Apply to each uses the following expression to iterate over each of the JSON objects returned.

outputs('Run_script_from_SharePoint_library')?['body']?['result']

Compose is just to show what each of the objects contains within the Apply to each.

After running the flow.

In my case, I could use the following expressions within my Apply to each to extract out the specific property values.

items('Apply_to_each')?['Campaign Owner']
items('Apply_to_each')?['Campaign Name']
items('Apply_to_each')?['Launch Date']

items('Apply_to_each')?['COLUMN NAME']

[koneill2020]

This is awesome many thanks!

Any suggestions what to do when the Apply to each is over 5000 rows?

Error:

Action 'Apply_to_each' failed

Unable to process template language expressions for action 'Apply_to_each' at line '0' and column '0': 'The number of foreach items limit exceeded for action 'Apply_to_each': maximum '5000' and actual '74886'.'

[grantjenkins]

75,000 items is a lot to process 🙂

One option would be to have a flow that gets the items using the Office Script and another flow where you pass in chunks of 5000 items which iterates over them and performs what you need.

You can split your 75,000 items using the chunk expression passing in your data, and 5000 as the chunk (amount of items) you want.

[koneill2020]

Thanks - I got the data filtered down to about 6000 rows so maybe the chunk expression will be helpful now and can be utilized.

[dkabza]

Hi, 

thanx for the solution. I've got a question if any body get BadGateway (504) error runnig this script and if so, how you managed this?

[takolota]

@dkabza @koneill2020 @grantjenkins 

By the way, there is also this template for handling in-data commas directly in Power Automate (No Script) & it can handle up to the max 100MB message size.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191