cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AK-47
Helper I
Helper I

Where to begin - extract Excel data

Hi, I have an Excel spreadsheet with an active connector to a source data file which has many columns. This xlsx is the filtered to exclude certain rows. I need to extract 5 specific columns from that sheet, and export it to another csv file overwriting the original csv. (I hope that makes sense).

 

I'm not sure where to begin with this one. It seems that in order for the data connection to update the xlsx file would need to be opened so the data can be refreshed. Once that problem is solved, I can't find any hints on how to copy out those specific rows as csv data and overwrite without a prompt.

 

I would love to hear your ideas.

 

Thanks and regards

Andrew

1 ACCEPTED SOLUTION

Accepted Solutions
v-duann-msft
Community Support
Community Support

Hi @AK-47 

 

Thank you for posting.

 

According to your description, you would like to extract few columns from excel file and update to a CSV file. If any misunderstanding, please kindly let me know.

 

Since there is no related trigger existing in excel to check file changes. Thus, I will start the flow using schedule to run it every 1 minute.

 

Here is the flow for your reference:

v-duann-msft_0-1615965368677.png

 

Flow in details:

v-duann-msft_1-1615965368681.png

v-duann-msft_2-1615965368683.png

 

If currently you haven’t has a CSV file, we can first run flow to create a csv file as original version:

v-duann-msft_3-1615965368687.png

 

Hope the content above may help you.

 

Thanks

Anna

View solution in original post

12 REPLIES 12
chrissahagun
Resolver II
Resolver II

Maybe "Run Script" under Excel Online?  You might need to do some preps outside Flow though...

 

Call scripts from a manual Power Automate flow - Office Scripts | Microsoft Docs

v-duann-msft
Community Support
Community Support

Hi @AK-47 

 

Thank you for posting.

 

According to your description, you would like to extract few columns from excel file and update to a CSV file. If any misunderstanding, please kindly let me know.

 

Since there is no related trigger existing in excel to check file changes. Thus, I will start the flow using schedule to run it every 1 minute.

 

Here is the flow for your reference:

v-duann-msft_0-1615965368677.png

 

Flow in details:

v-duann-msft_1-1615965368681.png

v-duann-msft_2-1615965368683.png

 

If currently you haven’t has a CSV file, we can first run flow to create a csv file as original version:

v-duann-msft_3-1615965368687.png

 

Hope the content above may help you.

 

Thanks

Anna

View solution in original post

AK-47
Helper I
Helper I

This looks pretty close. Since the data being extracted comes from an external data connection would this approach update the file data before extraction?

chrissahagun
Resolver II
Resolver II

In my mind, it plays out like that 😁 but to be honest, I haven't tried this yet.

AK-47
Helper I
Helper I

I'm taking this approach for the trigger as this file is part of another flow which get's dropped into the library from an email attachment and is the source file of the Excel data connection. Automate is warning me that this "may" create an infinite loop. But I think with the condition to check the file name it should be ok. 

 

Your thoughts?

 

flow-trigger.jpg

 

Hi @AK-47 

 

Thank you for update.

 

If only warning pops up, it depends and should be no problem. Please feel free to test and if any issue, you can reply me again.

 

Please click Accept as Solution if my post is helpful to you. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

 

Best regards,

Anna

Thanks. The only issue is that it isn't seeing the file update. Unsure why.

 

AK-47_1-1616054026210.png

 

 

Hi @AK-47 

 

Please help to change condition to 'contains' and pay attention to capital words of the file name. It's case-sensitive.

 

Thanks

Anna

AK-47
Helper I
Helper I

Changed it to contains and confirmed site, collection, and filename are all correct but still the same result. 

Hi @AK-47 

 

Thanks for your attempts.

 

I tested and found the issue is caused by encoding.

 

If we use 'x-ms-file-name-encoded' as file name, the output of filename will be encoded as 'ZGFpbHlyZXBvcnQyMDIxLTAyLTI1MzMueGxzeA=='. Thus, we need it to decoded the file name as below:

 

base64ToString(triggerOutputs()?['headers/x-ms-file-name-encoded'])

v-duann-msft_2-1616055639695.png

 

After that, you will be able to filter file based on normal name.

v-duann-msft_3-1616055639698.png

 

Hope it helps.

 

Thanks

Anna

AK-47
Helper I
Helper I

That seems to have fixed the condition trigger thanks. The last failure now is that it can't overwrite the existing file.

 

AK-47_0-1616061103753.png

 

Hi @AK-47 

 

Thanks for reply. Good to hear it works for you.

 

To overwrite the former file, we need to use 'update file' action instead of others. If you use 'create file', it will get duplicate error definitely.

v-duann-msft_0-1616139635682.png

Best regards,

Anna

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Users online (18,867)