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
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
Solved! Go to Solution.
@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.
Use below formula
IF REGEX_Match(ToString([Number]), "[\d.-E+]+")
THEN ToString(ToNumber(ToString([Number])))
ELSE ToString([Number]) ENDIF
Hope this helps : )
@atcodedog05 Worked like a charm, I can't thank you enough for your patience and expertise!!
Happy to help : ) @George_Fischetti
Cheers and have a nice day!
@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
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.
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!!