Having trouble of creating CSV table dynamically from different format of CSV File

[ripalamin]

I have different file format of csv file coming from SharePoint. I want to update date column in my csv file. CSV file doesn't contain table. Date column will be in different index in different CSV file. I retrieve  data from csv file. Now I want to create dynamically CSV Table from CSV file data. I am not able to map column name with Data.

Input of File is:

But Column name will be different for different CSV file. I want to create dynamically output in JSON Format. 

My flow looks like

[grantjenkins]

If you're getting commas within your values in more than one column and the column names could change - I'd suggest using an Office Script to convert your CSV data to JSON. I'm just off to sleep now (2AM for me), but will provide an example later for you. Hopefully someone else can also provide a solution in the meantime 🙂

[grantjenkins · Accepted solution]

This solution uses an Office Script that will allow you to pass in CSV data (including commas) and it will return a JSON object.

I have a central SharePoint Library were I store all of my Office Scripts so they can be reused across my flows. I also have a blank Excel file in the same location that I use to pass into the Office Script.

For this example, I'm using the following CSV data.

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

Get file content using path gets our CSV data.

Run script from SharePoint library uses the Office Script (shown at the bottom of this post) that takes in the CSV data and returns a JSON object for you to work with in your flow.

After running the flow we would get the following output.

{
  "result": [
    {
      "ID": "E4",
      "PartName": "ZZ_PC_Watmar",
      "Value": "3,18.07",
      "Month": "1",
      "Date": "10/03/2023"
    },
    {
      "ID": "D4",
      "PartName": "ZE_PC_Watmar",
      "Value": "132.96",
      "Month": "2",
      "Date": "10/03/2023"
    },
    {
      "ID": "V5",
      "PartName": "ZN_PC_Stone",
      "Value": "370.38",
      "Month": "3",
      "Date": "10/03/2023"
    },
    {
      "ID": "K1",
      "PartName": "ZV_PC_Water(Prod,Dev,UAT)",
      "Value": "13.61",
      "Month": "4",
      "Date": "10/05/2023"
    },
    {
      "ID": "D1",
      "PartName": "ZZ_PC_XVH",
      "Value": "58.41",
      "Month": "4",
      "Date": "10/03/2023"
    }
  ],
  "logs": []
}

Below is the actual Office Script. If you have this setup centrally then you can reuse it across your other flows that need CSV data converted to JSON.

/**
 * 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 {}

[ripalamin]

After successfully run script as you have mention above, store output in Array variable. I want to check "Date"

 column is exist or not. If Yes, then update Date Column with today date. Am I able to check Column is exist or not? 

[grantjenkins]

You can add a Select using the output from your script and update the dates that are not blank. The expressions used are:

//From
body('Run_script_from_SharePoint_library')?['result']

//ID
item()?['ID']

//PartName
item()?['PartName']

//Value
item()?['Value']

//Month
item()?['Month']

//Date
if(equals(item()?['Date'], ''), '', formatDateTime(utcNow(), 'dd/MM/yyyy'))

You can then use the output from the Select to create a new CSV file.

[ripalamin]

I want to check 'Date' Column. Because some of the .csv file has not Date column.

[grantjenkins]

The expression I mentioned should do this. If there is no date column or it's empty then it will set/leave it as blank, otherwise it will set it as today's date.

if(equals(item()?['Date'], ''), '', formatDateTime(utcNow(), 'dd/MM/yyyy'))