Solved

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

Posted on 2016-08-17
15
62 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 500 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

726 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