Further on tsv....

[boydkelly]

Hi there....

Just a couple of examples of challenges with csv format. I need to sort some 2400 rows of data in order to eliminate redundant data. The first field of my data is the guid, the second field should be the sort order.

When I run:

$  sort -t',' -k2 import.csv | head -5
"Nf(,1",ɲiniga,yd,,questionner,,ɲiningan,,,,,,,,"<img src=""_CIV.png""><br>",,,,,,,Mɔgɔ ɲinigan fɛn na / Fɛn ɲini mɔgɔ fɛ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"0406again, 0531c, 0601, 0605, 0625, v"
",1w;C",yíran,bk,,frire,,yiran,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"0406again, 0531c, 0601, 0605, 0625, lx-fr, vt"
"Jh,;2",ala y'a ɲuma se,bk,,bonne route (arriver),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"0406brainbrew, 0601, bénédiction, marked, phrase, salutation"
"fn,!2)b[Ok",kɔgɔjida,jl 12,,plage,,,,,,,,,,,,,,plagi,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"0406again, 0531c, 0605, 0625, emprunt"
")/V,2",ò / olugu / olu / ù,kodi,pp,ils / eux,,,,,,,,,,12,"<img src=""_BFA.png"">",,"<img src=""_MLI.png"">",u / ula,,u / olu,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,they /them,"0406again, 0531c, 0601, 0605, 0625, essentiel, MLI, pp, w100"

You can see that the sort order is totally botched. (I was going to use other terms....)

ɲininga
yiran
ala

is not sorted corrrectly.

I have been using other scripts with awk using:

 awk -F"," 'BEGIN { FPAT = "([^,]*)|(\"[^\"]+\")"; OFS="\t";} {print $1, $2, $5, $63}' | sort -k2

with some success, but even then I have had issues with the tags field which has comma delimiters within comma delimiters...

https://docs.google.com/spreadsheets/d/e/2PACX-1vRTiH57lVffHLnNcw8XUVb2A2ah3PgAkw-e_2iL4z45ONrkPN8wOjto38-QhT1UsAEcL-_9g3z5mgJN/pubhtml

If you scroll over to column bh, you can also see the problems with this data. (The tags are not all in the same column.)

Otherwise in the brainbrew file editiing with neovim this data is FINE. I do not have an issue. I just can't sort the data (easily).

I have been able to use awk to convert to TSV using an FPAT instead of FS. Then I sort the data and convert it back to CSV.

cat $bbrew |grep -v phrase | grep -v exp | awk 'BEGIN {OFS="\t"; FPAT = "([^,]*)|(\"[^\"]+\")";} {$1=$1} {print $0}'│  > $out.tsv

But this is a pain. And it has caused some recoverable errors, I am assuming that come from the uuids.

As I see things there are 3 issues with my data and brainbrew.

1. I have TONS of embeded quotes and commas. This is prose, not inventory.
2. The uuids are super nice and short, but contain commas and wierd stuff.
3. Anki stores its tags separated by commas within a field that is comma separated. (Thats crazy too!)

