Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Fixing E+11 notation from CSV/Excel

George_Fischetti
8 - Asteroid

Hi folks, I'm using csv files as my source, there is 40 of them.  when I process each file I have my bank account number showing values such as the figure below

 

George_Fischetti_0-1634145236918.png

 

At the source, this field is coming in as a V_WString, I then follow with a Data Cleaning tool and Select tool.  It looks like the format changes to V_String after the Select tool.

 

How would I fix this so that the entire account number is shown?

 

Thanks!  George

 

24 REPLIES 24
atcodedog05
21 - Polaris

Hi @George_Fischetti 

 

Dont use select and convert it to string. It should still work without it.

George_Fischetti
8 - Asteroid

@atcodedog05 This is what it looks like...........the grid below is the metadata for the Input Data tool, the formula is set by default.  your formula is showing the error.

 

George_Fischetti_0-1634217002293.png

 

atcodedog05
21 - Polaris

Hi @George_Fischetti 

 

Use below formula

 

IF REGEX_Match(ToString([Number]), "[\d.-E+]+") 
THEN ToString(ToNumber(ToString([Number])))  
ELSE ToString([Number]) ENDIF

 

Hope this helps : )

 

George_Fischetti
8 - Asteroid

@atcodedog05 Worked like a charm, I can't thank you enough for your patience and expertise!!

atcodedog05
21 - Polaris

Happy to help : ) @George_Fischetti 

Cheers and have a nice day!

George_Fischetti
8 - Asteroid

@atcodedog05 I found one more issue, but on a different field.   For some reason the Imput tool is not recognizing that the Contract Number can have a suffix.   The field is just showing numeric values.   I've tried variations on the formula you showed here but having no luck.   This is what the data should look like....The suffix can be anything from 1-4 alpha characters.   

 

Appreciate any help you can provide.   Thanks!!

 

Should Be       Showing As
00003456IV    00003456
231313RB          231313
5464569FT         546469
528698               528698

atcodedog05
21 - Polaris

Hi @George_Fischetti 

 

Strange it seems to be working for me.

 

Workflow:

atcodedog05_0-1634291386546.png

 

Hope this helps : )

George_Fischetti
8 - Asteroid

Thank you again, I found my issue.  I was using an Auto Field tool to adjust spacing and cleanup white space since my files are very large, was trying not to store quite a large file.

 

Appreciate your time, quite the learning experience.

George_Fischetti
8 - Asteroid

Hi @atcodedog05 - it appears that my assessment is incorrect.  While the REGEX formula is working, it is not reflecting the proper data in every instance as I found when doing some analysis, I didn't realize some of these account numbers were wrong until I was matching up with source systems.

 

In the figure you show, record 1,2,3 is suppressing the true account number by inserting 7 or 8 extra zeros.  Records 4 to 8 are showing properly.  Is this just something that is inherent in CSV files where the results are inconsistent?  Can we make any adjustments to the REGEX?

 

Appreciate your help!!

atcodedog05
21 - Polaris

Hi @George_Fischetti 

 

Can you share a sample excel file with the data.

Labels