Why isn’t there a decent file format for tabular data? • Hacker News Board

Re: Why isn’t there a decent file format for tabular data?

#301

Post by carapace » Wed, May 04, 2022, 1:39 PM UTC

https://git.sr.ht/~sforman/sv

> This is a simple file format for storing tabular content. It's meant to work like CSV files. So far only the low-level encoding is specified, handling e.g. type conversion, etc., is left to a higher-level abstraction layer (as yet unwritten.)

> An *.sv file consists of one or more documents separated by ASCII Group Separator character (0x1D), sv files are UTF-8 encoded.

> Each document can have an optional header indicated by the presence of ASCII Start of Heading character (0x01) followed by zero or more records of metadata. If present, the first record should be field names, the second record should be type names, any further records are user-defined. The first two records (names and types) must be the same length as the data records. The header is concluded by an ASCII Start of Text character (0x02).

> Records are separated by ASCII Record Separator characters (0x1E), and data units (fields) of each record are separated by ASCII Unit Separator characters (0x1F). All record values must be strings.

> ...a grammar:

    sv  ::=  doc [GS sv]

    doc  ::=  [header] records

    header  ::=  SOH records STX

    records  ::=  record [RS records]

    record ::= field [US record]

    field  ::=

A table of the sv ASCII characters:

    1   01  00000001    SOH &#001;      Start of Heading
    2   02  00000010    STX &#002;      Start of Text
    29  1D  00011101    GS  &#029;      Group Separator
    30  1E  00011110    RS  &#030;      Record Separator
    31  1F  00011111    US  &#031;      Unit Separator

Re: Why isn’t there a decent file format for tabular data?

#302

Post by Pxtl » Wed, May 04, 2022, 1:41 PM UTC

Pxtl wrote: post #139

tony-allan wrote: post #95

Earlier quoted context omitted.

This is a very cool idea but why not just add the closing tags and have a minimal format that is also valid HTML and can easily view the data by pointing your browser at the file? It's already a universal format and as dtech mentions below, you get Excel compatibility. I would also add headers and an optional type attribute... Age If the file is too big then you probably need compress it or use a more appropriate for…

The document I posted was valid html6 -- all of those end tags are optional. From an XML purist's perspective that's abhorrent, but if we're trying to make a terse table format skipping the close tags is good.

FFS I don't know how I typo'd "html6" I meant HTML5. Too late for edit.

Re: Why isn’t there a decent file format for tabular data?

#303

Post by Pxtl » Wed, May 04, 2022, 1:42 PM UTC

elcritch wrote: post #261

Pxtl wrote: post #139

Earlier quoted context omitted.

The document I posted was valid html6 -- all of those end tags are optional. From an XML purist's perspective that's abhorrent, but if we're trying to make a terse table format skipping the close tags is good.

SGML originally defined optional tags. The intent was for doc writers to not need to end tags. XML in my view was a 20 year regression..

Also didn't SGML offer a terse generic closer ?

Re: Why isn’t there a decent file format for tabular data?

#304

Post by tzs » Wed, May 04, 2022, 1:46 PM UTC

donatj wrote: post #78

> Columns are separated by \u001F (ASCII unit separator) > Rows are separated by \u001E (ASCII record separator) That's a nightmare to try to edit yourself in a text editor? I'd rather just have basically TSV, but with every value always quoted, always UTF-8. Quotes escaped with backslashes, backslashes escaped with backslashes, and that's it. Any binary allowed between the quotes. I deal with CSVs all day every day.…

> I'd rather just have basically TSV, but with every value always quoted, always UTF-8. Quotes escaped with backslashes, backslashes escaped with backslashes, and that's it. Any binary allowed between the quotes.

Encode rather than escape, such as encoding an arbitrary byte as %xx where xx is two hex digits. Use this encoding for any %s in the values, as well as any field separators and record separators and any bytes that have special meaning in your format.

Encoding rather than escaping means that given a record I can split it into fields using the built-in string splitting method of whatever language I'm using. Dealing with a format that can have the field separators escaped in the values will usually present less opportunity to use the language's efficient built-in string functions.

Re: Why isn’t there a decent file format for tabular data?

#305

Post by Pxtl » Wed, May 04, 2022, 1:46 PM UTC

elcritch wrote: post #257

Pxtl wrote: post #26

> Why can’t we have a format where Does Excel support it? No? Then that's the end of that. Excel is tabular data to all non developers. The formats supported by Excel are the whole thing. And if we're inventing a CSV-like format that uses a more convenient character than quotes and commas, maybe jumping to a non-displayable non-typeable character isn't the best? Honestly, if I were inventing a table format, I'd use a…

