I had a spreadsheet with tens of thousands of mailing addresses. One of the columns was  the ZIP code for each address row.  Some of those ZIP codes were in the ZIP + 4 format (12345-6789) and others were not.  Those others were 3, 4 or 5 digit numbers.  When the data was put into the spreadsheet, Excel removed the leading zeroes from some ZIP codes (like “00345” truncated to “345”).

I used the Import functionality in SQL Server Management Studio 2005 to put the data from the Excel spreadsheet into a table.  Without modifying the spreadsheet data the import would place a NULL into each row that had a 3,4 or 5 digit ZIP code, while keeping the ZIP + 4 codes correctly.

To get both “5-Digit” and “Zip Plus Four” ZIP codes into the same database column I needed to make Excel see them as text. To do this I made a new column next to my ZIP Codes with this style of formula:                =TEXT(B2,”00000″)

This causes the data to have at least 5 digits and leading zeroes if less than five  digits. Next I copied this new column and then used “Paste Special” with the “Values” option to replace my original ZIP code column. Saving the spreadsheet and executing a SQL Server import of the XLS file, all ZIP codes were then correct.

I also found that using the “Format Cells” functionality with the Special “Zip Code” setting only changes how the data appears. It does not change the underlying data. For example “345” shows as “00345” but if you look at the actual data, it remains unchanged. So just doing Excel formating did not work when importing in to a SQL Server database.