Solved

(URGENT) Read CSV File using UNIX SHELL SCRIPT

Posted on 2006-07-10
31
13,694 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:Dushan911
  • 16
  • 9
  • 5
  • +1
31 Comments
 
LVL 38

Expert Comment

by:yuzh
ID: 17079339
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


0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079379
Thanks again for your help  yuzh !

But its says

": bad interpreter: No such file or directory"

when I try to run it.

BR Dushan
0
 
LVL 38

Expert Comment

by:yuzh
ID: 17079423
You need to replace /path-to/datafile with the real path and filename for your
input file!

0
 
LVL 38

Expert Comment

by:yuzh
ID: 17079431
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.
0
 
LVL 1

Expert Comment

by:tacticalvehicle
ID: 17079481

#!/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
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079602
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





0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079612
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
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079621
Still same error is comming......
0
 
LVL 38

Expert Comment

by:yuzh
ID: 17079738
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?
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079751

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

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

BR Dushan
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079760
[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
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079787
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]#
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079794
#-----------------------------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
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079809
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]#
0
 
LVL 38

Expert Comment

by:yuzh
ID: 17079889
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!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Author Comment

by:Dushan911
ID: 17079926
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.... :(
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17079959
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
0
 
LVL 15

Accepted Solution

by:
bpmurray earned 500 total points
ID: 17079975
or to use your variable names:

#!/bin/ksh
# This line would actually be coming from the program:
tr "," " " <$1 | while read user password homedir
do
      echo "User=$user, Password=$password, Home=$homedir"
done

The same script will work for ksh, sh and bash.
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17079989
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]#
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17080005
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'

0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17080007
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
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17080021
Which shell are you using? What does echo $SH say?
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17080034
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
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17080048
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
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17080094
[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]#
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17080106
[root@linda1 csv]# ./read_csv.sh
: bad interpreter: No such file or directory
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17080110
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

0
 
LVL 17

Author Comment

by:Dushan911
ID: 17080112
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
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17080124
Have you tried running dos2unix? It will not work until you do that.
0
 
LVL 17

Author Comment

by:Dushan911
ID: 17080132
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
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17080162
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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever been frustrated by having to click seven times in order to retrieve a small bit of information from the web, always the same seven clicks, scrolling down and down until you reach your target? When you know the benefits of the command l…
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now