CSV To New Excel Table 05-04-2024 01:48 AM - last edited 05-04-2024 02:00 AM

[takolota]

Quickly & Easily Convert Various CSVs to New Excel Tables

Takes in most types of CSV data, dynamically identifies the header names, and creates an Excel table in a new or existing workbook.

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

(Works on comma-separated files where any items with in-data commas are surrounded in double quotes “String1, String2” but comma separated arrays with double quotes inside an item may also cause issues.)

For example, here is a CSV I downloaded from a random SharePoint list:

And I did not create any template doc table, & I did not adjust any names. I just selected the CSV file in my OneDrive, then ran the flow to get this output...

(But if you’re just downloading this template, then you will need to change the “Create table” action document library reference to your OneDrive folder instead of the id that is in there by default)

If needed, you should also be able to dynamically change the destination Excel to an existing workbook & worksheet based on other values in your flow. And the CSV data can automatically come from any preceding actions.

If you use something other than the default OneDrive action to get your CSV data, then add the CSV output to the "CSV_Data" field in the "Compose CSV data + Set-up" action.

If your CSV header row with column names is not the 1st row in your CSV, make sure to adjust the "ColumnNamesRow" value in the "Compose CSV data + Set-up" action to match the correct CSV row number.

This template is really just a combination of...
-The CSV to Dataset template with manual column mapping: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

-@RezaDorrani's video on exporting data to an Excel table: https://youtu.be/Kupz71dWYyY 

-@Paulie78's video on fast exporting to an Excel table: https://youtu.be/AA-D4lgjR3o 

Please 1st try downloading the below zip file & importing the whole flow as described in this post: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1891765/highl... 

If the standard zip file import method does not work for you, then you can try an alternative Power Apps Solution import method here: Re: CSV To New Excel Table - Page 15 - Power Platform Community (microsoft.com)

Or an alternative clipboard copy & paste import method from these 3 posts
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1841481/highl... 
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1841499/highl... 
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1841504/highl... 
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1843986/highl... 

Warning. The Groups HTTP connector with access to the graph API calls used for this template may be removed soon. You can vote to ask for this not to be removed here: https://ideas.powerautomate.com/d365community/idea/d51463df-6f0c-ed11-a81b-6045bd7c3e3f
You can also try using a different premium HTTP action to make that final call, like what Matt does here: https://www.matthewdevaney.com/quickly-add-multiple-rows-to-excel-table-in-power-automate/ 

Version 1.1 of the template flow does just this & uses the PREMIUM Azure HTTP action.

To set up the Azure HTTP action connection, use https://graph.microsoft.com for the url & for the Resource URI:

Version 1.1SP is set up to get the CSV from SharePoint & transfer the new Excel file & new table to SharePoint at the end of the flow.

Thanks for any feedback!

[takolota]

@Fernando21 I don't know of a way to fix that on the Excel side.

If you can, you may need to use a different method on those where you explicitly map each column so you can set the date values going to Excel to use a "yyyy-MM-dd" format.

Method where you can explicitly map the columns: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/m-p/1508191#M584 

[autoperfect93]

Hello @takolota ,

I am using the CSV_to_Excel_V1 flow but what I get as final output is an empty excel table containing only the header row with column names.

It's seems the flow does not load the data inside the table. The "Get file Metadata" action is retrieving the data of the previously created empty excel.

Could you advice at which step/action we are actually inputting the data inside the empty excel file?

[VANSKY]

Do you have any resource for how to extract input data with selected columns from a SharePoint list to an existing table on SharePoint? 

[takolota]

@autoperfect93 

The data to fill the Excel table should come from the "CSV_Data" input on the Compose CSV data + Set-up action. So whatever the text of your CSV is should be input there on that action.

What does the CSV_Data input look like on your run & what came out of the DataLines action?

[takolota]

@VANSKY 

Sorry, what do you mean by that? Extract input data for selected columns from one SharePoint list to another? Like you need to copy values from selected columns when items are created in one SharePoint list & pass them to another SharePoint list?
You may just want to open a ticket on the community forum for Building Flows to get help with that.

