Problems sorting a CSV with sort

[Johnny Rosenberg]

2013/9/4 Johnny Rosenberg <gurus.knugum at gmail.com>

> 2013/9/4 Johnny Rosenberg <gurus.knugum at gmail.com>
>
>> 2013/8/9 Johnny Rosenberg <gurus.knugum at gmail.com>
>>
>>> 2013/8/9 Tony Arnold <tony.arnold at manchester.ac.uk>:
>>> > Johnny,
>>> >
>>> > Just played with csvtool.
>>> >
>>> > You could use it to replace the , field separators with TABs, say, pipe
>>> > through sort specifying TAB as the field delimiter and then pipe
>>> through
>>> > csvtool again to convert the fileds separators back to commas. E.g.:
>>> >
>>> > csvtool -t ',' -u TAB cat input.csv | sort -f 2 -t '    ' | csvtool -t
>>> TAB
>>> > -u ',' cat - > output.csv
>>> >
>>> > Not tried the above so it may need adjusting, but I think you should
>>> get
>>> > the idea.
>>> >
>>> > Regards,
>>> > Tony.
>>>
>>> Thanks! I'll take a closer look at that csvtool later. Looks promising
>>> so far, at least.
>>>
>>>
>>> Johnny Rosenberg
>>>
>>
>> Have been playing around with it now. It seems to work but there is one
>> downside: It doesn't seem to support pipes…
>> SomeCommand | csvtool -t ',' -u TAB | sort…    # Not supported…?
>>
>> Well, I don't need to use pipes, but it would be more convenient. Now I
>> need to create at least one temp file, which isn't a big problem, but
>> still… One can always wish for more…
>>
>>
>> Johnny Rosenberg
>>
>>
> My bad. Some kind of a writing before thinking thing, I guess, sorry for
> that. I just didn't notice that you actually piped csvtool in your example…
> So I'm going to experiment more with this right now. It seems like it
> actually does exactly what I want to do…
>
>
> I was also searching for more information and found something called
> ”csvtools”. It seems to be some kind of collection of csv tools, such as
> converters to and from csv and tsv (csv2tsv and tsv2csv). It isn't
> available in the repositories, so I think I stick with csvtool for a while,
> at least.
>
> https://github.com/ceumicrodata/csvtools
>
>
>
> Johnny Rosenberg
>

After some experimenting I finally implemented csvtools in my script and it
works perfectly! Thanks for the suggestion!

So what was I actually trying to do?
Well, I have a spreadsheet (created with Apache OpenOffice Calc) and I need
access to one of its sheets with my Android phone (Nexus 4).
Since there doesn't seem to be any decent ODF (Open Document Format)
readers for Android out there, I figured that I could save as csv and then
read the file with a csv reader on my Nexus. I found a csv reader and it
only supports csv (Comma Separated Values), not tsv (Tab Separated Values).

On the other hand, when saving as csv in Apache OpenOffice Calc, I want to
do that with a minimum of effort, just accepting its default settings
(which means commas for separation). I could of course write a macro in
Apache OpenOffice Calc to save as csv with tab separated values, but my
decision was to solve that minor problem in my bash script instead.

The spreadsheet isn't sorted, I just add posts in Apache OpenOffice Calc, I
don't sort them.

However, when viewing my csv, I want it to be sorted by first, then third
and finally second column, and the problem was that the second column
sometimes contains commas. Apache OpenOffice Calc makes automatically sure
that cells with commas are automatically surrounded by double quotes, but
the sort command in my bash script doesn't care about that, obviously…

But converting to tsv, sorting and converting back again works, even though
it feels a bit like a bad workaround having to convert back and forth…


So whenever I made changes to my spreadsheet, I can easily transfer the
changes to my Nexus with these steps:
1. Select the right sheet in Apache OpenOffice Calc.
2. Save as csv in the same folder as the original ODF.
3. I placed my script in the very same folder, so now I only need to double
click it and click ”Run”.
4. If my Nexus isn't connected, a simple dialogue (yad) pops up and tells
me to connect it to my computer within 60 seconds (and the dialogue is also
counting down).
5. I connect my Nexus if it wasn't already connected. Another dialogue
tells me whether the operation failed or succeeded.
6. I can now view the result in my Nexus after reloading the csv into the
csv reader (called ”CSV Viewer”).

Actually, CSV Viewer can sort the file for me, I don't remember why I don't
use it, but it was probably too slow or something, so I kept sort disabled.
It's probably wise to perform the time consuming tasks with the PC…
The csv is currently 1122 lines and each line is 8 columns.



Johnny Rosenberg
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.ubuntu.com/archives/ubuntu-users/attachments/20130904/62d2d70d/attachment.html>