There are also lines with embedded html that also cause issues:
(Normally those > should be pipes (as displayed by csv.vim) but they are not, and so the line is showing 53 fields instead of 63.

viWUe/r/*^│tɛnɛnlɔn / tɛnɛn│bk││lundi││ntɛ̀nɛn / tɛ̀nɛn│>       > > > > > > "<img src=""_CIV.png"">"│"<img src=""_BFA.png"">"

Anki's default text export format is in tsv which avoids all these issues.

Python does seem to deal better with all this than awk and sort.

Would love to see brainbrew's python run recipes/anki_to_source.yaml (and back) that produces a tsv file! Much easier to work with!

Thanks so much for your awesome anki addon!

[boydkelly]

This is another example of a csv issue and awk. The split command below is using , as a separator. But everywhere I have a full sentence with a comma it is adding an extra field..... aaarg!

BEGIN { FS=OFS=","; FPAT = "([^,]*)|(\"[^\"]+\")" }
NR==FNR {
    file1rec[$1] = $0
    next
}
$1 in file1rec {
    split(file1rec[$1],file1flds)
    $2 = file1flds[2]
    $5 = file1flds[5]

[ohare93]

Aren't your sentences with commas in quotes? E.g:

test,test2,"test3, with more",test4

[boydkelly]

Yes they do. And the first column guid, when it contains commas the field is quoted. And as I mentioned I believe python and others deal with this very well. But the unix sort command seems to barf on I believe the guids. When I convert to tsv I can sort by the second field. But not with csv. Even with quoted fields. The second issue I found is that with another script, I am needing to use the awk split command. So test,test2,"test3, with more",test4 is dumped into a single array variable and then awk 'splits' it (using comma delimited). But with this example, I would finally end up with 1 additional field in the row. Looks like it also can't handle the quoted commas. Anyway I made some super progress last night. I went nuclear and replaced all the commas in a tsv file with tildes. Then I as able to do the awk split thing. (Now I just have to remember everything I did....)

[ohare93]

I will get Tsv setup as an option sometime in the next 2 weeks! 👍

[aplaice]

FWIW you can use csvtool (installable on Debian-based systems as part of the csvtool package), as probably a safer way of converting CSV to/from TSV:

# CSV to TSV
csvtool -t COMMA -u TAB cat in.csv > out.tsv
# TSV back to CSV
csvtool -t TAB -u COMMA cat in.tsv > out.csv

or as part of a pipeline:

foo | csvtool -t COMMA -u TAB cat - | bar

(You can use csvtool to also carry out some other manipulations of CSV/TSV files — e.g. select only certain columns etc.)

---

Is the botched Google docs import directly from BrainBrew's CSV export? (or was it from the awk conversion of the CSV export to TSV?) (If the former that's problematic...)

[boydkelly]

No there is no issues at all with BrainBrew's CSV export! I maintain a separate dictionary (yaml>xml>(tsv/csv)> brainbrew) where if I updated add entries, I can update my anki cards with that info. So I export that yaml to the same csv structure as my brainbrew file and then with awk, do what is in effect a 'join' on the uuid to update brainbrew. The
google botch was a result of this code which is splitting the csv record. I finally just removed all commas from my data and have at least been able to import to Google. And actually its simple to do a search/replace to restore the commas in specific fields.

$1 in file1rec {
    split(file1rec[$1],file1flds)

[boydkelly]

Yes thanks for the suggestions on csvtools. I had actullally already tested that and it probably works great. I had just settled on a python script I picked up that also does the job.

[boydkelly]

FYI: I also sent a message to the coreutils mailing list:

Not sure if I am doing this correctly, but I seem to have a problem sorting when the first column of my data contains a uuid often with embedded commas. Does sort deal with this properly?

My file contains this: (where the first and last fields may contain embedded commas.

"}~,)4",nɛgɛso,yq,,vélo,,,,,0630
"wjv(,4q_S}",badenmuso,cj,,soeur,,,,,"0630, fam, gens"
ARm}q0bjb#,a be baara kɛ.,bk,,il travaille.,,,,,"0630, phrase"

The following is not correctly sorted by the second field
$ sort -t "," -k 2 test2.csv
"}~,)4",nɛgɛso,yq,,vélo,,,,,0630
"wjv(,4q_S}",badenmuso,cj,,soeur,,,,,"0630, fam, gens"
ARm}q0bjb#,a be baara kɛ.,bk,,il travaille.,,,,,"0630, phrase"

However the second column alone will sort fine:
$ sort test2.txt
a be baara kɛ
badenmuso
nɛgɛso

Thanks for any enlightenment!

[boydkelly]

I also noticed something else extremely weird. (No I don't think with brainbrew; either with Anki or csvtool)
I tried out csvtool and noticed it was doing the same as another tool I had also tried. To demonstrate, I have a test csv file with a single row containing 18 fields:

FNbDHC4rMg,bolo jugu,bk,,gauche,,,,,,,,,,"<img src=""_CIV.png"">",,,"<img src=""_MLI.png"">",

when I run:

csvtool -t COMMA -u TAB cat test.csv > out.tsv

and then take a look in vim i only have 7 fields (same if I import to Google docs or libre office calc).
The converted file looks like this:

FNbDHC4rMg> bolo jugu>bk> > gauche> > > > > > > > > > "<img src=""_CIV.png"">"> > > "<img src=""_MLI.png"">">

Oddly it looks ok here. But the > (representing tabs) before the html are not registering as fields.

And I can see that vim is reporting on my status line one of the fields containing html contains some wierd control characters. (^I^I). You can also see below it is reporting that as field 6 of 7. (When there should be 18)

brainbrew/Jula/src/data/out.tsv   ["<img src=""_CIV.png"">"^I^I 6/7]

I didn't find any control characters in the Main.csv from brainbrew. Only once csvtool touched it. But why would csvtool be adding this? There is nothing else weird in the data. Even the guid here doesn't contain anything suspect.
Anyway, not expecting any answer.

Kind of a paradox that I'm finding next to impossible to sort a csv file on the command line, but Import the file to Google, LibreOffice, and click on the header and its sorted.

[aplaice]

Isn't ^I simply the control code for the tab character?

It's weird — when I open the converted out.tsv in LibreOffice it correctly recognises 18 fields (do you have merge delimiters off?).

[boydkelly]

Not sure at this point. But I have got things working by swapping the , with a ~. My suspicions about sort were confirmed by a rather detailed answer to the coreutils mailing list. Its really kind of too bad when these basic tools have been surpassed in functionality by python, libreoffice, and google docs etc. But there you go... Linux gnu sort won't work with embedded commas.

When using the comma ',' character as separator, then the data in the
literally goes until the first comma:

"}~
"wjv(
ARm}q0bjb#

The --debug option shows that as well:

$ sort --debug -t "," -k 2 test2.csv
sort: text ordering performed using simple byte comparison
"}~,)4",nɛgɛso,yq,,vélo,,,,,0630
_______________________________

---

"wjv(,4q_S}",badenmuso,cj,,soeur,,,,,"0630, fam, gens"
________________________________________________

---

ARm}q0bjb#,a be baara kɛ.,bk,,il travaille.,,,,,"0630, phrase"
____________________________________________________

---

In that sense, it is not foreseen that the delimiter character is part of
the fields. Obviously, the data comes from a tool which tries to work around
this by enclosing such data with a " as string delimiter, but that is not what
sort(1) is expecting. And to my knowledge, there's no way to tell sort that
it should treat the delimiter character somewhere in there special.

Instead, you should use a delimiter character which does or can not occur in
the original data; e.g. a tab '\t', a semicolon ';', a hash '#', a '@' or
maybe even a non-printable character.

Alternatively, you could also enforce that all column values get quoted by "....":

"}~,)4","nɛgɛso","yq","","vél","","","","","0630"
"wjv(,4q_S}","badenmuso","cj","","soeur","","","","","0630, fam, gens"
"ARm}q0bjb#","a be baara kɛ.","bk","","il travaille.","","","","","0630, phrase"

and then use the decorate-sort-undecorate (DSU) approach by changing "," (quote-comma-quote)
to a single and safe delimiter, sort by that delimiter, and then change the delimiter back
to ",".

$ sed 's/","/\t/g' < test2.csv-all-columns-quoted
| sort -t $'\t' -k2,2
| sed 's/\t/","/g'
"ARm}q0bjb#","a be baara kɛ.","bk","","il travaille.","","","","","0630, phrase"
"wjv(,4q_S}","badenmuso","cj","","soeur","","","","","0630, fam, gens"
"}~,)4","nɛgɛso","yq","","vél","","","","","0630"

Still, this is only working under certain assumptions. E.g. how does it look when the
original data has a "," inside? Or what if a field contains a newline character?
An CSV export is not really optimal for this.

If you have the data e.g. in LibreOffice Calc, the best bet is maybe to use the CSV export
with "fixed column width" (which is not really comma-separated anymore), and then sort by
specific offset-numbers

}~,)4 nɛgɛso yq vélo 630
wjv(,4q_S}badenmuso cj soeur 0630, fam, gen
ARm}q0bjb#a be baara kɛbk il travai 0630, phrase

Then running sort with the DSU idiom:

$ tr '\n' '\0' < test-with-fixed-column-width.csv
| sort -z -k 1.11,1.23
| tr '\0' '\n'

Of course, this still only works when none of the fields contain a newline (which is a ambiguity
in the input, not in sort).

Have a nice day,
Berny

[ohare93]

Feature added #35 👍 Lemme know if there's any issues