[VANSKY]

Hi. I created a SharePoint list form. The data users entered in the list form automatically stored in the SharePoint list. My question is I only want to extract some columns of my SharePoint list and paste them into another table with some common columns by Power Automate. My workflow passed but I see no values in that table. I have no idea why and which step(s) I am missing. 

[takolota]

@VANSKY You will want to post that question to the Building Flows community thread: https://powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows 

[autoperfect93]

Hi @takolota 

Thanks for the quick response. The DataLines action shows the data from the CSV file:

I noticed the last action in the flow "Send a HTTP Request" is using the POST Method in order to post the Values which is the data from my CSV file.

The flow runs successfully and it currently fails at the last step: 

I am pasting the Raw Outputs from the "Send a HTTP Request"

{

    "statusCode": 400,

    "headers": {

        "Cache-Control": "no-cache",

        "Transfer-Encoding": "chunked",

        "Vary": "Accept-Encoding",

        "Strict-Transport-Security": "max-age=31536000",

        "request-id": "2f047303-4419-47ee-aed1-3eeed4e7e110",

        "client-request-id": "2f047303-4419-47ee-aed1-3eeed4e7e110",

        "x-ms-ags-diagnostic": "{\"ServerInfo\":{\"DataCenter\":\"France Central\",\"Slice\":\"E\",\"Ring\":\"5\",\"ScaleUnit\":\"005\",\"RoleInstance\":\"PA3PEPF000062EF\"}}",

        "Timing-Allow-Origin": "*",

        "x-ms-apihub-cached-response": "false",

        "x-ms-apihub-obo": "false",

        "Date": "Fri, 03 May 2024 18:57:32 GMT",

        "Content-Type": "application/json",

        "Content-Length": "523"

    },

    "body": {

        "error": {

            "code": "InvalidArgument",

            "message": "The number of rows or columns in the input array doesn't match the size or dimensions of the range.",

            "innerError": {

                "code": "invalidArgument",

                "message": "The number of rows or columns in the input array doesn't match the size or dimensions of the range.",

                "date": "2024-05-03T18:57:33",

                "request-id": "2f047303-4419-47ee-aed1-3eeed4e7e110",

                "client-request-id": "2f047303-4419-47ee-aed1-3eeed4e7e110"

            }

        }

    }

}

[takolota]

@autoperfect93 That helps. So it isn't parsing the CSV the right way. Could you share what the header & at least 1st line of your CSV looks like? Like in an Excel or as raw text?

First it looks like you may have a column header "Deal No" that may have the same line-break in it that the CSV uses between its lines. That will not work. If possible you need to change that column header to remove the line-break or to replace it with a line-break that doesn't include a carriage return.
Secondly, I'll likely need to see your CSV header & 1st line because I don't know what that "," is doing at the end of your headers line after Unit of Measure. I would expect a line-break with a carriage return there, not a comma.

[autoperfect93]

Hi @takolota ,

I am sharing the file in Excel. Actually my first row is the name of the table. And below the first row the respective column names are displayed starting from the first column "A" which is "gl_entry_id" and so on. What I noticed while testing the flow is that when I trigger the flow and leave the first row "BOLD010 - BOLD Gelly + Alert 03/05/2024 - Start Date: 02/05/2024 - End Date: 03/05/2024" in the CSV file the flow does no recognize the column names which are situated in the second row.

When I run the flow and use a file with the table name in the first row the flow recognizes the first row as column header but that is wrong

Instead the actual column names are in the second row starting from "gl_entry_id"

This is why I decided to change the "Compose Header Name Columns" action and input "0" instead the "1" after ['ColumnNamesRow']

Once I Saved the flow and Ran it I get the correct column names:

I have an idea to remove the first row from the CSV file itself and then make sure that the column names are situated at the first row but I will wait for your answer on what is the best way to approach in this case.

From what I understand is that this first row which is the table name is creating the issue and is breaking the flow.

Here is the file opened in Notepad++

Thanks in advance for your help!