Solved

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

Posted on 2016-08-17
15
47 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
  • 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
importing users to Security group 2 45
Active DirectoryScript to change the Pager Field 1 48
issue with beginner powershell script. 5 54
Linux  Script File Attributes 5 51
This article will show, step by step, how to integrate R code into a R Sweave document
Utilizing an array to gracefully append to a list of EmailAddresses
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…

919 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

13 Experts available now in Live!

Get 1:1 Help Now