Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I'm requesting a list of ec2 snapshots via amazon's ec2 command line tool:

ec2-describe-snapshots -H --hide-tags > snapshots.csv

The data looks something like this:

SnapshotId      VolumeId        StartTime   OwnerId         VolumeSize  Description
snap-00b66464   vol-b99a38d0    2012-01-05  5098939         160         my backup

How can I intercept the data before redirecting it to snapshots.csv and do the following things:

  • replace "tabs" with commas
  • encapsulate values with quotations
  • if a value is all numbers, prefix it with an = so that excel will treat it as text - for example OwnerId should be "=5098939" (this one is not necessary if it cannot be done inline and would instead require a script file or function)

desired output:

"SnapshotId","VolumeId","StartTime","OwnerId","VolumeSize","Description"
"snap-00b66464","vol-b99a38d0","2012-01-05","=5098939","=160","my backup"
share|improve this question
    
This is where someone tells you to import using tabs. Or they would, if Excel wasn't on crack. –  Ignacio Vazquez-Abrams Jan 6 '12 at 22:46
    
Yeah I'm trying to help excel out a little bit since it doesn't seem to be doing so hot on it's own. Also having a CSV file that can just be opened instead of having to use the import menu command is always nice. I already tried changing the extension to ".tsv" with no luck. –  cwd Jan 6 '12 at 22:47
    
I think your desired output is a bit off. You have a lot of empty fields in there (the empty quotes). –  Patrick Jan 6 '12 at 23:20
    
@Patrick - thanks, fixed :) –  cwd Jan 7 '12 at 0:23
add comment

6 Answers

up vote 7 down vote accepted
#!/usr/bin/awk -f

BEGIN { FS = "\t"; OFS = "," }
{
    for(i = 1; i <= NF; i++) {
        if ($i + 0 == $i) { $i = "=" $i }
        else gsub(/"/, "\"\"", $i);
        $i = "\"" $i "\""
    }
    print
}

Assuming you name this convert.awk, you can either call with either

ec2-describe-snapshots -H --hide-tags | awk -f convert.awk > snapshots.csv

or (after adding execute permissions, chmod a+x convert.awk)

ec2-describe-snapshots -H --hide-tags | ./convert.awk > snapshots.csv

This will make a new column for each tab, which will keep the comment column together (unless it contains tabs), but add empty columns (though that is how your sample output looks, so maybe you actually do want that). If you want to split on all whitespace (this will collapse extra tabs within the table but put each word in the description as a new column), take out the FS="\t"; statement.

For future generations, if you don't need the "s or =s or embedded whitespace, you can make it a one-liner:

awk -v OFS=, '{$1=$1;print}'
share|improve this answer
    
Nice clean solution. Thought it would end up a lot uglier than that, but then I'm not a awk person :-) –  Patrick Jan 6 '12 at 23:52
    
so do i save this into a file such as ./convert.sh, chmod +x, and then pipe the input into it so that it will print the output? I'm getting an error: /usr/bin/awk: syntax error at source line 1 context is >>> . <<< /convert.sh. –  cwd Jan 7 '12 at 0:27
    
@cwd You can save it in a file, I'd suggest convert.awk to indicate it's an awk script and not a bash one. I updated the post with the full command line, and note that I added a -f flag I had forgotten to the first line (that tells it to interpret the file as commands). –  Kevin Jan 7 '12 at 0:39
add comment

Here's a perl solution. This might be possible with sed/awk, but testing for the numeric part would likely make it pretty ugly.

ec2-describe-snapshots -H --hide-tags | \
perl -e 'use Scalar::Util qw(looks_like_number);
         while (chomp($line = <STDIN>)) {
             print(join(",", map { "\"" . (looks_like_number($_) ? "=$_" :
                                           do {s/"/""/g; $_}) . "\"" }
             split(/\t/, $line)) . "\n");
         }' \
> snapshots.csv
share|improve this answer
add comment

Another Perl solution:

#!/usr/bin/perl -wln
use strict;

my($n,$s);chomp();
for $s ( split(/\t/,$_) )
{
    $s = '='.$s if ($s =~ /^\d+$/);
    $n.= '"'.$s.'",';
}
$n =~ s/(.*),/$1/;print $n;

invoke with ec2-describe-snapshots -H --hide-tags | /var/tmp/script.pl > output.txt

share|improve this answer
    
Scalar::Util isnt an outside module, it comes with standard perl. –  Patrick Jan 11 '12 at 12:22
    
True. Apologies for poorly wording my intended comment. Thank you for the correction. –  Jim Jan 13 '12 at 9:55
add comment

If you're just lazy like me and want to do it all on one command line without writing a script, here's how i'd do it.

ec2-describe-snapshots -H --hide-tags | sed -e 's/^I/","/g' | sed -e 's/^/"/' | sed -e 's/$/"/'> snapshots.csv

The ^I is made by pressing ctrl+v i.

The first sed swaps all the tabs for ",". The second sed inserts a " at the beginning of each line, and the last sed inserts a closing " at the end of each line.

share|improve this answer
    
How did you get the ctrl + v i to show up like that? –  Burhan Khalid Jan 7 '12 at 3:16
    
@burhan The syntax is <kbd>text</kbd>. –  jw013 Jan 7 '12 at 3:39
3  
Or in one line: sed -e 's/^I/","/g' -e 's/.*/"&"/' or even shorter sed -e 's/^I/","/g;s/.*/"&"/'. –  Arcege Jan 7 '12 at 19:31
add comment

This might work for you:

sed 's/\t\+/,/g;s/^\|$/"/g;s/,/"&"/g;s/"\([0-9]\+\)"/"=\1"/g' file
share|improve this answer
add comment

Here is another solution in gawk -

gawk '{for(i=1;i<=NF;i++) $i="\""$i"\""}1' FPAT="([^\t]+)|( [^ ]+ )" OFS=',' file

Test:

[jaypal:~/Temp] cat file
SnapshotId  VolumeId    StartTime   OwnerId VolumeSize  Description
snap-00b66464   vol-b99a38d0    2012-01-05  5098939 160     my backup

[jaypal:~/Temp] gawk '{for(i=1;i<=NF;i++) $i="\""$i"\""}1' FPAT="([^\t]+)|( [^ ]+ )" OFS=',' file
"SnapshotId","VolumeId","StartTime","OwnerId","VolumeSize","Description"
"snap-00b66464","vol-b99a38d0","2012-01-05","5098939","160","my backup"
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.