Change csv file delimiter in unix

Question

I have a number of .csv files with tab delimiter and now I want to change the delimiter. When I use the below script it does not change the file. Please help me with the script.

file_mask=$1
from_delim=$2
to_delim=$3
for i in `ls $file_mask*`
do
sed -i 's|$from_delim|$to_delim|g' $i
done

What errors are you getting? Note that $from_delim and $to_delim in sed won't be evaluated unless you use double quotes " — fedorqui
– fedorqui, Commented Oct 9, 2013 at 11:57

Anne van Rossum · Accepted Answer · 2017-01-11 12:34:24Z

14

Do not use sed or tr for this, use csvtool (available from common repositories). If you have TABs in strings the above simple tools will choke. You will need to use awk to start counting quotes, etc. It's gonna be a mess with these basic tools.

To replace TABs with e.g. semi-colons with csvtool including all corner cases is simple:

csvtool -t TAB -u ';' cat $ifile -o $ofile

With $ifile the input file and $ofile the output file.

answered Jan 11, 2017 at 12:34

Anne van Rossum

3,1871 gold badge37 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

devnull · Accepted Answer · 2013-10-09 11:58:27Z

9

You can use tr:

tr '\t' ',' < inputfile > outfile

(assuming that , is the new delimiter)

answered Oct 9, 2013 at 11:58

devnull

124k33 gold badges247 silver badges234 bronze badges

1 Comment

VIET THANG VU Over a year ago

This can potentially damage the file if there is a TAB in a field

VIET THANG VU · Accepted Answer · 2019-11-25 15:22:15Z

5

I recommend using csvkit: https://csvkit.readthedocs.io/en/latest/. With csvformat that comes with the package, you can safely change delimiter.

csvformat -D '[your delimiter here]' [file name]

answered Nov 25, 2019 at 15:22

VIET THANG VU

1581 silver badge4 bronze badges

Comments

glenn jackman · Accepted Answer · 2013-10-09 12:59:21Z

3

You need to use double quotes for the sed body so the shell variables can be expanded:

sed -i "s|$from_delim|$to_delim|g" $i

Better hope neither $from_delim nor $to_delim contain a pipe.

Also, don't parse ls -- don't need the for loop at all:

sed -i "s|$from_delim|$to_delim|g" ${file_mask}*

verify differences:

file_mask=$1
from_delim=$2
to_delim=$3

sed -i.bak "s|$from_delim|$to_delim|g" $file_mask*

for f in $file_mask*; do
    diff -q $f $f.bak >/dev/null
    if (( $? != 1 )); then
        echo "no changes made to $f"
    fi
done

edited Oct 9, 2013 at 12:59

answered Oct 9, 2013 at 11:58

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

4 Comments

Dileep Over a year ago

Thanks @glenn jackman. it's working. but i have doubt when i use this sed -i 's|\t|^|g' it is changing the files. why not in loop?

glenn jackman Over a year ago

You don't need the loop because if you give multiple files to sed -i it will edit each one in turn: sed loops over the files by itself.

Dileep Over a year ago

Okay. after changing the files, i want to do a scan as well whether the file is changed or not. so that i am very sure about the number of files changed as well. can you help me how should i use if condition.

Anne van Rossum Over a year ago

If the delimiter is quoted within a field a mere sed or tr will be not sufficient. You should have balanced quotes you might still be able to use sed but it becomes cumbersome: unix.stackexchange.com/questions/48672/…. Better to use csvtool e.g.

Collectives™ on Stack Overflow

Change csv file delimiter in unix

4 Answers 4

Comments

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related