Answered

Where to begin - extract Excel data

Like (0) Share

Report

Posted on 17 Mar 2021 04:13:05 by AK-47

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

I have the same question (0)

All responses (12)

Answers (1)

v-duann-msft on 19 Mar 2021 at 07:41:54

Like (0)

Report

Re: Where to begin - extract Excel data

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.
Best regards,
Anna

Was this reply helpful? Yes No
AK-47 70 on 18 Mar 2021 at 09:51:50

Like (0)

Report

Re: Where to begin - extract Excel data

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

Was this reply helpful? Yes No
v-duann-msft on 18 Mar 2021 at 08:21:16

Like (0)

Report

Re: Where to begin - extract Excel data

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'])

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

Hope it helps.

Thanks
Anna

Was this reply helpful? Yes No
AK-47 70 on 18 Mar 2021 at 08:02:07

Like (0)

Report

Re: Where to begin - extract Excel data

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

Was this reply helpful? Yes No
v-duann-msft on 18 Mar 2021 at 07:58:10

Like (0)

Report

Re: Where to begin - extract Excel data

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

Was this reply helpful? Yes No
AK-47 70 on 18 Mar 2021 at 07:53:59

Like (0)

Report

Re: Where to begin - extract Excel data

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

Was this reply helpful? Yes No
v-duann-msft on 18 Mar 2021 at 07:16:50

Like (0)

Report

Re: Where to begin - extract Excel data

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

Was this reply helpful? Yes No
AK-47 70 on 17 Mar 2021 at 09:31:04

Like (0)

Report

Re: Where to begin - extract Excel data

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?

Was this reply helpful? Yes No
chrissahagun 197 on 17 Mar 2021 at 08:44:59

Like (0)

Report

Re: Where to begin - extract Excel data

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

Was this reply helpful? Yes No
AK-47 70 on 17 Mar 2021 at 08:38:25

Like (0)

Report

Re: Where to begin - extract Excel data

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

Was this reply helpful? Yes No