Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

csv to table one-liner

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting csv to table one-liner
# 1  
Old 05-18-2009
csv to table one-liner
I've googled a lot on this, but could not fine a simple one-liner to do this.
I have a .csv file that looks like this:
Code:
Header one
Header two
Header three
col1,col2,col3
short data, very long data, data

If I use sed and change the comma to tab, being the colums of variable length I don't get a readable table.
How can I do this with awk or perl? I understand this is quite a trivial question, but cannot figure it out. I've been using gnumeric to open it and it works fine, but for some reason it does not get the chinese characters in the file properly, even changing font. And actually, even if I could use gnumeric, I would like to learn to view the file quickly in terminal.
Thank you in advance
# 2  
Old 05-19-2009
pls paste your expecting result
# 3  
Old 05-19-2009
something like this:
Code:
Header one
Header two
Header three
col1  col2               col3
short datavery long data data

# 4  
Old 05-19-2009
Ok, it seems I'm getting there. Telling explicitly the width of the wider (wider than a tab) column, I get an aligned table.
Code:
BEGIN {
    FS=","
}
{printf "%s\t%-40s\t%s\n", $1,$2,$3}

But isn't there a way to let awk calculate the width of the columns according to the lenght of the data without needing to guess it manually? Shall I use perl instead?
# 5  
Old 05-19-2009
You can do something like that :
Code:
awk -F, '
{
   if (NF > max_fields) 
      max_fields = NF;
   for (f=1; f<=NF; f++) {
      field[NR, f] = $f;
      if (length($f) > field_length[f]) 
         field_length[f] = length($f);
  }
}
END {
   for (f=1; f<=max_fields; f++) {
      l = field_length[f];
      field_format[f] = "%-" l "." l "s";
   }
   for (r=1; r<=NR; r++) {
      out = "";
      for (f=1; f<=max_fields; f++) 
         out = out "!" sprintf(field_format[f], field[r, f]);
      print out "!"
   }
}      

' input.txt

Input file :
Code:
aaa1
bbbb1,bbbbb2,bbbbbbbbbb3
cc1,ccc2
,dddddddddddddd2
eeeeeeeeeee1

Output:
Code:
!aaa1        !               !           !
!bbbb1       !bbbbb2         !bbbbbbbbbb3!
!cc1         !ccc2           !           !
!            !dddddddddddddd2!           !
!eeeeeeeeeee1!               !           !

Jean-Pierre.
# 6  
Old 05-19-2009
Code:
awk -F, 'gsub(/,/,"\t")1' file


-Devaraj Takhellambam
# 7  
Old 05-19-2009
I thought this was a very trivial thing, but I see it is not. devtakh's solutiong gives me the same result I had with sed, but that gives columns of different width.
aigles' solution seems to be near what I want, but on the real file the space between the first and the second column is huge, but the others are fine. Only, this is not exactly a "one-liner" to me. Maybe I should give up this exercise...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Plain text table to csv problems

I´m trying to input a plain text table and I want to output a csv file with semicolon field separator. I have some problems with the \r and the fields with more of one line.. Some gnu util to do this without problems or awk solution? I´m attaching the original plain table file and the needed... (2 Replies)
Discussion started by: faka
2 Replies

2. Shell Programming and Scripting

Update the table using values from a csv file

i want to run update query for oracle which is in up.sql taking values from a.csv. I have implemented shell script to do it. extn="perl" ls -1 | while read file do echo "$file,$extn" > a.csv done up.sql contains update file_list set filename=$1 where extn=$2; The code to update is... (2 Replies)
Discussion started by: millan
2 Replies

3. Shell Programming and Scripting

Find and replace variables using a csv table

I have a flat file (template) where I want to replace variables based upon a value in another file (csv). The variables in the template are named %VAR_X_z% The values are in the csv file and X is field 0 of each line and y field 1 and up. Example of the csv: Badidas, 13.00, 12.00, 11.00,... (8 Replies)
Discussion started by: biscayne
8 Replies

4. Shell Programming and Scripting

Convert file in csv or table

Hi there, i have a file like that in attachment (PLEVA3_280711_SAP.txt), i would extract some basic information from it and report in a new file or table like this: i try to use bash and i extract the single object in this way (see attach scriptino.sh), but i receive a strange... (5 Replies)
Discussion started by: alen192
5 Replies

5. Shell Programming and Scripting

Search & Replace regex Perl one liner to AWK one liner

Thanks for giving your time and effort to answer questions and helping newbies like me understand awk. I have a huge file, millions of lines, so perl takes quite a bit of time, I'd like to convert these perl one liners to awk. Basically I'd like all lines with ISA sandwiched between... (9 Replies)
Discussion started by: verge
9 Replies

6. Shell Programming and Scripting

creating a csv file from this 1 liner?

I'm trying to create a csv file by running awk and sed on a number of xml files in a directory; I'm using this below: hostname; grep "BuildDate" /dir/ABCD/configuration/*/*.xml | awk -F"/" '{ print $5 }' > /tmp/tempfile.txt; grep "BuildDate" /dir/ABCD/configuration/*/*.xml | awk -F\" '{ print $2... (2 Replies)
Discussion started by: rich@ardz
2 Replies

7. UNIX for Dummies Questions & Answers

Storing data from a table into a csv file

Hi I need to write a bash script to take the data stored in 3 oracle tables .. and filter them and store the results in a csv file. It is an Oracle database Thank you (1 Reply)
Discussion started by: ladyAnne
1 Replies

8. Shell Programming and Scripting

Store table contents in csv file

I need to write a script to store the contents of a table in a csv file I'm using Toad, it's a Oracle database. (5 Replies)
Discussion started by: ladyAnne
5 Replies

9. Shell Programming and Scripting

HTML table to CSV

Hi !! I have HTML Tables through which i want to generate graphs, but for creating graphs i need the file in CSV format so can anyone can please help me in how can i convert my HTML table file to CSV format. Thanks in Advance (2 Replies)
Discussion started by: i_priyank
2 Replies

10. Shell Programming and Scripting

merging CSV data using a one liner from shell?

I'm trying to merge multiple CSV (comma separated value) files into one large master file. All files have a field that is unique to act as the key for entry/merging into the master file & and all files have the same number of fields that are in the master file. I'll give an example here: ... (2 Replies)
Discussion started by: jjinca
2 Replies
Login or Register to Ask a Question