How to convert tab delimited file to .csv file

dtidke · January 9, 2009, 8:37am

Hi,

Can any one please help me in converting a tab delimited file in .csv file.

Records in my file are similar to mentioned below:

DET 001 0201 AC032508970 01478E1X8
DET 002 0202 AC032508971 01478E1X8

Could any one please suggest me what approach would be more suitable for this or if there is any direct method to convert flat file to .csv file.

Thanks and Regards,
Durwas

otheus · January 9, 2009, 9:22am

That's pretty easy. Awk would seem a likely candidate, but printing double-quotes in older AWKs can be difficult. If that's a problem for you, let us know.

awk -F'\t' '{ for (n=1; n <= NF; ++n) { if (n>1) printf(","); printf("\"%s\"",$n); } print ""; }'

Since this also quotes numbers, some of the columns might need to be converted to numeric. But generally, that's not the case.

arcadia · January 9, 2009, 9:33am

as usual, tr is your friend:

tr ' ' ';' < inputfile > outpufile

..if you want to check first

cat inputfile | tr ' ' ';'

..and if the file is actually tab deliminates use

tr '\t' ';' < inputfile > outputfile

otheus · January 9, 2009, 10:01am

Except that he needs commas, not semi-colons. Also, fields with commas will need to be double-quoted. It occurred to me that a much simpler version can be given with sed:

sed -e 's/^/"/; s/$/"/; s/\t/","/g;'

puertochico · April 7, 2009, 9:22am

The following script using awk fails in the first line of each csv file, what can i do to fix the problem?
#!/bin/bash
# list-glob.sh: Generating

[list]
in a for-loop, using "globbing"

echo

for file in *
# ^ Bash performs filename expansion
#+ on expressions that globbing recognizes.
do
ls -l "$file" # Lists all files in $PWD (current directory).
# Recall that the wild card character "*" matches every filename,
#+ however, in "globbing," it doesn't match dot-files.

# If the pattern matches no file, it is expanded to itself.
# To prevent this, set the nullglob option
#+ (shopt -s nullglob).
# Thanks, S.C.
done

echo; echo

for file in *xyz
do
awk -v dir=$PWD 'FS = "," {print NR";", $1";", $2";",$3";", FILENAME";", dir }' "$file" > $file.csv # Creates csv files in $PWD.
echo "Created file \"$file.csv\"".
done

echo

exit 0

puertochico · April 7, 2009, 1:42pm

puertochico:

The following script using awk fails in the first line of each csv file, what can i do to fix the problem?
#!/bin/bash
# list-glob.sh: Generating

[list]
in a for-loop, using "globbing"

echo

for file in *
# ^ Bash performs filename expansion
#+ on expressions that globbing recognizes.
do
ls -l "$file" # Lists all files in $PWD (current directory).
# Recall that the wild card character "*" matches every filename,
#+ however, in "globbing," it doesn't match dot-files.

# If the pattern matches no file, it is expanded to itself.
# To prevent this, set the nullglob option
#+ (shopt -s nullglob).
# Thanks, S.C.
done

echo; echo

for file in *xyz
do
awk -v dir=$PWD 'FS = "," {print NR";", $1";", $2";",$3";", FILENAME";", dir }' "$file" > $file.csv # Creates csv files in $PWD.
echo "Created file \"$file.csv\"".
done

echo

exit 0

Sorry
I got it
awk -v dir=$PWD 'BEGIN { FS="," } {print NR";", $1";", $2";",$3";", FILENAME";", dir }'