Understanding CSV file structure for Outlook
The acronym for CSV is “Comma Separated Values” representing a text file containing multiple fields in a single record/row where each field is separated by a comma. The other major element that is important to understand is the <field qualifier> character which is used to surround the value of a given field when the field value contains line breaks as may be the case for fields such as a street address or notes. The most common field qualifier character is the double-quote mark.
Outlook requires that any CSV file used as an import data source utilize the comma as the field delimiter character, otherwise all fields will appear to be contained in a the first field in a row. This is true for all versions of Outlook.
However, a quirk that causes a lot of aggravation for international users is Microsoft Excel’s use of the “list separator” character defined in the Windows regional settings as its field delimiter character which for many countries is the semi-colon character instead of the comma. In short, a CSV file created by Excel when this situation exists won’t be importable by Microsoft Outlook. Also, if CSV file is opened by Microsoft Excel which uses the comma as the field delimiter but the regional <list separator> character is something else such as a semi-colon, Excel will invoke an initial series of windows asking you to define the specifics of the text file being opened.
A CSV file is a native format for MS Excel and in a perfect world where the field delimiter in the text file is the same as defined as the list separator character, you will never be presented with any questions about the file under normal circumstances. In essence, a CSV file that uses a different field delimiter then that defined in the Regional Settings is in fact a “custom delimited” file – not a CSV file regardless of the file’s extension.
For international users where this is an issue and an Excel worksheet needs to be saved as a CSV file, the workaround is to temporarily change the regional settings list separator character to a comma, save the worksheet as a CSV file and then reset the regional setting to its normal value.
If on the other hand, you have a CSV file where the field delimiter is a semi-colon (or some other character) – then the quickest way to deal with that is to change the regional setting list separator character to reflect the character used in the CSV file, open the CSV file in Excel and save the worksheet. Once done, return the regional separator character to its original value (a comma), open the newly saved Excel worksheet and then resave it as a CSV file. The newly saved CSV file will now have a comma as its field delimiter character.
Another important element to any text file, not just CSV files, is the <field qualifier> character for any fields that contain a multi-line value where multi-line means that there are linebreaks contained inside the field value itself. If these fields are not “qualified”, a row that should be considered as a single row of multiple fields will be interpreted as multiple rows causing unpredictable results when the file is imported. Also, if the value of a field contains field qualifier character such as a double-quote, the field itself must be completely qualified (a double-quote at the beginning and end of the field value in addition to “escaping” the character within the field value by ensuring that it is preceded by itself – in other words, two double-quote marks must be contained within the field value otherwise it may not be translated correctly.
There is no universal standard for reading and creating a CSV file, how well any given program reads or creates a CSV file that contains multi-line field value is totally dependant on the program in use. There are many cases where a program reading a CSV file does not support the use of multi-line field values which is a deficiency of the program reading the file and not the program that created it.
If working with non-standard CSV files on a constant basis, all ContactGenie import programs provide the capability to import custom-delimited text files.
Category: Understanding Outlook
