Business Central CSV import with Power Automate ︎

There are plenty of things I try for blog posts that don’t make the cut. This was almost one of them. I’ll spare you the details on why exactly (until the end) so that we can keep focused on the good parts. My inspiration for this post came from watching this video: https://www.youtube.com/watch?v=2TIolfmtWQM, where a Excel script and a neat trick in Power Automate allow for a CSV file to be converted into an Excel table. The approach itself is very elegant and I’ve used the majority of it to my benefit. I recommend watching the video as I’ll skip over parts covered by it. The Excel script feature is truly awesome

Let’s lay out a scenario where a supplier/vendor will send a CSV file with new items, with some basic details, so you can have them created for you in BC. My super simple CSV for this example:

Barcode,Name,UoM,Price,RRP
4545FB9797,Football,PCS,2.75,8
4545BB1324,Basketball,PCS,3.26,10.5
4545TB1344,Tennis Ball,Pack,0.95,3
4545SB9976,Squash Ball,Pack,0.78,3
4545RB4457,Rugby Ball,PCS,3.25,10.99

As mentioned I’m skipping some details, due to the recommended video, but once you have reached the Excel script stage you will want to add a few lines to cater for an extra row which is created and a caption change to one header value. Here is my script:

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
  let selectedSheet = workbook.getActiveWorksheet();
  const alllines = lineCSV;
  let counter = 1;
  for (let line of alllines) {
    if (line.includes(",")) {
      let items = line.split(",");
      selectedSheet.getRange("A" + counter + ":E" + counter).setValues([[items[0], items[1], items[2], items[3], items[4]]]);
      counter++;
    }
  }
 
  workbook.addTable(selectedSheet.getRange("A1:E" + counter), true).setName("VendorItems");
  
//Delete Last blank line
 selectedSheet.getRange(counter+":"+counter).delete(ExcelScript.DeleteShiftDirection.up);
  //Retype the header otherwise the value is RRP\r
  selectedSheet.getRange("E1").setValue("RRP");
  }

From this point onward the video details are complete and it is time to think about extracting the data from the newly created Excel file to BC.

If you check over image 3 of the gallery you will notice that the “unitCost” and “unitPrice” values make use of an expression. This is to convert the data to the correct type. With it being CSV everything is text. This is an example of the expression to convert to decimals:

float(items('Apply_to_each_2')['RRP'])

All seems too simple right? Well yes in hindsight it sort of is. However, I will share the thing that tripped me up. The suggestion from the video is to use a compose action which is a carriage return. The end result of using that idea (with my data) is like this:

  "Barcode,Name,UoM,Price,RRP\r",
  "UVH72WFZ5EO1,facilisis,PCS,$25.33,55.93\r",
  "SVP51SRG4WY1,sodales,PCS,$85.55,50.16\r",
  "2RBS17JLI4CT,at,PCS,$71.72,29.32\r",
  "3YNT44PPY7LW,metus. In,PCS,$9.71,65.62\r",
  "4OGL41FFB6NB,fermentum convallis,PCS,$94.99,30.4\r",
  ""

Take note of the carriage returns which are the “\r” parts at the end of each row. Even after the data is converted into an Excel table those carriage returns remain in place. Remedy number 1 is to have the extra line to your script to rename the last column. Remedy number 2 is then the float() expression. I of course did not stumble across remedy number 2 that quickly, and tried some useless string manipulation . The performance of the import isn’t too bad. I tried a batch of 5000 records and it took 4.5mins to process. Baring in mind it is an automated process so those aren’t 4.5mins I lost in the space time continuum

The final thing I would do with this scenario is to move the file you have imported to an archive folder. I think for my scenario to work better you would need a custom api page. I think the catalogue item table is probably a better place for the type of data I was working with