In 2014, we changed our CSV (comma separated value) file to a tab delimited file. When we added the currency symbol feature in 2014 (e.g. dollar sign $, etc), we discovered that Excel does not handle currency symbols very well in a standard CSV file. Also, Excel does not handle foreign characters (e.g. umlaut, tilde, Russian, Korean, etc) very well in a CSV file which was also an issue for some clients. These currency and foreign characters are known as UTF-16 characters versus ASCII or UTF-8 characters. Our research showed that we needed to change to a tab-delimited file format and add some special bytes at the beginning of the CSV file known as BOM (byte order marker) so that Excel would know how to import the data as UTF-16.
If you need to have a true CSV file (with commas), please perform the following instructions:
- Download the applicant data from SmarterSelect.
- Open Excel with a new worksheet.
- In the Excel toolbar, select File/Import
- Select the radio button for CSV file, then click the Import button.
- Choose the CSV file that you downloaded from SmarterSelect.
- In the Text Import Wizard (page 1 of 3), keep the default settings and click Next.
- In the Text Import Wizard (page 2 of 3), keep the default settings and click Next.
- In the Text Import Wizard (page 3 of 3), for column 1 (which is selected by default), change the Column Data Format from General to Text then click Finish.
- In Import Data, click to import to the Existing Sheet, then click OK.
- The tab delimited file has now been imported.
- Then, in the toolbar, select File/Save As, and select file format as CSV and click Save.
- You will now have a true comma separated values file with the leading zeros preserved for the AppID.
We are sorry that you have to Import, then Save As to get a true CSV, but we have to work around some of the problems with Excel and this seems to be the best way to preserve all UTF-16 characters and preserve the AppID leading zeros.