Repair Delimited-Field CSV Files
Rationale:
Delimited-field or CSV format is the most common supported file. Address Lists can have invalid CSV records.
It is important that you have a basic understanding of what CSV is and the few simple rules that are necessary for it to be valid.
You will need to know this when patching invalid delimited-field Address List records.
See Also: Delimited Field
Delimited-Field CSV Error Reporting
When selecting a file for EZ24x7 services, the records are counted. If all the records are the same length, this is assumed to be fixed-field, otherwise delimited-field and is checked for validity.
Any errors found are reported in a log file found in ‘C:\AES\log’ The name of the file will be either ‘csv_qualifier.log’ or ‘csv_no_qualifier.log’. If not sure which, look at the date and time.
CSV Error Log Sample Content
ERROR: CSV record=4 byte=61
6437,John Stafford,333 Bryant St.,Suite 250,"San Francisco," CA",94107
CSV Error Log Sample Explained
Errors are introduced in the log with
"ERROR: CSV record=row byte=offset"
followed by the actual record.
Looking at byte 61 of record 4 we find a space after a single double-quote. Analysis:
• The fifth column is qualified because it has a delimiter embedded in it.
• The problem is that once qualified starting with a double-quota, any literal double quote must be doubled.
• If a literal quote were needed, the fix is doubling it.
• Otherwise, the fix is simply removing the offending double-quote.
Delimited-Field CSV Error Resolution
Listed below are the choices you might consider.
• Show the list provider the problem(s) and ask for a corrected file.
• Make the correction(s) yourself.
• Delete the offending records.
Handy Advice
This will be easier if you have an editor that reports line and column and has a command to go to row and column. If you do not have an editor that does this, we like "TextPad" from Helios Software Solutions which has these capabilities. You can use "TextPad" on a trial basis or license it inexpensively.