?
Solved

Reading data with a bash script from a CSV file based in uniqueID

Posted on 2016-08-17
15
Medium Priority
?
71 Views
Last Modified: 2016-08-19
Hi Guys - I have a .csv file like this:

SERIALNO    FIRSTNAME   SURNAME  PHONENUMBER
11111           MIKE               SMITH        123456789
11112           TOM               JONES         123456789
11113           JOHN              PATRICK     123456789
11114           PAUL              ONEILL        123456789

Using bash - I need to get firstname, surname and phonenumber based on a unique SERIALNO within the csv file into local variables within the script.

Any advice much appreciated.

regards

Michael
0
Comment
Question by:Choakem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 2000 total points
ID: 41759116
I understand that you have an ID at hand and want to read the data based on that ID into one (?) variable.
If that's what you want - here you go:

ID=11111   # Fill in the required value
DATA="$(awk '/^'$ID'/ {print $2,$3,$4}' inputfile.csv)"
echo $DATA

If you want three variables use this:

ID=11111   # Fill in the required value
DATA="$(awk '/^'$ID'/ {print $2,$3,$4}' inputfile.csv)"
FIRSTNAME=${DATA%% *}
TEMP=${DATA#* }; SURNAME=${TEMP% *}
PHONE=${DATA##* }

echo $FIRSTNAME $SURNAME $PHONE
1
 
LVL 1

Author Comment

by:Choakem
ID: 41759123
Excellent - that works perfectly.

Could I get data from each field into a separate variable?

regards

Michael
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 41759124
Please re-read my comment - I augmented it in the meantime.
0
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 1

Author Comment

by:Choakem
ID: 41759132
Awesome - You sir are a scholar amongst men....
0
 
LVL 1

Author Closing Comment

by:Choakem
ID: 41759133
Fast, responsive and worked first time - Truly excellent

Michael
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 41759135
Thanks for the nice compliment and for the points - you're always welcome!
0
 
LVL 1

Author Comment

by:Choakem
ID: 41759158
Could you give a quick explanation of

FIRSTNAME=${DATA%% *}
TEMP=${DATA#* }; SURNAME=${TEMP% *}
PHONE=${DATA##* }

how would this expand into additional variables - I cant follow it.....

Usually I can follow such things - lol - I'm getting old.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 41759177
That's a variable edititng (string manipulation) feature.

"%%" removes the rightmost part (longest match, shortest match would be "%") from the variable, up to the specified expression (" *", "a space and everything on the right of that space"), so if we have DATA as "MIKE SMITH 123456789" then ${DATA%% *} will yield "MIKE".

"##" does the same for the leftmost part (longest match, shortest match would be "#") up to the specified expression ("* ", "everything on the left of a space and that space itself"), so again, if we have DATA as "MIKE SMITH 123456789" then ${DATA##* } will yield "123456789".

The remaining expression is just a combination of these two, using an intermediate, temporary variable and the "shortest match" variant, of course.

Here is an explanation of string manipulation in bash:
http://tldp.org/LDP/abs/html/string-manipulation.html

By the way, we could also use an array, so we won't have to edit the string afterwards:

ID=11111   # Fill in the required value
MYARRAY=($(awk '/^'$ID'/ {print $2,$3,$4}' inputfile.csv)) # (..) is short for "define and fill an array"
FIRSTNAME=${MYARRAY[0]}
SURNAME=${MYARRAY[1]}
PHONE=${MYARRAY[2]}
echo $FIRSTNAME $SURNAME $PHONE

Instead of assigning additional variables ("FIRSTNAME", ...) you could also work directly with the array elements ("${MYARRAY[0]}", ...)

ID=11111   # Fill in the required value
MYARRAY=($(awk '/^'$ID'/ {print $2,$3,$4}' inputfile.csv)) # (..) is short for "define and fill an array"
echo ${MYARRAY[0]} ${MYARRAY[1]} ${MYARRAY[2]}
0
 
LVL 1

Author Comment

by:Choakem
ID: 41759509
That is wonderful and makes perfect sense - Much appreciated.

Final question -> I promise :P

I have almost everything in place except when I download my CSV file - each file is in quotes (blame google sheets for this)

so my source file (the *.csv) file looks like this

"SerialNo" "FirstName" "SecondName" "Phone" ETC

Can i run a script afterwards to remove every occurrence of " ?
0
 
LVL 1

Author Comment

by:Choakem
ID: 41759531
Never mind - this site is awesome

sed 's/\"//g' file.txt

gives me what I need :P
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 41759823
No need for "sed", "awk" is OK:
...
...
MYARRAY=($(awk '/'$ID'/ {gsub("\"","",$0);print $2,$3,$4}' inputfile.csv))  # Omit the caret ("^")
...
...
0
 
LVL 1

Author Comment

by:Choakem
ID: 41761761
All the above works perfectly on Ubuntu but I guess i have a cutdown version of linux on my target box.

this works fine
MYARRAY="$(awk '/^'$ID'/ {print $2,$3,$4}' /tmp/userfile.csv)"

but his does not
MYARRAY=($(awk '/^'$ID'/ {print $2,$3,$4,$5,$6,$7,$8}' /tmp/userfile2.csv))
   This gets the error message when I run it of:
    line 9: syntax error: unexpected "("

I am being forced to use BusyBox v1.24.1

I have and always will have nine colums I would like to assign into variables as above. Can I use string manipulation for this as it looks like i am unable to use arrays...... ):
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 41761767
Your Busybox implementation seems to run with a shell other than bash. I assume it's "ash", and this shell does not know about (  ) as a method to define arrays, and afaik it doesn't support the "typeset" alternative either. I doubt if we can work with arrays here at all.
Please check your shell with "echo $0" or "echo $SHELL".

I don't know if there is "set" but please try

MYARRAY=$(awk '/^'$ID'/ {print $2,$3,$4,$5,$6,$7,$8}' /tmp/userfile2.csv)
set $MYARRAY

#Now you should have the first field in $1, the second in $2 and the third in $3 etc. The more fields you specify in awk's print directive, the more $x variables ($4, $5, $6 ...) will be filled. But please be aware, the maximum is 9 variables (no $10 etc.).

echo $1
echo $2
echo $3
...
...

Did it work?

String manipulation could perhaps be done for 9 variables,  but will be very tricky.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 41761790
My day is over now, sorry. CU tomorrow!

wmp
0
 
LVL 1

Author Comment

by:Choakem
ID: 41762287
Woolmilporc, Absolutely perfect - Worked a treat..........

The set command had no issues with the cut down version of linux I had at my command!!


BRILLIANT!!!!!!!!!!!

PS: I didn't expect an answer last night and went to bed - I figured you be the same! LOL
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
Utilizing an array to gracefully append to a list of EmailAddresses
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

765 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