cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rashantha
Helper I
Helper I

CSV to Excel Template

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.

5 REPLIES 5
SudeepGhatakNZ
Super User
Super User

@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

If my suggestion helped you, please give it a Thumbs up and mark it as a Solution so that it can benefit others in the community.
takolota
Community Champion
Community Champion

@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

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).

 

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?

@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-...

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,300)