topic Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell? in Alteryx Designer Desktop Discussions

Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

TheOC — Thu, 13 May 2021 12:09:28 GMT

Hey,
I'm just looking for a little help regarding an excel import. I have the following file:

Field 3 is populated by a calculation, as you can see, 1+2. Is there a way to bring that calculation through to Alteryx, rather than just the value of the cell?
The data is brought into Alteryx as such:

I've attached the spreadsheet if it helps investigate.
Cheers!
TheOC

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

atcodedog05 — Thu, 13 May 2021 12:20:39 GMT

Hi @TheOC

I dont think its possible. Most of the applications when it reads data from the excel file it can read only the data but not the underlying formula.

But lets see if anyone has a hack on how to do this.

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

TheOC — Thu, 13 May 2021 12:22:18 GMT

Hey @atcodedog05, long time no see!

That was my assumption, but ran into an issue a couple days ago which may require it - I guarantee someone has a magic method 😁

Cheers!
TheOC

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

AngelosPachis — Sun, 17 Sep 2023 01:56:25 GMT

Hi @TheOC ,

As a matter of fact, I believe there is a way.

In a post yesterday, @danilang showed how you can read an Excel file as a .zip file and then parse the comments of some cells. I've never seen anything like that before and as he said, "parsing the various xml files within, you can find out all sorts of secrets".

So I tried something similar, using exactly the same inputs; now, I'm no XML master but in just a few minutes I managed to play a bit with the xml tool and I was able to find this

This is the formula I used in the Excel file, to calculate cell B2

So it's doable! Sorry this is not a perfect solution for you but hopefully it can show you the way. With a bit more delicate parsing, you should be able to get there.

Cheers,

Angelos

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

TheOC — Thu, 13 May 2021 12:52:03 GMT

oh man, thats an impressive solution!

Massive props to you and @danilang.

I think that should get me the rest of my way, thank you!!

Cheers,
TheOC

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

atcodedog05 — Thu, 13 May 2021 13:40:30 GMT

Oh my God 😮 This is just mind-blowing 😮

Is there anything that @danilang cant do ?

Thanks for sharing this with us @AngelosPachis

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

atcodedog05 — Thu, 13 May 2021 13:42:18 GMT

And i guess at the end of the day everything is just XML then 😅

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

TheOC — Thu, 13 May 2021 14:02:36 GMT

I think this is closer to the truth than we think 😅

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

danilang — Fri, 14 May 2021 01:09:03 GMT

Stop it!!

You guys are making me blush!!

Dan

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

JokeFun — Thu, 06 Apr 2023 10:25:39 GMT

Error: Input Data (22): Error reading "xl/worksheets/sheet1.xml": Record #1: Fields must be smaller than 16M

Any solution on this without manual change to the input file? I think there're too many columns there.

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

JokeFun — Fri, 07 Apr 2023 01:11:05 GMT

Now I have set the field length as 10000 and just had two columns in the file. And then another message: Error: XML Parse (24): unexpected end of input at Line:1 and Column:100001

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

JokeFun — Fri, 07 Apr 2023 01:31:16 GMT

I have a file with more than 10K rows where the field contains text string, as well as some rows with formulas like =-@ abcd.

The purpose is to identify rows with such formulas and replace by abcd (removing =-@).

However, I was seeing this error message.

Error: XML Parse (24): input ended before all started tags were ended; last tag started is 'row' at Line:1 and Column:100001

Then I set the field length to be 10000000000 something like that, even larger. Then it works.

Re: Alteryx Excel Import, Is there a way to bring through the calculation for the cell?

caltang — Sun, 17 Sep 2023 02:00:21 GMT

Hi @AngelosPachis

Sorry to revive an old thread from 2021 - but this is exactly what I need.

May I ask, what do these extract file boxes represent?

@TheOC - I wonder if this can be made into a Universal Tool that can be shared to the Community. What do you think?