Solved

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

Posted on 2016-08-17
15
43 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
Comment Utility
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
Comment Utility
Excellent - that works perfectly.

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

regards

Michael
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
Please re-read my comment - I augmented it in the meantime.
0
 
LVL 1

Author Comment

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

Author Closing Comment

by:Choakem
Comment Utility
Fast, responsive and worked first time - Truly excellent

Michael
0
 
LVL 68

Expert Comment

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

Author Comment

by:Choakem
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
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
Comment Utility
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
Comment Utility
Never mind - this site is awesome

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

gives me what I need :P
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
My day is over now, sorry. CU tomorrow!

wmp
0
 
LVL 1

Author Comment

by:Choakem
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

772 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

10 Experts available now in Live!

Get 1:1 Help Now