Bash script for reads from csv and use it in mysql query

Question

I need a bash script which reads from csv 2 values per line and use it in mysql query.

The .csv contains about +3k lines like this:

FA63MZ6,200000000163,2020-06-27,CF,Name Surname1 Surname1
FA63MZ6,200000000163,2020-06-27,CF,Name2 Surname2 Surname2

I would like to extract "date" and "Name Surname1 Surname1" as variables to use it in a mysql query.

I am triying with :

echo "select status from invoices where date_created= \"${date01}\" and customer_id in (select id from customers where name = \"${name01}\" );" | mysql -u user -pPassword -D dbname | grep -v status

Ideally this script could be create a .csv with the result of query:

FA63MZ6,200000000163,2020-06-27,CF,Name Surname1 Surname1,paid
FA63MZ6,200000000164,2020-06-27,CF,Name2 Surname2 Surname2,not_paid

Thanks in advance,

Gilles Quénot · Accepted Answer · 2020-07-01 18:29:53Z

2

Like this:

{
    while IFS=, read -r f1 f2 f3 f4 f5; do
        printf "SELECT status FROM invoice WHERE date_created=\047$f2\047 AND
        customer_id IN (
        SELECT id FROM customers WHERE name = \047$f5\047);"
        echo
    done < Input_File.csv
} | mysql --skip-column-names -u user -pPassword -D dbname

The \047 ascii sequence is the single quote character.

Test without MySQL

while IFS=, read -r f1 f2 f3 f4 f5; do
    printf "SELECT status FROM invoice WHERE date_created=\047$f2\047 AND
    customer_id IN (
    SELECT id FROM customers WHERE name = \047$f5\047);"
    echo
done < Input_File.csv

Output

SELECT status FROM invoice WHERE date_created='200000000163' AND
    customer_id IN (
    SELECT id FROM customers WHERE name = 'Name Surname1 Surname1');
SELECT status FROM invoice WHERE date_created='200000000163' AND
    customer_id IN (
    SELECT id FROM customers WHERE name = 'Name2 Surname2 Surname2');

edited Jul 1, 2020 at 18:29

answered Jul 1, 2020 at 17:17

Gilles Quénot

188k43 gold badges232 silver badges229 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bill Karwin Over a year ago

I'd use mysql --skip-column-names instead of the final grep

user1452962 Over a year ago

Now where's the FA63MZ6,200000000163,2020-06-27,CF,Name Surname1 Surname1,paid output? :)

Gilles Quénot Over a year ago

Feeded to MySQL STDIN. If you remove ` | mysql ...........`, you will see the output of teh script

Gilles Quénot Over a year ago

Added the output in my answer

Ubun3t Over a year ago

@GillesQuenot your code works, but doesn't output in a csv format with paid or not_paid :( Thanks a lot!!!

|

user1452962 · Accepted Answer · 2020-07-01 17:25:41Z

0

Something like this should work, however is untested:

#!/bin/bash

while read -r LINE; do
  DATE=$(awk -F, '{print $3}' <<< "${LINE}")
  NAME=$(awk -F, '{print $5}' <<< "${LINE}")
  RES=$(echo "select status from invoices where date_created= \"${DATE}\" and customer_id in (select id from customers where name = \"${NAME}\" );" | mysql -u user -pPassword -D dbname | grep -v status)
 echo "$LINE, $RES"
done < "csv_file.csv"

It should be noted that it works only for provided format FA63MZ6,200000000163,2020-06-27,CF,Name Surname1 Surname1 and does not work with quoted forms of csv, or fields containing ,

edited Jul 1, 2020 at 17:25

answered Jul 1, 2020 at 16:40

user1452962

3863 silver badges8 bronze badges

7 Comments

Shawn Over a year ago

If LINE is an array, ${LINE} is the first element of it. And if you want read to split on commas, set IFS appropriately. Useless Use Of Cat (tm) too.

user1452962 Over a year ago

I want to read the whole LINE from a file with default IFS (newline, tab, space), extract arguments for query with awk and put it back. I honestly don't understand what you really mean and what arrays could come from provided format FA63MZ6,200000000163,2020-06-27,CF,Name Surname1 Surname1

Ubun3t Over a year ago

Thanks @user1452962, the script return : " FACE63MZ6,200000000163,2020-06-27,CF,Maycol, " when must be returns: "FACE63MZ6,200000000163,2020-06-27,CF,Maycol Adolfo Test ,paid" . I mean $NAME has only a name.

Shawn Over a year ago

If you don't want read to split the line into an array, why are you using -a with it?

Ubun3t Over a year ago

ok, this is the key, I have removed -a from while and now seems to works.

|

Collectives™ on Stack Overflow

Bash script for reads from csv and use it in mysql query

2 Answers 2

Test without MySQL

Output

7 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Test without MySQL

Output

7 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related