Start Free Trial

asked on

(URGENT) Read CSV File using UNIX SHELL SCRIPT

Hi Experts,
I want shell script to read values in CSV(comma seperated) file. But not a perl script. I need shell script.

CSV file will looks as follows:
------------------------------------------------
user1, password1, homedir1
user2, password2, homedir2
user3, password3, homedir3
-------------------------------------------------
I want read line by line at once.
eg: in the first loop $user, $password and $homedir variable values should like
$user="user1" and $password="password1" and $homedir="homedir1"

in the second loop $user, $password and $homedir variable values should like
$user="user2" and $password="password2" and $homedir="homedir2"

like ..etc

BR Dushan

Here's the sample codes:

#!/bin/ksh
INPUTFILE=/path-to/datafile

# We need to get rid of the "," in the files.

tr -d , <$INPUTFILE >${INPUTFILE}.tmp

# read the file
exec 0<${INPUTFILE}.tmp
while read user password homedir
do
# do something, eg
echo "Current user = $user"
echo "Password = $password"
echo "homedir = $homedir"
done

# clean up
rm ${INPUTFILE}.tmp
exit
# End of script

ASKER

Thanks again for your help yuzh !

But its says

": bad interpreter: No such file or directory"

when I try to run it.

BR Dushan

You need to replace /path-to/datafile with the real path and filename for your
input file!

also please chech if you have ksh installed on your system,
type in:
which ksh

or modify your script to use sh

#!/bin/sh
set -x
INPUTFILE=/path-to/datafile

# We need to get rid of the "," in the files.

tr -d , <$INPUTFILE >${INPUTFILE}.tmp

# read the file
exec 0<${INPUTFILE}.tmp
while read user password homedir
do
# do something, eg
echo "Current user = $user"
echo "Password = $password"
echo "homedir = $homedir"
done

# clean up
rm ${INPUTFILE}.tmp
exit
# End of script

If you have problem, post the output on your screen.

tacticalvehicle

#!/bin/bash

awk -F\, ' { print $1" "$2" "$3 } ' $1 > "$1.tmp"
while read USR PAS HD
do
echo $USR;
echo $PAS;
echo $HD;
echo "------------";
done <"$1.tmp";
rm "$1.tmp";
-------------------------- tested ----------------

$ . test.sh cvs.txt
user1
password1
homedir1

------------
user2
password2
homedir2

------------
user3
password3
homedir3

ASKER

Hi yuzh,

I have typed "which ksh" on my command line. Result is "/bin/ksh". I have reaplced "#!/bin/sh" with "/bin/ksh". But still same error getting.
And second suggestion also giving same error.

tacticalvehicle >
Also having same problem.

Thanks! Plz help me.

BR Dushan

ASKER

And I have set INPUTFILE path correctly.
----------------------------------
#!/bin/ksh
set -x
INPUTFILE="/home/share/scripts/reverse/csv/datafile.txt"
# We need to get rid of the "," in the files.
tr -d , <$INPUTFILE >${INPUTFILE}.tmp
# read the file
exec 0<${INPUTFILE}.tmp
while read user password homedir
do
# do something, eg
echo "Current user = $user"
echo "Password = $password"
echo "homedir = $homedir"
done

# clean up
rm ${INPUTFILE}.tmp
exit
# End of script
----------------------------------

BR Dushan

ASKER

Still same error is comming......

I comment out "set -x" and test it:
yuzh@mybox[/home/yuzh/test] 152# ./myscript

screen output:

Current user = user1
Password = password1
homedir = homedir1
Current user = user2
Password = password2
homedir = homedir2
Current user = user3
Password = password3
homedir = homedir3

It works fine for me. How did you run the script?

ASKER

[root@linda1 csv]# ./read_csv.sh

Also I commented "set -x" and tested. But same error getting.

BR Dushan

ASKER

[root@linda1 csv]# ls -l
total 16
-rwxrwxrwx 1 xib xib 84 Jul 11 10:38 datafile.txt
-rw-r--r-- 1 root root 84 Jul 11 14:11 datafile.txt.tmp?
-rwxrwxrwx 1 xib xib 438 Jul 11 14:54 read_csv.sh
-rw-r--r-- 1 root root 29 Jul 11 13:15 t1.txt?
[root@linda1 csv]# ./read_csv.sh
: bad interpreter: No such file or directory

ASKER

I can run other scripts properly.

