5 ms·
TPSV, an Alternative to TSV (and CSV)
- Hackbraten 1y agoGood on you to leverage EditorConfig settings. Almost every modern IDE or editor supports it either out of the box or with a plug-in.
- helix278 1y agoI like that there is plenty of room for comments, and the multiline extension is also cool. The backslash almost looks like what I would write on paper if I wanted to sneak something into the previous line :)
- ctenb 1y agoThanks :)
- montroser 1y agoThis is pretty under-specified... > A cell starts with | and ends with one or more tabs. |one\t|two|three How many cells is this? Seems like just one, with garbage at the end, since there are no closing tabs after the first cell? Should this line count as a valid row? > A line that starts with a cell is a row. Any other lines are ignored. Well, I guess it counts. Either way, how should one encode a value containing a tab followed by a pipe?
- jasonthorsness 1y agoThe spec says the last cell does not need to end in a tab, so this would be two cells IMO
- ctenb 1y agoThat's correct
- montroser 1y agoIs the "spec", the rules listed under the "syntax" heading? Or is it the python script? Or something else I missed? Because the python is in conflict with the syntax rules in that the rules say a cell ends with one or more tabs, but the python says a cell can end with the end of the line, even if there are no trailing tabs.
- ctenb 1y agoIt's rule number 6, which basically makes an exception to rule 1 :)
- bvrmn 1y agoI think spec tries and fails to translate code implementation into human language. In code cell separator is `\t|`.
- ctenb 1y agoThat's also correct. In what way does it fail?
- Hashex129542 1y agoWe need binary formats. In this era we are capable for it. Throw away the text formats.
- deleted 1y ago[deleted]
- bsder 1y agoData will always outlive the program that originally produced it. This is why you should almost always use text formats.
- account-5 1y agoText is universally accessible and widely supported. Binary has it's benefits, but human facing, it has to be text.
- voidfunc 1y agoYep, and stuff it into a sqlite db too you have a query interface all built.
- culi 1y agoWhat's a good program that non-technical people can use to write sqlite db data. I think it's a great idea in theory but lacking in support
- rr808 1y agoparquet?
- smallerize 1y agoOk but how do you type them? How do you search them? How do you copy-and-paste between documents?
- Hashex129542 1y agoWe are just changing the encoders and decoder program only from text to binary. The front end software remains same for example we are using for CSV. The binary form has lot of benefits than plain text form in editing. For example, when you are replacing UIn8 value from 0 to 100 then you just replacing a byte at a position instead of rewriting whole document.
- bvrmn 1y agoAccording to spec it's nearly impossible to correctly edit files in this format by hand.
- mkl 1y agoHow so? All you need is a text editor that preserves tabs.
- bvrmn 1y ago1. It's quite easy to miss a tab and use only `|`. 2. Generated TPSV would look like an unreadable hard to edit mess. I doubt any tool would calculate max column length to adjust tab count for all cells. It basically kills any streaming.
- mkl 1y agoYou have a very strange definition of "nearly impossible". > 1. It's quite easy to miss a tab and use only `|`. Any format is hard to edit manually if you don't follow the requirements of the format (which are very simple in this case). > 2. Generated TPSV would look like an unreadable hard to edit mess. CSVs are much less readable than this, but still entirely possible to edit.
- bvrmn 1y agoMy wording is bad, I agree. Original thought was: complicated edit makes TPSV adoption as nearly impossible.
- AstroJetson 1y ago> A row with too many cells has the superfluous cells ignored. Ummm, how do you figure out what row has too many cells? Can all the rows before this one have too few cells?
- stevage 1y agoI hate this kind of format. It's trying to be both a data format for computers and a display format for humans. Much better off just using a tool that can edit CSV files as tables. Also it doesn't seem to say anything about the header row?
- nmz 1y agoCSV is also a display format for humans and also for computers. Its also a terrible one because its too variable FS is variable, escapes could exist, "" could be used, this slows down parsing.
- stevage 1y agoI wouldn't say CSV is a display format. Attempting to edit it by hand is pretty error prone, and reading it is hard work.
- DrillShopper 1y agoOr we could use the actual characters for this purpose - the FS (file separator), GS (group separator), RS (record separator), and US (unit separator). ASCII (and through it, Unicode) has these values specifically for this purpose.
- addoo 1y agoI’m pretty sure part of the intent is that it should be easy to write (type) in this format. Separator characters are not that. Depending on the editor, they’re not especially readable either.
- EvanAnderson 1y agoI did an ETL project for an ERP system that used these separators years ago. It was ridiculously easy because I didn't have to worry about escaping. Parsing was an easy state machine. Notepad++ handles the display and entry of these characters fairly easily. I think they're nowhere as unergonomic as people say they are.
- ilyagr 1y agoI don't think popularizing these ASCII characters would solve the problem in its entirety. If RS and US were in common use, there would be a need to have a visible representation for them in the terminal, and a way to enter RS on the keyboard. Pretty soon, strings that contain RS would become much more common in the wild. Then, one day somebody would need to store one of those strings in a table, and there would be no way to do so without escaping. I do think that having RS display in the terminal (like a newline followed by some graphic?) and using it would be an improvement over TSV's use of newline for this purpose, but considering that it's not a perfect solution, I can understand why people are not overly motivated to make this happen. The time for this may have been 40+ years ago when a standard for how to display or type it would be feasible to agree upon.
- eviks 1y ago> there would be a need to have a visible representation for them in the terminal, and a way to enter RS on the keyboard. Both already possible, they have official symbols representing them > Then, one day somebody would need to store one of those strings in a table, and there would be no way to do so without escaping. Why? But also, yes, escaping also exists, just like in the alternative formats
- aidenn0 1y agoIf only ASCII had a field separator character, then we could just use that instead.
- LargoLasskhyfv 1y agoNot sure if satire, or not. Just in case of not: https://en.wikipedia.org/wiki/ASCII#Character_groups https://en.wikipedia.org/wiki/ASCII#Character_groups https://en.wikipedia.org/wiki/C0_and_C1_control_codes#C0_controls https://en.wikipedia.org/wiki/C0_and_C1_control_codes#C0_con...
- 1vuio0pswjnm7 1y agoNow sure about "we" but I have used these for years in personal projects for own purposes. Dec Octal Hex Binary 028 034 01C 00011100 FS (File Separator) 029 035 01D 00011101 GS (Group Separator) 030 036 01E 00011110 RS (Request to Send)(Record Separator) 031 037 01F 00011111 US (Unit Separator)
- ctenb 1y agoWhat software did you use to write that, and how did other dev tools behave with these characters? Just curious
- 1vuio0pswjnm7 1y ago1. tcpclient, yy025 (a program I wrote that generates HTTP), sed, cut, paste, nvi or vim 4.6 if I need to edit, depending on whether I'm using NetBSD or Linux; formatted text is copied and pasted using tmux buffers since I do not use X11 For example program|sed or grep|tmux loadb /dev/stdin where program is a script that outputs a custom ASCII table 2. The UNIX software I'm using works with these characters The idiom I use for inserting the FS character via command line or shell script is x=$(echo x|tr x '\34') For example, echo select \* from t1|sqlite3 -separator $x 0.db > 0.fsv "To import data with arbitrary delimiters and no quoting, first set ascii mode (".mode ascii"), then set the field and record delimiters using the ".separator" command. This will suppress dequoting. Upon ".import", the data will be split into fields and records according to the delimiters so specified."^1 But I use tr. tr '\34' , < 0.fsv > 0.csv If there is a risk that field values might contain the FS character, then I delete or replace that character in the data before I create fields. For example, sed -i "s/$x//g" data sed -i "s/$x/FS/g" data 1. https://sqlite.org/cli.html https://sqlite.org/cli.html
- CJefferson 1y agoHonestly at this point my favorite format is JSONLines (one JSON object per line). It instinctively feels horrible, but it’s easy to create and parse in basically every language, easy to fully specify, recovers well from one broken line in large datasets, chops up and concatenates easily.
- hiAndrewQuinn 1y agoI second this. I'm using JSONL to bake in the data for my single binary Finnish to English pocket dictionary ( https://github.com/hiAndrewQuinn/tsk https://github.com/hiAndrewQuinn/tsk ). It just makes things like data transformations so easy, especially with jq.
- karmakaze 1y agoIs there a text format like TSV/CSV that can represent nested/repeating sub-structures? We have YAML but it's too complex. JSON is rather verbose with all the repeated keys and quoting, XML even moreso. I'd also like to see a 'schema tree' corresponding to a header row in TSV/CSV. I'd even be fine with a binary format with standard decoding to see the plain-text contents. Something for XML like what MessagePack does for JSON would work, since we already have schema specifications.
- culi 1y agoWell there's JSONL which is used heavily in scientific programs (especially in biology) But CSV represented as JSON is usually accomplished like so: { "headers": ["name", "habitat", "food"], "data": [ ["Acorn Woodpecker", "forest", "grain"], ["American Goldfinch", "grassland", "grain"], ["Anhinga", "wetland", "fish"], ["Australian Reed Warbler", "wetland", "grub"], ["Black Vulture", "forest", null] ] }
- gglitch 1y agoS-expressions?
- dreamcompiler 1y agoGreenspun's Tenth Corollary: Every textual data format that is not originally S-expressions eventually devolves into an informally-specified, bug-ridden, slow implementation of half of S-expressions.
- tomhovv 1y ago[flagged]
- ilyagr 1y agoIt's a clever format, especially if the focus is on machines generating it and humans or machines reading it. It might even work for humans occasionally making minor edits without having to load the file in the spreadsheet. I think it can encode anything except for something matching the regex `(\t+\|)+` at the end of cells (*Update:* Maybe `\n?(\t+\|)+`, but that doesn't change my point much) including newlines and even newlines followed by `\` (with the newline extension, of course). For a cell containing `cell<newline>\`, you'd have: |cell<tab>| \\<tab >| (where `<tab >` represents a single tab character regardless of the number of spaces) Moreover, if you really needed it, you could add another extension to specify tabs or pipes at the end of cells. For a POC, two cells with contents `a<tab>|` and `b<tab>|` could be represented as: |a<tab ><tab>|b ~tab pipe<tab>|tab pipe (with literal words "tab" and "pipe"). Something nicer might also be possible. *Update:* Though, if the focus is on humans reading it, it might also make sense to allow a single row of the table to wrap and span multiple lines in the file, perhaps as another extension.
- ctenb 1y agoFor multiline cell contents, there is rule 7, the multi line extension. Newlines are not allowed in cells otherwise, because of rule 2, it's a line based format. I personally use it to write tabular data manually, used to define our datamodel. Because this format is editor agnostic, colleagues can easily read and edit as well. So in my case it's focus on human read/write and machine read.
- Rhapso 1y agoThe poor delimiter special characters in the ascii table never get any love.
- ctenb 1y agoYeah :) Though I can think of two reasons why: it's not typable for most people on a keyboard, and most programs are not designed to deal with it, or render it properly in an aligned way, like tab characters.
- TheTaytay 1y agoI have been using TSV a LOT lately for batch inputs and outputs for LLMs. Imagine categorizing 100 items. Give it a 100 row tsv with an empty category column, and have it emit a 100 row tsv with the category column filled in. It has some nice properties: 1) it’s many fewer tokens than JSON. 2) it’s easier to edit prompts and examples in something like Google sheets, where the default format of a copied group of cells is in TSV. 3) have I mentioned how many fewer tokens it is? It’s faster, cheaper, and less brittle than a format that requires the redefinition of every column name for every row. Obviously this breaks down for nested object hierarchies or other data that is not easily represented as a 2d table, but otherwise we’ve been quite happy. I think this format solves some other things I’ve wanted, including header comments, inline comments, better alignment, and markdown support.
- imtringued 1y agoThe problem with CSV is that there are too many variations and that quoting is still a mess. The vast majority of people on this planet do not know CSV, so they invent a new adhoc format on the fly and falsely call it CSV. TPSV solves none of that and makes things worse.
- ctenb 1y agoWhat does it make worse exactly?