everything in one sheet: csv compatibility

[jcolomb]

Hi,
I was sent here from the csvy project. I had an idea to expand csv files to have the metadata included in the file, in a way very similar to what metatab is supposed to work (I am not sure I got all of it though), maybe my comment is therefore more valuable here, or you can direct me to yet another project :)

Here the idea:
Dear all,

I think finding a way to pool metadata and data into one file is great and the way to go. I love the idea of csvy. Thing is, if the metadata needs a different program to be written, it will done only by techies.

When I saw the new read_csv function from readr, I got an idea... My proposition would be the following, take everything that is good in YAML, but transform its format to be csv. This would be the example:

---,---
name, my-dataset
description, "this is an example of the new csv format, including metadata on top."
author, "Julien Colomb"

fields, name, title, type,desciption, constraints
fields,var1,variable 1, string, explaining var1, required
fields, var2, variable 2, integer
fields, var3, variable 3, number

---,---
var1,var2,var3
A,1,2.5
B,3,4.3

why I think it could be cool:

scanning for the first 8 character, if the first 3 are "-" at the beginning, the fourth one is the delimiter, the 8th is the line break. This would make csv file readable, whatever the the delimiter is, and makes compatibility issues (with excel) obsolete. In addition if the first 3 character are not "-", there is no metadata and the program can (try to) read the file as a normal csv.

You can read and write the metadata in your normal spreadsheet program.

if templates is made for fields names, it is easy for any user to enter metadata, you just copy and paste-transposed the headers and fill the table.

What do you think?

This issue is licenced CC0.

[ericbusboom]

Hi Julien,

That does look a lot like Metatab! The only difference would be that the first two columns of the first "fields" row would be something like 'Section,schema' instead of 'fields,name'. Or it could be:

Section, Schema
Header, name, title, type, description, constraints
fields,var1,variable 1, string, explaining var1, required
fields, var2, variable 2, integer
fields, var3, variable 3, number

Since we have both invented the same thing, I think it is a great idea.

I might suggest that the '---,---' markers could be replaced with an "end of metadata" marker, since you really don't need both of them.

name, my-dataset
description, "this is an example of the new csv format, including metadata on top."
author, "Julien Colomb"
Section, Schema
Header, name, title, type, description, constraints
fields,var1,variable 1, string, explaining var1, required
fields, var2, variable 2, integer
fields, var3, variable 3, number

EndOfMetadata

var1,var2,var3
A,1,2.5
B,3,4.3

I could update the spec to require that a Metatab parser stop reading rows at the first EndOfMetadata term.

The '---,---' delimiter works too, but then there would be a different top level parser which would split out the Metatab from the CSV and parse the Metatab separately.

[jcolomb]

Another difference is the presence of a delimiter for the start of the metadata. It makes sense to include it such that one could scan the first element of the speadsheet and know if there is metadata or not. (instead of having to look for a "EndOfMetadata" tag. (First row will always be row number 1 :)).

As explained, having the "startofmetadata" tag over two columns would make it possible to make the csv computer readable without having to look manually at what the delimiter is (at the moment, Reading csv code are split between a version for comma and tab delimiter and one version for semicolon delimiter...)

Having different start and end tags is a good point, I agree.

Another expension I forsee for this kind of metatab, is the possibility to get columns with a unique value in the metadata. For example if the same spreadsheet is used to gather data every day, the day column (unique value for each spreadsheet) could go in the metadata part, while the computer could still merge the different spreadsheets, adding the column date which would now have a unique value anymore (this is probably neither clearly explained nor important, my apologies...)

[jcolomb]

What about

StartOfMetadata, metatab, "v1.0"

as start tag?

What about encoding? If I write StartOfMetadata in the first cell of my spreadsheet, will it stay identical in different encoding? What about having some tag which would be transformed differently in different encoding, such that by reading it, the computer might "know" what is the encoding of the file?

[ericbusboom]

There's two alternatives here:

1. Embed Metatab in a higher level format, which defines the delimiters
2. Extend Metatab to include inline data.

For 1, You can have any start or end tags you'd like, since the format would be one you;d define. It would use Metatab, but would be defined separately.

For 2, the delimiters would have to be valid Metatab, and there shouldn't be a new tag to start the metatab data, since that would be unnecessary for most Metatab files, which don't have inline data.