Save the file as a Text File (.txt), rather than an Excel file (.xls). This will preserve the leading Zeros.
then re-import the zip codes if that's easiest, always ensuring to backup your data and making sure you don't overwrite/erase anything.
otherwise, if the aforementioned is not convenient, you can find all the zips with only 4 digits, and do a replace all with a calculation of "0" & "yourzipfield".
I don't think FM PRo 11 will let me use either TXT or CSV files as Import files. (They are greyed out).
Can anyone give me an example of how I might create a calculation that would examine the zip and if it is only 4 digits, add a zero to the left side? I would like to make a script step to do this before making a backup copy and doing the update of the file. Otherwise maybe it could be done as a calculated field in the database. ???
You can import from text, csv and many other file formats.
When and where do you see them as "greyed out"?
You could use Replace Field Contents with this calcluation expression immediately after import to update the zipcodes with a leading zero where needed:
Right ( "0" & zipcodefield ; 5 )
That ought to work, but I'll have to do a Find first ( 4 digit zip codes) to be sure it doesn't put a zero in front of the 5 digit zip codes - Right?
No find needed. When you have a 5 digit zip, the function returns the right 5 characters and this strips off the extra zero automatically.
This is a standard number formatting expression for adding leading zeroes to a number in a number field.
Come to think of it, you can use this expression as an auto-enter calculation for your field. If you can enable auto-enter options during import, the leading zero will be added back in automatically as part of the import. (But check to make sure that this does not cause a problem with auto-enter operations being performed on other fields that you don't want to take place during the import.)
-Sorry to be so dense.
When I added the step to my script (Replace Field Contents) I chose the calculated option, and entered Right("0"&Zip;5). It highlighted Zip (which is the name of my zip field, and said it couldn't find the table. ??? Zip is a field in my MemberInfo table.
In a script, you need a table occurrence name to go with the field name. Find the field in the list above your edit box and add it to your calculaiton by double clicking it. You'll get something like this if your cursor is in the right place:
Right ( "0" & YourTable::zip ; 5 )
Success! Thank you for all your help!! Good Job!