CODEWITHSUNDEEP
mysqlbashcsv
Ubun3t
Ubun3t

Reputation: 3

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

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,

Upvotes: 0

Views: 153

Answers (2)

Gilles Quénot
Gilles Quénot

Reputation: 185126

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');

Upvotes: 2

user1452962
user1452962

Reputation: 386

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 ,

Upvotes: 0

Related Questions