shell script to batch replace specific string in .csv file

Question

I want to replace some strings in my raw csv file for further use and I search for the internet and create the script so far. But it seems they doesn't work. Hope anyone can help me

The csv file is like this and I want to delete "^M" and "# Columns: " so that I can read my file.

# Task: bending1^M
# Frequency (Hz): 20^M
# Clock (millisecond): 250^M
# Duration (seconds): 120^M
# Columns: time,avg_rss12,var_rss12,avg_rss13,var_rss13,avg_rss23,var_rss23^M

#!/usr/bin/env bash
function scandir(){
cd `dirname $0`
echo `pwd`
local cur_dir parent_dir workir
workdir=$1
cd ${workdir}

if [ ${workdir}="/" ]
then 
    cur_dir=""
else
    cur_dir=$(pwd)
fi

for dirlist in $(ls ${cur_dir})
do
    if test -d ${dirlist}
    then
        cd ${dirlist}
        scandir ${cur_dir}/${dirlist}
        cd ..
    else
        vi ${cur_dir}/${dirlist} << EOF
        :%s/\r//g
        :%s/\#\ Columns:\ //g
        :wq
        EOF
    fi
done
}

KamilCuk · Accepted Answer · 2019-06-16 19:12:51Z

1

Your whole script looks like just:

find "$workdir" -type f | xargs -n1 sed -i -e 's/\r//g; s/^# Columns://'

Notes to your script:

Check your scripts for validity on https://www.shellcheck.net/
The of << EOF here document is invalid. The closing word EOF has to start from the beginning of the line inside the script:

    vi ${cur_dir}/${dirlist} << EOF
    :%s/\r//g
    :%s/\#\ Columns:\ //g
    :wq
EOF
#^^ no spaces in front of EOF, also no spaces/tabs after EOF
# the whole line needs to be exactly 'EOF'

There cannot be any spaces, tabs in front of it. Also, I don't think vi is not the best tool to run substitutions on a file, also I don't know how it acts with tabs or spaces infront of :. You may want to try to run it without whitespace characters in front of ::

    vi ${cur_dir}/${dirlist} << EOF
:%s/\r//g
:%s/\#\ Columns:\ //g
:wq
EOF

Backticks ` are deprecated, less readable and don't allow for easy nesting. Use $( ... ) command substitution instead.
echo `pwd` is just invalid use of echo, just use pwd.
for dirlist in $(ls parsing ls output is bad. Use find command instead, or if you have to, shell globulation, ie. for dirlist in *.
if [ ${workdir}="/" ] is invalid. This tests if the string "${workdir}=/ is not null. Bash is space aware, it needs a space between = and operands. It should be if [ "${workdir}" = "/" ].
Always quote your variables. Don't cd ${dirlist} do cd "${dirlist}" and so on.

answered Jun 16, 2019 at 19:12

KamilCuk

146k8 gold badges84 silver badges154 bronze badges

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

2 Comments

Sigi Over a year ago

xargs is not necessary (find can execute commands), and the idiomatic way to skip/delete lines in sed is to use /pattern/d

Zixi Liu Over a year ago

Great! Thank you so much!

F. Hauri - Give Up GitHub · Accepted Answer · 2019-06-16 19:49:34Z

1

Well posted answer are corrects, but I would recommand this syntax:

find "$1" -type f -name '*.csv' -exec sed -e 's/\r$//;s/^# Columns: //' -i~ {} +

Using + instead of \; at end of find command will permit sed to work on many files at once, reducing forks and make whole job quicker.
The ~ after -i option will rename existing files by appending tilde at end of names instead of deleting them.
Using -type f will ensure working on files only (no symlinks, dirs, socket, fifos, devices...)

edited Jun 16, 2019 at 19:49

answered Jun 16, 2019 at 19:44

F. Hauri - Give Up GitHub

72.9k19 gold badges136 silver badges154 bronze badges

Comments

Sigi · Accepted Answer · 2019-06-16 19:39:34Z

0

You can reduce the entire script to one command, and you do not have to use Vim to process the files:

find ${workdir} -name '*.csv' -exec sed -i -e 's/\r$//; /^#/d' '{}' \;

Explanation:

find <dir> -name <pattern> -exec <command> \; will search <dir> for files matchingand execute` on each file. You want to search for CSV files and do something with them (run a command on them).
The command run on every (CSV) file that was found will be sed -i -e 's/\r$//; /^#/d'. This means to edit the files in-place (-i) and run two transformations on them. s/\r$// will remove the ^M from each line, and /^#/d will remove all lines that start with a #.
'{}' is replaced with the files found by find and \; marks the end of the command run by find (see the find manual page for this).

Most of your script emulates part of the find command. That is not a good idea.

Also, for simple text processing, it is easier and faster to use sed instead of invoking an editor such as Vim.

edited Jun 16, 2019 at 19:39

answered Jun 16, 2019 at 19:32

Sigi

1,88414 silver badges19 bronze badges

2 Comments

F. Hauri - Give Up GitHub Over a year ago

Consider + end tag to -exec option of find command, instead of \;

Sigi Over a year ago

@F.Hauri Yes, that would be slightly more efficient because it would invoke sed only once for all the files; however, I wanted to make the example as simple as possible and not use the more advanced functionality.

Atul · Accepted Answer · 2023-10-16 06:10:08Z

0

For me this works:

I want to replace 'some string' with 'something'. So I used this command to replace the text into the csv file.

sed -i '' 's/old string/new string/g' data.csv

answered Oct 16, 2023 at 6:10

Atul

3,45734 silver badges47 bronze badges

Collectives™ on Stack Overflow

shell script to batch replace specific string in .csv file

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related