When I export data from QuickBase (in CSV format) to MS Excel, some data appears incorrect or incorrectly formatted. Why?
[The Quick Base Knowledge Base is your library of frequently-asked questions that help you better customize your apps to solve your business problems.]
Most Quick Base data will export seamlessly into Microsoft Excel. However, there are some cases when data is lost or displayed with the incorrect formatting.
One example is exporting a 5-digit zip code stored in a text field. When displayed in Excel, any leading zeroes in the zip code are lost. Similar problems can happen when exporting ID numbers, etc.
When exporting to Excel, Quick Base uses the standard CSV (comma-separated value) file format. Unfortunately, this format contains no information about the type of each column. When importing the data, Excel guesses at the type of the data each column. The zip code problem happens because Excel guesses that a cell containing only numbers should be formatted as a number and it drops the leading zeroes.
Unfortunately, Excel does not allow a column's format to be specified when importing a CSV format file.
If you are running into this problem, there are several alternative workarounds available:
(1) After opening the CSV file, change the column's format in Excel to the correct type. While this won't restored lost leading zeroes, it can be effective in correcting problems with date formatting, etc.
OR
(2) Open the file using Excel's Text Import Wizard. In Excel 2007, click on the Data tab and then click on "From Text" under "Get External Data". In earlier versions of Excel, open the Data menu then click on Import External Data > Import Data... Select your CSV file in the resulting file dialog, which will bring the Text Import Wizard. Select a "delimited" data type and then select "comma" as the delimiter. You will then have the opportunity to specify the type of each column. To correct the lost leading zero problem, select "text" as the type of your zip code field. Unfortunately, this solution will not work if cells in your export contain multi-line data.
OR
(3) Edit the CSV file "by hand". This is only needed in extreme cases. One surefire way to prevent Excel from modifying your data is to surround it with ="". For example, 02134 becomes ="02134".