[SOLVED] How to display a CSV file in a nice format?

kwatts60 · 05-06-2020, 12:13 PM

I have a python program that issues the LINUX "gedit" command to open up a csv file for display.
The gedit command opens up a window and displays the file, but the output is hard to read.

Is there some other editor that can use to open up a csv file and display it nicely?

shruggy · 05-06-2020, 12:23 PM

I prefer formatting CSV with miller and/or browsing them with VisiData, but I'm not a GUI guy.

sevendogsbsd · 05-06-2020, 12:27 PM

If you are looking for a graphical application: Libreoffice, Gnumeric, probably many others.

GPGAgent · 05-06-2020, 12:29 PM

Quote:

Originally Posted by shruggy

I prefer formatting CSV with miller or browsing them with VisiData, but I'm not a GUI guy.

Both look good to me and I've added them to my toolkit. Cheers

teckk · 05-06-2020, 01:06 PM

Quote:

I have a python program that issues the LINUX "gedit" command to open up a csv file for display.

If you are using python then use python.
https://docs.python.org/3/library/csv.html

remmilou · 05-06-2020, 03:44 PM

You should definitely give "tad" a try. Tad = tabular data viewer.
https://www.tadviewer.com/
I use it for ridiculously big (multiple millions of lines) csv files and it runs smoothly.

kwatts60 · 05-06-2020, 08:57 PM

I decided to go with Libreoffice. It's like Excel for LINUX.
Works ok with CSV files but it has an annoying import dialog box.
I guess I can live with it for now.
Thanks for your help.

WideOpenSkies · 05-07-2020, 10:22 PM

Have you considered using awk?

shruggy · 05-08-2020, 01:54 AM

@Contrapak Doing it right with awk would require using FPAT though, which is a feature exclusive to gawk.

syg00 · 05-08-2020, 02:46 AM

I would have though LibreOffice was a massive over-reach - a sledgehammer nut-cracker.
And really, for a site-specific problem what is the issue with using gawk ?. I long ago gave up fretting about POSIX compliance - I need solutions that work in my environment, not some "feel-good" resolution. Anything that makes my life easier is good. Gotta get into miller tho', I love the look of that as I've said before.

Turbocapitalist · 05-08-2020, 04:03 AM

Quote:

Originally Posted by shruggy

@Contrapak Doing it right with awk would require using FPAT though, which is a feature exclusive to gawk.

Covering all the cases is quite difficult and benefits from a parser. Consider the following line:

Code:

1, "b", "c1,c2", "d1,d2,d3", "e", 6,7, 8,"i",9,"""j""", 11, """l,m""", 13,14

With a tab-delimited file or certain types of comma-separated values, it is doable with AWK. In some cases it is even easy. But when you start getting quotes and commas in the data itself, it quickly becomes a very difficult task for AWK.

WideOpenSkies · 05-08-2020, 10:55 AM

Quote:

Originally Posted by Turbocapitalist

With a tab-delimited file or certain types of comma-separated values, it is doable with AWK. In some cases it is even easy. But when you start getting quotes and commas in the data itself, it quickly becomes a very difficult task for AWK.

Perhaps you can use a combination of substitution and sed commands to get around the tabs and quotes? From your sample, it seems like separating by commas is possible.

shruggy · 05-08-2020, 11:03 AM

@Contrapak. Sorry, but sed is even worse in this respect than awk.

Quote:

Originally Posted by Contrapak

From your sample, it seems like separating by commas is possible.

Ok, let's try it. Here is a real parser (csvtool):

Code:

$ csv='1, "b", "c1,c2", "d1,d2,d3", "e", 6,7, 8,"i",9,"""j""", 11, """l,m""", 13,14'
$ csvtool transpose - <<<"$csv"
1
b
"c1,c2"
"d1,d2,d3"
e
6
7
8
i
9
"""j"""
11
"""l,m"""
13
14

I challenge you to do the same with

Quote:

a combination of substitution and sed commands

@syg00. I made an example that demonstrates how miller compares to awk.

shruggy · 05-08-2020, 03:49 PM

Answering my own challenge

. Well, it is possible to do this particular transformation on this particular set of input data with sed. Moreover, throwing wc and pr into the mix, I could do it even on a multi-line set. Not that I would really like doing it in this manner:

Code:

#!/bin/bash
line(){ printf =%.0s {1..76};echo;}
csv='1, "b", "c1,c2", "d1,d2,d3", "e", 6,7, 8,"i",9,"""j""", 11, """l,m""", 13,14'
csv="$csv
$(rev <<<"$csv")"
line
printf %s\\n "$csv"
line
echo
echo == csvtool ==
csvtool transpose - <<<"$csv"

echo
echo == wc + sed + sed + pr ==
nr=$(wc -l <<<"$csv")
sed -E 's/("+)[ \t]*,/\1,/g;s/,[ \t]*("+)/,\1/g;s/,"/\n"/g;s/",/"\n/g' <<<"$csv"|
sed '/^[^"]/{s/^[ \t]*//;s/[ \t]*$//;s/[ \t]*,[ \t]*/\n/g}'|pr -$nr -T -s,

fatmac · 05-09-2020, 04:47 AM

Quote:

....command to open up a csv file for display.

The sc (spreadsheet calculator) program can do that for you.