How does MassEffect read .csv and .xls data?
MassEffect uses the standard Microsoft data components to load data from XLS or CSV.
These components work great with Access MDB files or a SQL Server connection as the format of the columns are formally defined.
In an XLS or CSV file (and Excel), the data reader will look at the first few rows of data to determine the field type.
It then uses that field type to read the data.
In the case of a zip code, if the first 5 records are all 5-digit zip codes it will assume that the entire column is a numeric integer and then trim off the data that is supplied from the database.
We do not have control on this as it is done by the Microsoft component.
You can try to force the column to be seen as text and make sure that a 5+4 zip code or Canadian postal code is in the first 5 lines.