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

Question

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.

`Import-CSV -Path 'theInput.csv' | Export-Csv -Path 'theOutput.csv' -UseCulture -NotypeInformation`. The `-UseCulture` switch makes PowerShell use the delimiter character **your** installed Excel understands, so after this, just double-click the output.csv file and it will open nicely in Excel. — Theo, Aug 03 '21 at 18:47
This is probably a duplicate of [this question](https://stackoverflow.com/q/62078546/9898643) if that is what yoy mean by _"but the formatting as a table seems to be problematic."_ — Theo, Aug 03 '21 at 18:55
@BigBen - I cannot find a way to do it - that's why it is problematic — Chris Eaheart, Aug 03 '21 at 19:10
@SantiagoSquarzon - will that format the Excel data as a table? — Chris Eaheart, Aug 03 '21 at 19:10
@Theo - I think that will get me an Excel file, but I do not believe the data in that Excel file will be formatted as a table, unless I'm mistaken? — Chris Eaheart, Aug 03 '21 at 19:12
@Theo - no, that doesn't appear to be my issue. I'm talking specifically about functionality in Excel where you "Format as a Table" - https://support.microsoft.com/en-us/office/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664 — Chris Eaheart, Aug 03 '21 at 19:13
This would be very easy to do with vba. Is that an option for you? — chris neilsen, Aug 03 '21 at 19:31
So, plan then list steps to algorithm then to code... or did you just expect us to write it? Check on here, there are several answers with code that you could edit. Even one of mine may start you off. I found about 10 when I was looking for one I needed. — Solar Mike, Aug 03 '21 at 19:32
Should be easy with module [ImportExcel](https://www.powershellgallery.com/packages/ImportExcel/7.2.2) where you can do ``Import-CSV -Path 'X:\theInput.csv' | Export-Excel -TableName 'WhatEver' -TableStyle Medium13 -Path 'X:\'theOutput.xlsx'``. (TableStyles enum [here](https://documentation.help/OfficeOpenXML/8331a2de-ea92-deda-f33c-968796f91541.htm)) — Theo, Aug 03 '21 at 19:39
@chrisneilsen - it is not, unfortunately - this has to be completely hands off and done without even opening the file — Chris Eaheart, Aug 03 '21 at 19:39
Maybe use [this](https://stackoverflow.com/q/64970809/445425) — chris neilsen, Aug 03 '21 at 19:40
Using the Excel COM object, you need to create a Range object from the table columns and cells and add that to the sheets ListObjects collection (returns a ListObject object you can capture in a variable). You can then set the `.TableStyle` property on this object using `$ListObject.TableStyle = "TableStyleMedium9"` — Theo, Aug 03 '21 at 19:45
@Theo - your approach works - I can just create a Scheduled Task and run the command you provided. Thanks! — Chris Eaheart, Aug 03 '21 at 19:55

score 1 · Accepted Answer · answered Aug 03 '21 at 20:04

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.

score 0 · Answer 2 · answered Dec 09 '21 at 01:25

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

score 0 · Answer 3 · answered Oct 21 '22 at 00:17

0

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.

answered Oct 21 '22 at 00:17

Tyler Kolota

21
2

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/32994631) – Fastnlight Oct 27 '22 at 20:04

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

3 Answers3

Linked

Related