I've pondered exactly that. After a previous HN thread, I tried my hand at writing a specification that was minimal as possible but followed HTML5. For example many end tags are optional like `tr` and `td` end tags. Though I pulled in RDFa for richer data types. Here's the GitHub repo for what I like to call HSV5: https://github.com/elcritch/hsv5/blob/main/README.md ;) And an example of the format, pretty similar to…

Neat!

edit: wait, the stuff after is implicitly the first cell? That's new to me.

Re: Why isn’t there a decent file format for tabular data?

#306

Post by zoomablemind » Wed, May 04, 2022, 1:53 PM UTC

PeterisP wrote: post #206

zoomablemind wrote: post #180

Earlier quoted context omitted.

What's so special about having a named type for datetime? User will still need to call functions to manipulate the dates. If only for the default display and import?

For dates, having a specific date type instead of a text field is required to have proper behavior when sorting and efficient storage/data transfer. It's also important to have date-related functions on the DB server side, so that you can use them in filtering data before it gets sent over to the user code running on the client, to avoid unnecessary data transfer and allow proper use of indexes in optimizing it. Also…

> ...`WHERE year(date)=2021` without actually running that function on every date, but rather automatically optimize it to an index lookup of `WHERE date between '2021-01-01' and '2021-12-31'`

Sure this would be handy. Are there engines that implement such optimization?

I can also see how the 'dated' WHERE clause could be used directly in SQLite to leverage the index. Of course, using year() is more expressive. It may also make sense in such a case to simply add a year column and have it indexed.

Re: Why isn’t there a decent file format for tabular data?

#307

Post by sindoc » Wed, May 04, 2022, 1:54 PM UTC

I agree with the author. There’s merit in an open format to be developed with a simple spec as pretty much defined here. Of course more formalism should he attached to it i.e. BNF (am I too old?)

Anyway, one of the giants making billions out of data should maybe commission the publication of such a standard and the implementations in every programming language will follow, I’m sure.

The Unicode bit alone and an enforced mine type makes the case in my opinion and it should have streaming capabilities as well. Maybe chunking of content so that it can be used for caching and compression, ideally still human readable but not marked up like XML so you can’t see the end of it.

Re: Why isn’t there a decent file format for tabular data?

#308

Post by Pxtl » Wed, May 04, 2022, 2:05 PM UTC

zzo38computer wrote: post #226

Pxtl wrote: post #26

> Why can’t we have a format where Does Excel support it? No? Then that's the end of that. Excel is tabular data to all non developers. The formats supported by Excel are the whole thing. And if we're inventing a CSV-like format that uses a more convenient character than quotes and commas, maybe jumping to a non-displayable non-typeable character isn't the best? Honestly, if I were inventing a table format, I'd use a…

Is used anywhere else? (I had considered ; my reason was that it seems to work better with user CSS, and might be simpler to parse in other cases too.)

I can't remember where I saw "subtype". Probably something I misrememebered. Yours is better.

Re: Why isn’t there a decent file format for tabular data?

#309

Post by Pxtl » Wed, May 04, 2022, 2:06 PM UTC

qayxc wrote: post #276

Pxtl wrote: post #26

> Why can’t we have a format where Does Excel support it? No? Then that's the end of that. Excel is tabular data to all non developers. The formats supported by Excel are the whole thing. And if we're inventing a CSV-like format that uses a more convenient character than quotes and commas, maybe jumping to a non-displayable non-typeable character isn't the best? Honestly, if I were inventing a table format, I'd use a…

Works well until some joker comes along and puts or in your data :)

Well yeah, no matter what escape chars will be a challenge. Also whitespace. Imaging all the   values if you import a whitespace-heavy string.

Re: Why isn’t there a decent file format for tabular data?

#310

Post by sleightofmind » Wed, May 04, 2022, 2:25 PM UTC

I don't know why.

That said, I've enjoyed using Carlo Strozzi's NoSQL program in the past. It's not NoSQL in the current sense of the word. It's a plain text database that stores data in delimited text fields -- e.g. tsv or csv. Create those fields however you want. I just used rlwrap/readline in a simple shell program for input. It does the basic relational database things, but all with plain text. A text editor will work just fine for data entry, and for viewing it later. It will be around as long as Linux commandline utilities are around.

Your data can be viewed in a line-oriented or list-oriented fashion -- the program does the conversion for you in commandline fashion. And you can view everything in a text editor. I think it's just a bit of code tying together a bunch of standard Linux commandline utilities. It's not in a lot of repositories these days. But back when, I found it quite handy for working with line-oriented tabular data. It was the guts of a bailing wire and bubblegum program I wrote to create reinforcing steel barlists back when I only thought I knew what I was doing. My ignorance persists but now I'm aware of and at peace with it.