Field Zero Metadata - Adding metadata to your CSV files

3.1 # as comments or metadata

This seems to be the most common way to add metadata or comments to a csv, it either requires a custom parser or preprocessing, it has no standard and can be quite ambiguous as a field starting with # can also be a valid field.

1. #This is my csv file!
2. #type=vectors
3. x,y,z
4. 1,2,3
5. 4,5,6
6. 7,8,9

3.2 Metadata file/Zip file

A file for metadata purposes is carried together with the csv file, usually with the same name as the csv file plus a suffix (-meta.type) and uses any data format, both files can then be bundled in a zip file.

1. my-csv.csv:
2. x,y,z
3. 1,2,3
4. 4,5,6
5. 7,8,9
7. my-csv-meta.xml:
8. <meta>
9. <description>This is my csv file!</description>
10. <type>vectors</type>
11. </meta>
13. my-csv.zip:
14. my-csv.csv
15. my-csv-meta.xml

3.3 W3C

They tried, I never heard of this W3C format until I started searching about metadata in CSV files.

1. #publisher,W3C
2. #updated,2015-10-17T00:00:00Z
3. #name,sensor,temperature
4. #datatype,string,float
5. sensor,temperature
6. s-1,25.5

3.4 References

• Adding metadata / identifier data to a CSV file?

• Store metadata in CSV file

• How to add metadata to a csv file?

• A Straightforward Way To Extend CSV With Metadata

4.1 Field Zero

After thinking about it, there's no better place to add metadata than placing into the first field of the csv file.

1. "metadata",y,z
2. 1,2,3
3. 4,5,6
4. 7,8,9

4.2 No other data formats

Instead of using other data formats such as json or xml, we use a embedded csv for it, this eliminates the requirement for other data formats.

1. "a,b,c,d",y,z
2. 1,2,3
3. 4,5,6
4. 7,8,9

4.3 Identifiable

We use a csv header for it, this can only contain a key and a value, any other header is not valid and should be processed as a normal field, even if the header is quite unique, it is still a good idea for parsers to add a option for reading it as text instead of metadata, redefinition of fields is allowed and is done in order of appearance, because this is a embedded csv file, double quotes must be written as two instead of one and four instead of two inside of quoted fields.

1. "csv-metadata-key,csv-metadata-value
2. a,10
3. b,20
4. x,""field, with, commas""
5. y,""field with """"double quotes""""!""
6. c,30
7. c,40
8. ",y,z
9. 1,2,3
10. 4,5,6
11. 7,8,9

4.4 Identification of the CSV data

We add a optional "csv-type-name" for type identification, similar to XML Namespaces, this allows a csv file to be easily distinguishable from each other, as it is for XML I recommend using a URL for it but any string is valid, even an empty one or nothing.

1. "csv-metadata-key,csv-metadata-value
2. csv-type-name,https://example.com/vectors.txt
3. ",y,z
4. 1,2,3
5. 4,5,6
6. 7,8,9

4.5 Field Zero Value

We add a optional "csv-field-zero" for defining the value of the field zero (the first field), if not present, a empty string is assumed.

1. "csv-metadata-key,csv-metadata-value
2. csv-type-name,https://example.com/vectors.txt
3. csv-field-zero,x
4. ",y,z
5. 1,2,3
6. 4,5,6
7. 7,8,9

4.6 Compatibility

If it is needed to remain compatible with a system, we can move all data to the right and remove the "csv-field-zero", this does not affect anything, it only changes the structure of the csv itself and makes the header reappear correctly again.

1. "csv-metadata-key,csv-metadata-value
2. csv-type-name,https://example.com/vectors.txt
3. ",x,y,z
4. ,1,2,3
5. ,4,5,6
6. ,7,8,9

4.7 Single-line and Multiline Comments and Line Skips

The fact that we allowed redefinition of fields means that we can now use empty keys as single-line or multiline comments or to skip a line, care should be taken with double quotes, as this is still a normal field.

1. "csv-metadata-key,csv-metadata-value
2. csv-type-name,https://example.com/vectors.txt
3. ,
4. ,Single line comment!
5. ,
6. ,""
7. This is
8. a multiline
9. comment!
10. ""
11. ",x,y,z
12. ,1,2,3
13. ,4,5,6
14. ,7,8,9

5.1 Reading

1. CSV csv = CSV.read(...);
3. String keyHeader = "csv-metadata-key";
4. String valueHeader = "csv-metadata-value";
5. String header = keyHeader + "," + valueHeader;
6. String quotedHeader = "\"" + keyHeader + "\",\"" + valueHeader + "\"";
8. Map<String, String> metadata = new LinkedHashMap<>();
9. String fieldZero = csv.get(0, 0);
11. //check if the field zero starts with the header
12. if (fieldZero.startsWith(header) || fieldZero.startsWith(quotedHeader)) {
14. //read the metadata as csv and check if the header is valid
15. CSV csvMetadata = CSV.read(fieldZero);
16. if (csvMetadata.getNumberOfFields() == 2
17. && csvMetadata.get(0, 0).equals(keyHeader)
18. && csvMetadata.get(1, 0).equals(valueHeader)
19. ) {
21. //read the metadata, skipping the header
22. for (int metaRecord = 1; metaRecord < csvMetadata.getNumberOfRecords(); metaRecord++) {
23. String k = csvMetadata.get(0, metaRecord);
24. String v = csvMetadata.get(1, metaRecord);
25. //remove comments
26. if (k.isEmpty()) {
27. continue;
28. }
29. metadata.put(k, v);
30. }
32. //set the field zero
33. csv.set(0, 0, metadata.get("csv-field-zero"));
34. }
35. }
36. System.out.println("Type: "+metadata.get("csv-type-name"));
37. System.out.println("Field zero: "+csv.get(0, 0));

5.2 Writing

1. CSV csv = CSV.read(...);
3. String keyHeader = "csv-metadata-key";
4. String valueHeader = "csv-metadata-value";
5. ...
6. Map<String, String> metadata = new LinkedHashMap<>();
7. ...
9. //set field zero
10. metadata.put("csv-field-zero", csv.get(0, 0));
12. //create a new csv with the size of metadata plus 1
13. CSV csvMetadata = new CSV(null, 2, metadata.size() + 1);
15. //write the header
16. csvMetadata.set(0, 0, keyHeader);
17. csvMetadata.set(1, 0, valueHeader);
19. //write the metadata
20. int index = 1;
21. for (Map.Entry<String, String> e:metadata.entrySet()) {
22. csvMetadata.set(0, index, e.getKey());
23. csvMetadata.set(1, index, e.getValue());
24. index++;
25. }
27. //output to field zero
28. csv.set(0, 0, csvMetadata.toString());
29. System.out.println(csv.toString());

7.1 Google Sheets

Google Sheets was the best one, seems to fully follow the RFC4180 in importing and exporting csv files.

7.2 LibreOffice

LibreOffice was able to import/export correctly but it has a strange behaviour to replace double quotes with curved double quotes on the editor and the lack of smooth scrolling makes it annoying to edit large metadata fields.

7.3 Excel

Excel never supported RFC4180 as far as I know, if things did not change, excel should still be the devil of csv files, generating different csv files that depend on which country you are, making sure that your life as a programmer will be a pain.

7.4 Other Online Editors

I was able to find a bunch of small online editors of csv files, all of the ones I tested seems to follow the RFC4180.