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

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

sheet.xlsx

Datasets

Settings

Help

Accepted answers

AngelosPachis

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

Screenshot 2021-05-13 134022.jpg

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

Notes_from_danilang.yxzp

All comments

atcodedog05

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.

TheOC

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

AngelosPachis

Hi @TheOC ,

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

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

Screenshot 2021-05-13 134022.jpg

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

Notes_from_danilang.yxzp

TheOC

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

atcodedog05

Oh my God 😮 This is just mind-blowing 😮

Is there anything that @danilang cant do ?

Thanks for sharing this with us @AngelosPachis

JokeFun

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.

JokeFun

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

JokeFun

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703