CSV to Excel Template

[rashantha]

Hello,

I have data that comes from PowerApps to a flow, I can take that data and create a CVS but I am trying to get that data into a XLSX Template that has a static table.

At the moment I am using apply to all and it add items by row to the XLSX table and it takes 17 minutes to add 500 records.

Isn't there an easy way to get that CVS file and add that data to the XLSX file?

Maybe I am overthinking this.

BTW I can't recreate the final file because it's used in a Label App (NiceLabel) where the link breaks if you create a new file even with the same name.

[SudeepGhatakNZ]

@rashantha ,

I don't believe you can do this natively.

You can use the Encodian 'Convert Excel' to do this - https://support.encodian.com/hc/en-gb/articles/360011804178-Convert-Excel

Example here: https://blog.encodian.com/2020/09/convert-excel-and-csv-files-in-power-automate/

Alternatively, you could check PAD (Power Automate Desktop), which does this natively.

Convert a CSV file into an Excel spreadsheet - Power Automate | Microsoft Learn

[takolota]

@rashantha @SudeepGhatakNZ 

If you can export the data out of the Power App as stringified JSON, then you can work that JSON into this Excel batch update & create template:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

If you definitely need to go from a CSV to Excel, then you can try modifying this CSV to New Excel template:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1826096

[rashantha]

Thanks for your reply. I did start this process win PAD but it wasn't very consistent, there was some SQL in it as well. But I think the snag was with OneDrive managing changes, deletion to rows etc. (delays).

[rashantha]

Thank you so much for sharing. I have looked Paulie78s video and I was wondering if you could help me with this part. I have the string that was passed from PApps to Automate and then I was able to get it in this format:

[
    {
        "Box_Num": "03315",
        "Description": "Vanart creme rinse pink (enjuague) 25 oz",
        "Exp_Date": "",
        "Item_No": "81975",
        "PO_No": "20530",
        "Qty": 64,
        "Rec_Date": "",
        "Rec_Qty": "",
        "Vendor": "BLACK & PEACH RETAIL LLC",
        "Vendor_Code": "102379",
        "Whs_Loc": "E0914"
    },
    {
        "Box_Num": "71007",
        "Description": "IMUSA CHEF KNIFE 8\"",
        "Exp_Date": "",
        "Item_No": "92884",
        "PO_No": "20916",
        "Qty": 246,
        "Rec_Date": "",
        "Rec_Qty": "",
        "Vendor": "IMUSA USA, LLC",
        "Vendor_Code": "109510",
        "Whs_Loc": "I0524"
    },

But now I have to get it to:

[

 [ row 1],

 [row 2],

[row 3],

]

What is the function I would use in compose?

[takolota]

@rashantha 

Well you could pick apart the last few actions in the CSV to New Excel template & the expressions used there. But if you already have it outputting JSON, then you can just plug it in to the batch Excel template Generate Update Data action From field & use the batch create there. It will post atleast 1000 rows every 30 seconds.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...