[root@linda1 reverse]# pwd
/home/share/scripts/reverse
[root@linda1 reverse]# ls -l
total 96
-rw-r--r-- 1 root root 613 Jul 9 17:13 ?
-rwxrwxrwx 1 xib xib 303 Jul 9 10:57 1.sh
-rw-r--r-- 1 root root 891 Jul 11 14:59 bb.txt
-rw-r--r-- 1 root root 31 Jul 9 12:21 b.txt
-rw-r--r-- 1 root root 31 Jul 9 12:11 cc.txt
-rwxrwxrwx 1 root root 1549 Jul 7 17:16 checkftp_1.sh
-rwxr--r-- 1 xib xib 2158 Jul 9 21:01 checkftp_2.sh
-rwxr--r-- 1 xib xib 2557 Jul 11 12:39 checkftp.sh
-rwxrwxrwx 1 xib xib 1654 Jul 7 12:58 CreateBulkUsers.pl
drwxr-xr-x 2 xib xib 4096 Jul 11 14:11 csv
-rwxr--r-- 1 xib xib 42 Jul 11 10:35 datafile.txt
-rwxrwxrwx 1 xib xib 51 Jul 9 20:07 data.txt
-rw-r--r-- 1 root root 0 Jul 9 18:09 grep
-rwxrwxrwx 1 xib xib 202 Jul 7 12:59 iuser.txt
-rwxrwxrwx 1 xib xib 178 Jul 9 17:13 t1.sh
-rw-r--r-- 1 root root 757 Jul 11 15:01 t1.txt
-rw-r--r-- 1 root root 19989 Jul 9 18:24 t2.txt
-rwxr--r-- 1 xib xib 222 Jul 9 11:45 testftp_back.sh
-rwxrwxrwx 1 root root 786 Jul 9 10:55 testftp.sh
-rwxrwxrwx 1 xib xib 205 Jul 9 11:43 t.sh
-rw-r--r-- 1 root root 38 Jul 9 12:18 user
[root@linda1 reverse]# ./checkftp_2.sh
[root@linda1 reverse]#

ASKER

#-----------------------------checkftp_2.sh------------------------------------

PROD_TEST="live test"

USER_DIR_PREFIX="/data/NMS"

USER_DIRS="FromNms/conshipmentorder \
FromNms/prealert \
FromNms/responsercpt \
FromNms/shipmentorder \
ToNms/conshipmentorder \
ToNms/prealert \
ToNms/responsercpt \
ToNms/shipmentorder"

USER_SUBDIRS="bad build proc work"

MKDIR=`which mkdir`
FTP=`which ftp`

echo "================================================================" >t1.txt
echo " NMS FTP CONNECTION TESTING on 192.168.1.216 STARTED " >>t1.txt
echo "================================================================" >>t1.txt


#      for USER_DETAILS in $USER_PWD; do
      echo "----------------------------------------------------------------" >>t1.txt
echo " NMS FTP CONNECTION TESTING on $USER_DETAILS USER STARTED " >>t1.txt
echo "----------------------------------------------------------------" >>t1.txt

      {
      echo "open 192.168.1.216
            user ftp_user user_ftp"

            for DIR_TYPE in $PROD_TEST; do
                  for CURR_DIR in $USER_DIRS; do
                        for SUBDIR in $USER_SUBDIRS; do
                              TODO_DIR="$CURR_USER/$DIR_TYPE/$CURR_DIR/$SUBDIR"
                                    echo "cd $TODO_DIR"
                                    echo "put datafile.txt"
                                    echo "rename datafile.txt datafile1.txt"
                                    echo "delete datafile1.txt"

                                    if [ ! -d $TODO_DIR ]; then
                                          #echo "Changing $TODO_DIR" >> $LOG_FILE
                                          #cd $TODO_DIR

                                                if [ $? -ne 0 ]; then
                                                echo " Failed to create directory." >> $LOG_FILE
                                                fi
                                          fi
                        done
                  done
            done
            echo "close"
      } | ftp -i -n -v 2>&1 | egrep " No such file or directory| denied" >> /home/share/scripts/reverse/t1.txt
            echo "----------------------------------------------------------------" >>t1.txt
            echo " NMS FTP CONNECTION TESTING on $USER_PWD USER FINISHED " >>t1.txt
            echo "----------------------------------------------------------------" >>t1.txt
      #done
echo "================================================================" >>t1.txt
echo " NMS FTP CONNECTION TESTING on 192.168.1.216 FINISHED " >>t1.txt
echo "================================================================" >>t1.txt

ASKER

I have removed first line "#!/bin/sh" . And tried.

[root@linda1 csv]# ls -l
total 16
-rwxrwxrwx 1 xib xib 84 Jul 11 10:38 datafile.txt
-rw-r--r-- 1 root root 84 Jul 11 14:11 datafile.txt.tmp?
-rwxrwxrwx 1 xib xib 417 Jul 11 15:04 read_csv.sh
-rw-r--r-- 1 root root 29 Jul 11 13:15 t1.txt?

[root@linda1 csv]# pwd
/home/share/scripts/reverse/csv

[root@linda1 csv]# ./read_csv.sh
: No such file or directorye/share/scripts/reverse/csv/datafile.txt
: No such file or directorye/share/scripts/reverse/csv/datafile.txt
./read_csv.sh: line 19: syntax error: unexpected end of file

[root@linda1 csv]#

check any typo in the script:

1)eg:
INPUTFILE="/home/share/scripts/reverse/csv/datafile.txt"

not
INPUTFILE=/share/scripts/reverse/csv/datafile.txt

in the datafile.txt dir, type in:
pwd
to verify the path to the file.

2) make sure

tr -d , <$INPUTFILE >${INPUTFILE}.tmp

NOT

tr -d , <$INPUTFILE >${INPUTFILE}.tmp

