I am connecting to a large, pipe delimited text file (23 Million records) and the most important field is SSN. US TINs are normally 9 digit (123456789). However, some of the SSNs in my file have a leading zero (012345678). The connection wizard is interpreting this field, the SSN, to be numeric and is dropping the leading zero. Now I have (12345678) only 8 digits and the correct SSN number has been altered.
My question is, how can I force the connection wizard to interpret this field as a character to prevent losing the leading zeroes?
I was thinking about adding a record in the text file to give a fake 000000000 TIN at the beginning but I don't know how to open a file that large and am not sure if it would work. I tried this on a smaller file, with the first record edited to AAAAAAAAAA and this did not work.
MS Access allows you to custom define the field type using a custom import specifications.
Thank you for your attempts, I have not found a way yet. In Fact, even if I use 0123456789 as all the SSN numbers, it still truncates an interprets as numeric.
A Co-worker found a solution! Basically he uses MS Access engine functions in the query to force the formatting.
Here is his solution: Brilliant
I modified '000000000000000' to nine digits - '000000000'
Ross,
Here’s a way I found to do it. This obviously does nothing to preserve what the data looked like when it came in, but it definitely allows you to pad zeros onto it.
SELECT format(str(dda_acct_no),'000000000000000') as formatted_dda FROM FDW DDA Joint Accts - 20190831.txt;
It’s a little weird because you’re using Toad, but behind the scenes it’s using MS ACCESS’s text engine, so searching for how to do something in Access often works.
STR appears to be the command to convert a number to a string.
Then use format to pad it with zeros. Basically, you just put what you’d want it to look like if you had nothing.