Skip to main content

Notifications

CSV To New Excel Table

takolota1 Profile Picture Posted 04 May 2024 by takolota1 4,750

 

takolota_0-1666901862004.png


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:
CSVData.png

 

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...
ExcelTableData.png

(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 


 

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!


Solution Zip Download Link: https://drive.google.com/file/d/1TpndUP56X2Vf4DPTRPR4JXxVMZl40PRB/view?usp=sharing


Legacy Power Automate Import: https://drive.google.com/file/d/1Xiwevt00X1ztvTbiO6CVlOYJ8C_QsBzC/view?usp=sharing

​​​​​​​

Categories:

Comments

  • RaulAlantra Profile Picture RaulAlantra
    Posted 14 Oct 2024 at 09:37:37
    CSV To New Excel Table
    I get this error message in the "Send an HTTP request" instruction:
     
    The number of rows or columns in the input matrix does not correspond to the size or dimensions of the range 
     
    Any clue? Thanks.
  • Fernando21 Profile Picture Fernando21
    Posted 07 Aug 2024 at 13:30:21
    CSV To New Excel Table
     Hello, 
     
    I have the same issue as autoperfect93, 
       "message": "The response is not in a JSON format.",
       "innerError": "Cannot read server response."
     
    But I have only this error with a csv files with ~70 000 rows.
    When i use it with smallest csv files (~3000 rows) it works perfectly.
     It works even with multiple files.
    And in the case of this big file, I can put smallest files with it, the small files will be converted to excel but not this big one.
     
    Yes, 70000 rows, that means I cannot use the alternative by Office scripts and also I cannot use the premium connector.
    I use the Office Groups HTTP connector.
     
    What could mean the "innererror" : cannot read server response ?
     
    I suppose it's related to the size of the file, where should I put some delays to maybe let time to get the response ?
    I tried some but without success.
     
  • takolota1 Profile Picture takolota1 4,750
    Posted 09 Jun 2024 at 18:14:36
    CSV To New Excel Table

    @autoperfect93 

    It may be running into some other error & the error response message is not formatted in proper JSON or something.

    It’s hard to diagnose if it comes on randomly, but the most recent solution package import CSVToNewExcelTable_x_0_0_x.zip includes a Non Premium version of the flow that, instead of a graph call, uses an Office Script to batch create up to like 18,000 rows of a CSV to the new Excel table. But it does take longer to run.

    So if you want to try for something more reliable, are fine with a slower flow, & don’t have CSVs with more than 18,000 rows, then you could try that version instead.

  • autoperfect93 Profile Picture autoperfect93 96
    Posted 09 Jun 2024 at 07:56:48
    CSV To New Excel Table

    Hello @takolota ,

     

    I have been using the flow to convert the CSV files into XLSX since the beginning of May but now I started facing the following error:

     

    autoperfect93_0-1717919616147.png

    The flow fails at action "Invoke an HTTP request" with error "The response is not in a JSON format."


    Here is the raw outputs of the step:

     

    "statusCode": 302,
        "headers": {
            "Cache-Control": "private",
            "Location": "/error/error.html?aspxerrorpath=/x/_layouts/xlrestinternal.aspx",
            "Strict-Transport-Security": "max-age=31536000",
            "request-id": "bb1a2b23-8a16-44de-a3c2-fc544db63d0e",
            "client-request-id": "bb1a2b23-8a16-44de-a3c2-fc544db63d0e",
            "x-ms-ags-diagnostic": "{\"ServerInfo\":{\"DataCenter\":\"France Central\",\"Slice\":\"E\",\"Ring\":\"5\",\"ScaleUnit\":\"000\",\"RoleInstance\":\"SL3RIPS0213B089\"}}",
            "Timing-Allow-Origin": "*",
            "x-ms-apihub-cached-response": "false",
            "x-ms-apihub-obo": "false",
            "Date": "Sun, 09 Jun 2024 07:45:45 GMT",
            "Content-Length": "256",
            "Content-Type": "application/json"
        },
        "body": {
            "error": {
                "code": 302,
                "source": "france-001.azure-apim.net",
                "clientRequestId": "17db72ef-f224-4326-b1ab-d73550e044bb",
                "message": "The response is not in a JSON format.",
                "innerError": "Cannot read server response."
            }
        }
    }

     

    The interesting part is that the error appears randomly. Sometimes the flow finishes in success but it is not stable and the above mentioned error stops the flow and I still am not able to understand what is triggering the issue.

     

    Any advice would be highly appreciated.


    Best Regards,
    Dzhuneyt

  • autoperfect93 Profile Picture autoperfect93 96
    Posted 07 May 2024 at 18:19:10
    CSV To New Excel Table

    Hi @takolota ,

     

    Thank you so much! I have added 30 seconds before the "Get file content" action in order to give some time for the excel file to be updated with the data.

     

    autoperfect93_0-1715105830188.png

  • takolota1 Profile Picture takolota1 4,750
    Posted 06 May 2024 at 18:42:39
    CSV To New Excel Table

    @autoperfect93 Because of the AD HTTP action all those steps must happen in OneDrive. Then the SharePoint Create file action will recreate the file in SharePoint to get it over there. By the time it re-reads the content, the new rows from the CSV should be there. If they are not, try adding a delay before retrieving the file contents of the newly updated Excel file & see if that helps. It reads the contents of the file at that moment, not back when the file was empty.

  • autoperfect93 Profile Picture autoperfect93 96
    Posted 06 May 2024 at 18:00:11
    CSV To New Excel Table

    Indeed. I did put the SharePoint URL but I did some research and noticed this afterwards.

     

    The only issue left is the question "How to take the content of the file in OneDrive for Business to SharePoint"

     

    I think this is due to the action Get file content retrieving the empty excel file created in the "Create file blank Excel"

     

    autoperfect93_0-1715018397989.png

     

     

  • takolota1 Profile Picture takolota1 4,750
    Posted 06 May 2024 at 17:56:32
    CSV To New Excel Table

    @autoperfect93 I think you input a SharePoint url instead of the MS Graph url as listed in the instructions

    AD-URL.png

  • autoperfect93 Profile Picture autoperfect93 96
    Posted 06 May 2024 at 15:57:40
    CSV To New Excel Table

    Hi @takolota ,

     

    I have tested the CSV_To_Excel_V1.1 SP Flows. It works but the file I get in SharePoint is empty.


    I think this is due to the action Get file content retrieving the empty excel file created in the "Create file blank Excel"

     

    autoperfect93_0-1715017562063.png

     

    So the step "Create file" actually creates an empty excel file in SharePoint. 

     

  • takolota1 Profile Picture takolota1 4,750
    Posted 05 May 2024 at 21:43:30
    CSV To New Excel Table

    Version 1.1 SP

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