you have a "datafile.txt.tmp?" file !!!

Good luck and good night!

ASKER

1.) Its in correct path.
[root@linda1 csv]# ll /home/share/scripts/reverse/csv/datafile.txt
-rwxrwxrwx 1 xib xib 84 Jul 11 10:38 /home/share/scripts/reverse/csv/datafile.txt

2.) I didn't get you. both things you have mentioned same......

Yes.

[root@linda1 csv]# pwd
/home/share/scripts/reverse/csv
[root@linda1 csv]# ls -l
total 16
-rwxrwxrwx 1 xib xib 84 Jul 11 10:38 datafile.txt
-rwxrwxrwx 1 root root 84 Jul 11 14:11 datafile.txt.tmp?
-rwxrwxrwx 1 xib xib 417 Jul 11 15:04 read_csv.sh
-rwxrwxrwx 1 root root 29 Jul 11 13:15 t1.txt?
[root@linda1 csv]#

Badly something is missing, still same error.... :(

That's way too complicated. Try this:

#!/bin/ksh
tr "," " " </home/share/scripts/reverse/csv/datafile.txt | while read USR PWD HOM
do
echo "User=$USR, Password=$PWD, Home=$HOM"
done

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi bpmurray,
I'm getting same error. :(

[root@linda1 csv]# pwd
/home/share/scripts/reverse/csv
[root@linda1 csv]# ls -l
total 16
-rwxrwxrwx 1 xib xib 84 Jul 11 10:38 datafile.txt
-rwxrwxrwx 1 root root 84 Jul 11 14:11 datafile.txt.tmp?
-rwxrwxrwx 1 xib xib 152 Jul 11 15:39 read_csv.sh
-rwxrwxrwx 1 root root 29 Jul 11 13:15 t1.txt?
[root@linda1 csv]# ./read_csv.sh
: bad interpreter: No such file or directory
[root@linda1 csv]#

ASKER

Hi bpmurray,
I have removed "#!/bin/ksh" line.

Then I'm getting

./read_csv.sh: line 5: syntax error near unexpected token `done'
./read_csv.sh: line 5: `done'

OK, there are two possible reasons for the "bad interpreter: No such file or directory" message, which is from bash:

1. It can't find the shell which seems odd. Instead of having #!/bin/bash or similar at the top of the script, put "#!/usr/bin/bash"
2. The end of line is in DOS format - have you created the shell file in a DOS or Windows machine? If so, use:
dos2unix read_csv.sh

Which shell are you using? What does echo $SH say?

Did you copy the entire text, with quotes, etc.? The standard format for while is
while xxx
do
...
done

so "done" should not be unexpected unless something isn't quite right elsewhere

Just to double-check that nothing is going too wrong, can you do the following, and post the output here:

cat -v read_csv.sh

ASKER

[root@linda1 csv]# pwd
/home/share/scripts/reverse/csv

[root@linda1 csv]# ls -l
total 16
-rwxrwxrwx 1 xib xib 84 Jul 11 10:38 datafile.txt
-rwxrwxrwx 1 root root 84 Jul 11 14:11 datafile.txt.tmp?
-rwxrwxrwx 1 xib xib 188 Jul 11 15:59 read_csv.sh
-rwxrwxrwx 1 root root 29 Jul 11 13:15 t1.txt?

[root@linda1 csv]# which ksh
/bin/ksh

[root@linda1 csv]# cat -v read_csv.sh
#!/bin/ksh^M
# This line would actually be coming from the program:^M
tr "," " " <$1 | while read user password homedir^M
do^M
echo "User=$user, Password=$password, Home=$homedir"^M
done^M

[root@linda1 csv]#

ASKER

[root@linda1 csv]# ./read_csv.sh
: bad interpreter: No such file or directory

OK - I can see the problem. You have created this file on DOS or Windows. Do the following:

Run
dos2unix read_csv.sh

It'll display "read_csv.sh: done" when it's ready - a couple of seconds. Then try running it again

ASKER

I have put following , with removing first two lines.
---------------------------------------------------------------------------------
tr "," " " <$1 | while read user password homedir
do
echo "User=$user, Password=$password, Home=$homedir"
done
---------------------------------------------------------------------------------
Then

[root@linda1 csv]# ./read_csv.sh
./read_csv.sh: line 5: syntax error: unexpected end of file

Have you tried running dos2unix? It will not work until you do that.

ASKER

Yes. Finally it got worked. :)
Output came as
---------------------------------------------------------------
User=user1, Password=password1, Home=homedir1
User=user2, Password=password2, Home=homedir2
User=user3, Password=password3, Home=homedir3
---------------------------------------------------------------

Thank you very much for your Great help!

BR Dushan

Whew!
Watch out in the future - if you create a file on a DOS/Windows machine, it remains in that format until you run dos2unix on it. A giveaway that this is the problem is when you run "cat -v file" you can see "^M" at the end of the lines. What happens then is that the shell includes this character (CR) in the line, so "done" becomes "done^M", and the shell from "#!/bin/ksh" becomes "#!/bin/ksh^M" which doesn't exist.