Store metadata in CSV file

Question

I understand that this would be a misuse of the CSV format. I do understand that more appropriate solution would be to export xls/xlsx file, or give user an OData end point and let them use power pivot to retrieve data. At this time I want to stick with CSV, because it is easier to implement.

I need to export a flat list of rows (a report) as a CSV file. At the same time I would like to include some metadata about the data. E.g. report title, generation date, and user.

Is there a neat way to accomplish both goals using CSV file format?

The intent usage for this file is to allow the user to open file in Excel and do data crunching.

One solution would be to break CSV into two sections: meta and data. E.g.

Title,Report Generation Date,Generated by User,,
Outstanding Jobs,5/5/2015,[email protected],,
,,,,
Job Started, Estimated Completion, Description, Foo, Bar
9/3/2003,1/1/2006,"do something important""""",5,7
5/4/2007,2/2/2010,"do something else""""",3,12

Or perhaps there is a "standard" extension to CSV format for this purpose?

Are there any practical issues with this approach?

Answer 1

When I wanted to add some metadata to a csv file I ended up storing it as a json string as part of the last column header.

timestamp;x;y;z;heading#{"id": "-L4uNnjWXL2cLY_xpU_s"}
89318.329;0.00541;-0.00548;-0.00219;-1.0
89318.339;0.00998;0.0063;-0.00328;-1.0
89318.349;0.0043;0.01318;0.00069;-1.0
89318.359;0.00477;0.0138;0.0007;-1.0

The advantage of this compared to OP's suggestion is that it's a valid csv file in terms of all rows having equal amount of columns and each column containing only one type of data. A parser that knows about the format could split the header at the # and parse the json metadata. A parser that doesn't know of the format will simply display the last column's header with the metadata.

Answer 2

There is no standard extension to CSV that allows for storing of metadata.

You can certainly have a separate section in the file for metadata, but that tends to complicate processing as the CSV parser has to deal with separate headers, etc. Standard CSV parsers won't do that for you.

Consider whether you can store the metadata in a separate file, using a naming convention, e.g.

MyData123.csv
MyData123-Meta.csv

You could bundle both into a Zip archive to keep them together, until they are ready for processing.