• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

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

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
Choakem
Asked:
Choakem
  • 8
  • 7
1 Solution
 
woolmilkporcCommented:
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
 
ChoakemAuthor Commented:
Excellent - that works perfectly.

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

regards

Michael
0
 
woolmilkporcCommented:
Please re-read my comment - I augmented it in the meantime.
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
ChoakemAuthor Commented:
Awesome - You sir are a scholar amongst men....
0
 
ChoakemAuthor Commented:
Fast, responsive and worked first time - Truly excellent

Michael
0
 
woolmilkporcCommented:
Thanks for the nice compliment and for the points - you're always welcome!
0
 
ChoakemAuthor Commented:
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
 
woolmilkporcCommented:
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
 
ChoakemAuthor Commented:
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
 
ChoakemAuthor Commented:
Never mind - this site is awesome

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

gives me what I need :P
0
 
woolmilkporcCommented:
No need for "sed", "awk" is OK:
...
...
MYARRAY=($(awk '/'$ID'/ {gsub("\"","",$0);print $2,$3,$4}' inputfile.csv))  # Omit the caret ("^")
...
...
0
 
ChoakemAuthor Commented:
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
 
woolmilkporcCommented:
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
 
woolmilkporcCommented:
My day is over now, sorry. CU tomorrow!

wmp
0
 
ChoakemAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now