Excel Formatting Breaking Alteryx Workflow

[Data_User88]

Hello fellow Alteryx users,

Our internal data warehouse (DW) exports business reports into a CSV file before sending it Alteryx for processing. The CSV file contains 500,000 plus rows of data. The file will export the product number in the format shown below (see table). A manual intervention is performed to convert the contents of the product number in the table below to a 'Number', using the following formula - 

=IF(LEN(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))>7,LEFT(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0),4)&"E"&IF(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0))-4<=9,0,"")&(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"#"))-4),IF(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),LEFT(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0),4)&"E"&IF(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0))-4<=9,0,"")&(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"#"))-4),INDIRECT(ADDRESS(ROW(),COLUMN()-1))))

Product No

4.00E+03

7.01E+03

7.08E+03

7.10E+03

7.14E+03

7.18E+03

7.20E+03

On saving and closing the CSV file, the changes made are not retained and therefore the contents of the CSV file returns to its original state before the manual intervention. With that said, I would be grateful for some advice on how I can complete the manual excel intervention in alteryx.  Any suggestions would be greatly appreciated. Thanks in Advance. 

[danilang]

Hit @Data_User88

Is there data lost in the conversion to scientific notation?  i.e. are your product numbers more than 3 digits?  If not you can use the following in a formula tool

Take the first 4 characters from the input string, convert to a number and multiply by 100

Here's a workflow that shows it in use.  It read from a csv called Product.csv in the same directory as the workflow

Dan

[Data_User88]

Many thanks @Danilang for taking the time to reply to my post. Yes, the data is lost as a result of scientific notation. Our product numbers are seven digits in length and typically follow the following format N,N,N,A,,N,N. (N= Numeric & A = Letters). Nearly all of our letters begin with a 7 but that is not strictly true as presented in the example above . 

[danilang · Accepted solution]

Hi @Data_User88

If the data is lost in the conversion, how do map what you get to the correct product IDS?  Can you post some sample data, before and after.   Just a few conversions is enough.

Dan

[Data_User88]

Thanks @Danilang for your help. Its greatly appreciated.