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.
How can I do this in Toad?
Thank you in advance.