How can I convert tab delimited data to comma delimited data?
#!/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}'
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
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.