Can a CSV file have a comment?

Question

The CSV "standard" (such as it is) does not dictate how comments should be handled, no, it's up to the application to establish a convention and stick with it.

In engineering data, it is common to see the # symbol in the first column used to signal a comment.

I use the ostermiller CSV parsing library for Java to read and process such files. That library allows you to set the comment character. After the parse operation you get an array just containing the real data, no comments.

No, CSV doesn't specify any way of tagging comments - they will just be loaded by programs like Excel as additional cells containing text.

The closest you can manage (with CSV being imported into a specific application such as Excel) is to define a special way of tagging comments that Excel will ignore. For Excel, you can "hide" the comment (to a limited degree) by embedding it into a formula. For example, try importing the following csv file into Excel:

=N("This is a comment and will appear as a simple zero value in excel")
John, Doe, 24

You still end up with a cell in the spreadsheet that displays the number 0, but the comment is hidden.

Alternatively, you can hide the text by simply padding it out with spaces so that it isn't displayed in the visible part of cell:

                              This is a sort-of hidden comment!,
John, Doe, 24

Note that you need to follow the comment text with a comma so that Excel fills the following cell and thus hides any part of the text that doesn't fit in the cell.

Nasty hacks, which will only work with Excel, but they may suffice to make your output look a little bit tidier after importing.

I think the best way to add comments to a CSV file would be to add a "Comments" field or record right into the data.

Most CSV-parsing applications that I've used implement both field-mapping and record-choosing. So, to comment on the properties of a field, add a record just for field descriptions. To comment on a record, add a field at the end of it (well, all records, really) just for comments.

These are the only two reasons I can think of to comment a CSV file. But the only problem I can foresee would be programs that refuse to accept the file at all if any single record doesn't pass some validation rules. In that case, you'd have trouble writing a string-type field description record for any numeric fields.

I am by no means an expert, though, so feel free to point out any mistakes in my theory.

A Comma Separated File is really just a text file where the lines consist of values separated by commas.

There is no standard which defines the contents of a CSV file, so there is no defined way of indicating a comment. It depends on the program which will be importing the CSV file.

Of course, this is usually Excel. You should ask yourself how does Excel define a comment? In other words, what would make Excel ignore a line (or part of a line) in the CSV file? I'm not aware of anything which would do this.

If you need something like:

  │ A                              │ B
──┼────────────────────────────────┼───
1 │ #My comment, something else    │
2 │ 1                              │ 2

Your CSV may contain the following lines:

"#My comment, something else"
1,2

Pay close attention at the 'quotes' in the first line.

When converting your text to columns using the Excel wizard, remember checking the 'Treat consecutive delimiters as one', setting it to use 'quotes' as delimiter.

Thus, Excel will split the text at the commas, keeping the 'comment' line as a single column value (and it will remove the quotes).

If you're parsing the file with a FOR command in a batch file a semicolon works (;)

REM test.bat contents

for /F "tokens=1-3 delims=," %%a in (test.csv) do @Echo %%a, %%b, %%c

---

;test.csv contents (this line is a comment)

;1,ignore this line,no it shouldn't

2,parse this line,yes it should!

;3,ignore this line,no it shouldn't

4,parse this line,yes it should!

OUTPUT:

2, parse this line, yes it should!

4, parse this line, yes it should!