Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Building Flows / Extract data from an C...
Power Automate - Building Flows
Unanswered

Extract data from an CSV email attachment and paste it into an existing excel file

(0) ShareShare
ReportReport
Posted on by D0uttful 4

I've been at this for entirely too long, so apologizes if this is not clear. 

 

To start, I currently do a lot of manual report running in my role. It takes time, but it also delays information getting to our team. They have to wait for me to sign on and perform my many tasks. I want to automate these processes, I know it's possible I'm having issues with execution. 

 

Desired Automation Chain of Events: 

1. Report is auto exported from application to an CSV file

2. File and emailed via attachment to an email account

3. When the email is received, the data from the attachment is copied and pasted into a permanent excel worksheet. 

 

First I followed Update Excel Spreadsheet from email attachment - Power Platform Community (microsoft.com),  However, I couldn't get around "Get Rows" that this example calls for. I tried MULTIPLE different configurations with create table, and get list of rows and those connectors, but it still failed with "Source not found errors" when trying to create the table. 

 

In research I also found some mentions that Create File and Create Table don't work well together on the same flow. So I separated the two flows

 

D0uttful_2-1688002463695.png

Even with the two separate flows, I could not get create table to cooperate. 

 

After searching some more I found this post Solved: Re: Extract data from email attachment and add to ... - Power Platform Community (microsoft.com) that references using Office Scripts with Power Automate. 

 

I found a FANTASTIC tutorial Move data between workbooks (Power Automate+Office Scripts) (exceloffthegrid.com) But, when I run through these steps I still get an error. 

D0uttful_0-1688001912035.png

prod-72.westus.logic.azure.com/workflows/33d40e7464ef43a39c454f02d33ce9bc/runs/08585136059000279093668628861CU153/contents/TriggerOutputs?api-version=2017-07-01&se=2023-06-29T05%3A00%3A00.0000000Z&sp=%2Fruns%2F08585136059000279093668628861CU153%2Fcontents%2FTriggerOutputs%2Fread&sv=1.0&sig=TpbTbNZQqbmAjxBMLeD064X7bjbGjl2qwtN0hnnXfD8

 

I am beyond frustrated at this point. So if someone could help me I would SUPER appreciate it. 

 

Some additional info on my data.

The columns in the permanent excel are the same as the columns in the exported CSV files. My application only allows for data export in CSV or PDF formats. I tried to have the created file save with the XLSX extension in the name to overwrite the default file when creating the SharePoint file, but the file does not open when saved that way. 

 

 

  • D0uttful Profile Picture
    D0uttful 4 on at
    Re: Extract data from an CSV email attachment and paste it into an existing excel file

    Upon further research, I was able to find a tutorial that that allowed me to save the attachment from a CSV to an excel file using Office Scripts. (Convert CSV Files to Excel (xlsx format) in Power Automate - YouTube) 

     

    My problem now is that the table generated via Office Scripts is not formatted as expected. 

     

    The source data from the CSV report export is

    Interval StartInterval EndInterval CompleteFiltersMedia TypeQueue IdQueue NameOfferAnswerAnswer %AbandonAbandon %ASAService Level %Service Level Target %Avg WaitAvg HandleAvg TalkAvg HoldAvg ACWHoldTransfer
    7/1/2023 0:007/2/2023 0:00TRUE voicec49cdbc9-a4ca-4b31-8458-a2bcba36617eIT Corporate Support32190.59375130.40625638958.20.343750.8719566.4627131.8283339.426625342391.11 
    7/1/2023 0:007/2/2023 0:00TRUE voice0c97fd2a-9f0a-4e14-850c-743affa9617eIT MIDS420.520.5289990.50.250.83571821083752290097 793655  
    7/1/2023 0:007/2/2023 0:00TRUE voice4d010029-2331-4a70-81a2-56be591f6402IT Rx Support58550.94827586230.051724138135047.20.4137931030.8137290.63250597294373.6 2958346  
    7/1/2023 0:007/2/2023 0:00TRUE voice8668b841-df1c-4d76-9abc-0608476e24ebIT Store Support6333450.5450236972880.454976303685148.40.1548183250.8751551590078.5303517.188447.5286368.449

     

    But after running the script the table generated is this:

    ["\"Interval Start\"\"Interval End\"\"Interval Complete\"\"Filters\"\"Media Type\"\"Queue Id\"\"Queue Name\"\"Offer\"\"Answer\"\"Answer %\"\"Abandon\"\"Abandon %\"\"ASA\"\"Service Level %\"\"Service Level Target %\"\"Avg Wait\"\"Avg Handle\"\"Avg Talk\"\"Avg Hold\"\"Avg ACW\"\"Hold\"\"Transfer\"\r""\"7/1/23 12:00 AM\"

                           

    D0uttful_0-1688320380595.png

     

    I'm not sure what I've done wrong, as this did not occur in the tutorial. 

    The outputs of my information in the flow also look correct. 

    D0uttful_1-1688320546022.png

     

    Below is the code for my Office Script. It creates an XLSX using the saved data from the CSV attachment. 

    CSVtoXLSX Script: 

    function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
      let selectedSheet = workbook.getActiveWorksheet();
      const alllines = lineCSV;
      let counter = 1;
      for (let line of alllines) {
        if (line.includes(",")) {
          let items = line.split(",");
          selectedSheet.getRange("A" + counter + ":W" + counter).setValues([[items[0], items[1], items[2], items[3], items[4], items[5], items[6], items[7], items[8], items[9], items[10], items[11], items[12], items[13], items[14], items[15], items[16], items[17], items[18], items[19], items[20], items[21], items[22]
          ]]);
          counter++;
        }
      }
      workbook.addTable(selectedSheet.getRange("A1:W" + counter), true).setName("Table1");
    }
     
    Here's screenshots of the full flow: 
    D0uttful_2-1688320813576.png

    D0uttful_3-1688320875277.png

    D0uttful_4-1688320907223.png

    Thanks to anyone who is willing to look into this or provide suggestions!!! 

     

    Empty Workbook.xlsx
  • D0uttful Profile Picture
    D0uttful 4 on at
    Re: Extract data from an CSV email attachment and paste it into an existing excel file

    When the CSV attachment arrives, I am trying to insert the data into an existing xlsx file. Then appending the new data to an existing table/sheet in that excel workbook. 

     

    The data in the xlsx is formatted as a table. However, I've tried running flow using data with both plain excel format and table content structure with no luck. My reporting isn't picky, so if having it formatted as a table is the problem for the automation, then I can remove that without issues. 

     

    Thanks so much!! 

  • AlexEncodian Profile Picture
    AlexEncodian 4,409 Super User 2025 Season 1 on at
    Re: Extract data from an CSV email attachment and paste it into an existing excel file

    @D0uttful 

    Can I check your exact requirements please. 

    When a CSV is received via email, are you simply converting it to a new xlsx file or are you trying to insert the data in the CSV into an existing xlsx file (ie. that already has data and therefore you are appending further data)? 

    And when you say data is copied and pasted into the xlsx, are you simply adding it to a new tab or are you trying to insert the data by appending it to an existing table for example?

    And is the data in the xlsx file in a table content structure or in a plain excel format?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Now Available: 2025 Release Wave 2
Welcome to the Power Platform Communities

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 1

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans