Unanswered

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

(0) Share

Report

Posted on by D0uttful

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

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.

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.

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

AlexEncodian 4,409 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
D0uttful 4 on at

Like (0)

Report

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!!

Was this reply helpful? Yes No

D0uttful

4 on at

Like (0)

Report

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 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
7/1/2023 0:00	7/2/2023 0:00	TRUE		voice	c49cdbc9-a4ca-4b31-8458-a2bcba36617e	IT Corporate Support	32	19	0.59375	13	0.40625	638958.2	0.34375	0.8	719566.4	627131.8	283339.4	26625	342391.1	1
7/1/2023 0:00	7/2/2023 0:00	TRUE		voice	0c97fd2a-9f0a-4e14-850c-743affa9617e	IT MIDS	4	2	0.5	2	0.5	289990.5	0.25	0.8	357182	1083752	290097		793655
7/1/2023 0:00	7/2/2023 0:00	TRUE		voice	4d010029-2331-4a70-81a2-56be591f6402	IT Rx Support	58	55	0.948275862	3	0.051724138	135047.2	0.413793103	0.8	137290.6	3250597	294373.6		2958346
7/1/2023 0:00	7/2/2023 0:00	TRUE		voice	8668b841-df1c-4d76-9abc-0608476e24eb	IT Store Support	633	345	0.545023697	288	0.454976303	685148.4	0.154818325	0.8	751551	590078.5	303517.1	88447.5	286368.4	4	9

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\"

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.

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: