CODEWITHSUNDEEP
excelpowershellcsvpower-automate
Chris Eaheart
Chris Eaheart

Reputation: 568

Automated way to convert CSV to Excel, and format as a table

I am using Power Automate to process an Excel file - the data in the single-tab Excel file is formatted as a table, which makes it easier to use in Power Automate.

The system where the data is coming from generates a CSV file, and I'm trying to figure out a middle-tier solution to convert the CSV file to an Excel file, and format the data as a table.

I've looked into Power Automate solutions (any solution seems to require a Premium connector), and I've looked into PowerShell - converting CSV to Excel is fine, but the formatting as a table seems to be problematic.

I'm open to other solutions, but I'd prefer to use something in the Microsoft stack and steer away from other vendors/third parties.

Upvotes: 0

Views: 6493

Answers (3)

Tyler Kolota
Tyler Kolota

Reputation: 51

If anyone prefers Power Automate for this, try this free template that converts most CSVs to a new Excel file with a new table based on the CSV headers & values… https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/td-p/1826096

No premium, 3rd party, or Office Script connectors required.

Upvotes: 0

f33d-m3
f33d-m3

Reputation: 1

I had to solve this exact problem recently and I implemented a fully automated solution using only O365 cloud tools.

Csv is emailed to me which triggers the flow to create a csv file in SharePoint. Then flow runs an excel macro against it to set the headers to a format with no spaces, this is important for the next step.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Set range A1:H1 on selectedSheet
    selectedSheet.getRange("A1:H1").setValues([["Model", "Serial", 
    "AssignedTo", "LastCheckin", "WarrantyExpiry", "State", "Substate", 
    "DisposalReason"]]);
}

Then run another macro against it to format the data as a table with a variable size, depending on the days export.

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet.
    let selectedSheet = workbook.getActiveWorksheet();
    
    // Create a table with the used cells.
    let usedRange = selectedSheet.getUsedRange();
    let newTable = selectedSheet.addTable(usedRange, true);
}

Fig 1. How to use Excel Macros from Power Automate Excel Macro

Upvotes: 0

Theo
Theo

Reputation: 61178

As commented, if you install and import module ImportExcel, then it would not be hard to format your imported CSV data as table.

Import-CSV -Path 'X:\theInput.csv' | Export-Excel -TableName 'WhatEver' -TableStyle Medium13 -Path 'X:\'theOutput.xlsx'

Style Medium13 is an example. There are lots more styles. You can find the TableStyles enum here

It should also be possible to do this using Excel COM object.

For that, after loading the CSV data in a new sheet, you need to create a Range object from the table columns and cells and add that range to the sheets ListObjects collection. That will return a ListObject object you can capture in a variable.
You can then set the .TableStyle property on this object using $myListObject.TableStyle = "TableStyleMedium9"

However, this needs for you to have Excel installed where that is not needed when using the ImportExcel module.

Upvotes: 1

Related Questions