Skip to content


Modifying Field to be "Text" to Maintain Leading Zeroes

John Ellis asked 2 years ago
A map has a .txt file, as its data source, with the query shown below.
The field [File2].[F139] loses its leading zeroes, upon import.  You see this, upon previewing the data source in the map.  But, if you open the file, the leading zero is indeed there.
So, how do I modify the field in this query to be a text field, so that any leading zeroes will not be dropped?
Thank you!
SELECT [File2].[F4], [File2].[F6], [File2].[F7], [File2].[F8], [File2].[F9],  
[File2].[F10], [File2].[F13], [File2].[F14], [File2].[F45], [File2].[F49],  
[File2].[F63], [File2].[F64], [File2].[F68], [File2].[F133],
[File2].[F149], [File2].[F165]
FROM [APInvoice.txt] [File2]
WHERE [File2].[F165] IS NOT NULL
Patrick Roth Staff answered 2 years ago
You don’t modify the query, you modify the schema using the “create schema” button and tell the Text ODBC driver that the field is a char field and not the (apparent) integer value that it assumes the field is.  Once you do that, the field should read as any other string and not lose leading zeroes.
Even though you aren’t running into this – the issue is similar so I’ll throw it out there for the benefit of the community.
Ran across similar issue yesterday where the user was integrating GP GL Accounts.
Their structure as 1234.560 using a decimal instead of the very typical – symbol.
When the data was previewed, instead it showed as 1234.56 vs 1234.560 as they expected.
In this case, the Text ODBC driver assumed these were currency amounts and so set the type to currency and the 3 digit got truncated.
So again here we set the field to char I the schema and was fine.  While John’s issue is very common, I don’t recall running into this dropping the last digit before.

If you would like to submit an answer or comment, please sign in to the eOne portal.