Importing zip codes from Excel with leading zeros

[savesara]

I've searched through the archives and have found a number of discussions about EXPORTING zip codes with leading zeros from FM6, but none on importing. How do you import zip code data that is formatted in Excel 2001 as "Special-Zip" and retain the zero in FM?

Thanks,

Frank Lennon

[CobaltSky]

In general, the answer is to import the zip data as text rather than number data. Text fields do not 'interpret' the incoming information - leading zeros included, so that will resolve the FileMaker end of the equation. As long as you can get Excel to output the leading zeros, a FileMaker text field will receive them.

Alternatively, you would be able to bring the data in and then restore the zeros afterwards using a replace with calculated result option, and a formula along the lines of:

Right(10^(n - Length(ZipCode)), (n - Length(ZipCode))) & ZipCode

where 'n' is the desired length of the zip codes, including the leading zeros. This formula will add zeros as required, but will not truncate a zip code if the existing data is of n or greater length. Moreover a 'Replace Contents' procedure using such a formula will be effective regardless of whether the target field is defined as a text or number field.