Trim excel table column names in Azure logic app

Question

We have an Azure Logic app that reads an excelfile from sharepoint and inserts the rows into a sql db. Sometimes the column names in the excel file contains leading and/or trailing white spaces witch makes the insert fail because the column names in the Insert Row action wont match the column names in the file. What would be the best way to remove leading and trailing white spaces from column names in an Azure Logic app?

Answer 1

One of the workarounds is that you can use the replace expression by taking the name variable in the compose connector. Here is my workflow -

I'm using the below dynamic expression for replacing the white space

replace(variables('Name'),' ','')

output:

Answer 2

Instead of using the built-in replace, you could add a Flow step that pulls the data out from Excel via an Office Script. The Office Script can get the range/table and perform the cleanup just on the headers, then return the result to the next step in the Flow. You can get as creative with the details as you need to be:

function main(workbook: ExcelScript.Workbook)
{
  const table = workbook.getTable("DemoTable");
  const headersAndValues = table.getRange().getValues();
  cleanup(headersAndValues[0] as string[]);
  console.log(JSON.stringify(headersAndValues));
  return headersAndValues;
}

function cleanup(data: string[]): string[] {
  data.forEach((val, index) => {
    data[index] = val.trim();
  })
}