A script to take data from csv file and perform related operations in a database

Question

I am new to the world of shell scripting and don't have much idea of shell scripting . My work requires me to write a script which takes phone numbers from a list of records stored in an CSV file .

For each phone number , it searches in the table of users in the database and if a match is found then it updates the phone number column by adding "A2B1" in front of the phone number .

For example , if a record in the database is found for a phone number like "456789" in the csv file containing records , then the phone number column will be updated as "A2B1 456789".

I thought of the following way to do it : First I will use " cut " command to take the second column from each line of the CSV file . (However , I do not know , how to store the value in the second column in each line in a variable , so that I can use it in a SQL query statement.) Create a db link , and write the SQL search statement/query .

Then if some record is returned and then the updation mentioned above will be done .

I don't know how to do this or write it in shell language .I tried thinking of a way to convey the output of a query to a file and check if the file size is zero and if not it takes the variable in the file(which is the record /phone number) returned by the query , then store it in variable and perform the update operation but I doubt that this would be a good way and moreover given my two days old infancy in this field I am not confident about the syntax too . Eagerly waiting for your help

Answer 1

While you can do mysql queries/updates/etc in a shell script, it's far easier to use a language (like perl or python) with good support for both databases and CSV files.

Here's one way of doing it in perl, using the perl DBI module, and the DBD::CSV and DBD::mysql modules.

It reads each row from your CSV file (i've called it 'updates.csv' and assumed the column name is phonenum) and issues the SQL UPDATE commands for users table of the dbname database in mysql. Change dbname to suit your database.

NOTE: the following code is untested but it should work. As it's untested it's possible I made a few typos or other mistakes.

I strongly recommend testing it first on a COPY of your database rather than immediately running it on the real data. In fact, it's ALWAYS a good idea to test your code on a copy of the real data, no matter what you're writing or what language you're writing it in.

#! /usr/bin/perl

use strict;
use DBI;

### 
### variables setup
### 

# DBD::CSV treats all .csv files in this dir as tables.
# i.e. this directory is the "database" and the .csv files
# are the tables in that database.
my $csv_dir = '/path/to/csv/dir'; 

my $csv_db  = 'updates';    # corresponds to "$csv_dir/updates.csv"

my $m_db    = 'dbname';     # replace with your mysql database name 
my $m_user  = 'username';
my $m_pass  = 'password';
my $m_host  = 'localhost';
my $m_port  = '3306';
my $m_dsn   = "DBI:mysql:database=${m_db};host=${m_host};port=${m_port}";

###
### database handle setup
###

# database handle for CSV connection
my $c_h = DBI->connect ("DBI:CSV:", undef, undef, {
               f_ext      => ".csv/r",
               f_dir => $csv_dir,
               RaiseError => 1,
               }) or die "Cannot connect: $DBI::errstr";


# database handle for mysql connection
my $m_h = DBI->connect($m_dsn, $m_user, $m_pass, { PrintError => 0 });

###
### all set up, time to do some work.
###

# NOTE: this script assumes that the .csv file contains a header line with
# the field names as the first line of the file.
#
# If not, the easiest thing to do is edit it with your preferred text
# editor and add one.  Otherwise, see `man DBD::CSV` to find out how to
# specify field names.
#
# or EDIT and uncomment the following three lines of code:

#$c_h->{csv_tables}{$csv_db} = { 
#  col_names => [ qw(column1 phonenum column3 column4 ...) ];
#};

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
my $c_sth = $c_h->prepare("select phonenum from ?");

# and execute it.  later, we'll use a forech loop to read the data returned
$c_sth->execute($csv_db);

# prepare the SQL statement for the mysql db using placeholders ? for
# the values. this assumes that the column/field name is also called
# 'phonenum' in mysql.  These placeholders are invaluable, they automaticaly
# quote any data that needs to be quoted (e.g. strings) while not quoting
# things that shouldn't be quoted (e.g. integers).  They prevent a huge
# range of common mistakes.
#
# prepare it once, execute it multiple times with different values.

my $m_sth = $m_h->prepare('UPDATE users SET phonenum = ? WHERE phonenum = ?');

$m_h->begin_work;  # begin transaction

foreach ($c_sth->fetchrow_array) {
   chomp;
   my $newphone = "A2B1 $_";
   $m_sth = $m_sth->execute($newphone, $_);
};

$m_h->commit;  # commit transaction

### 
### we're done.  finish the statement handles and disconnect from
### the databases.
###
$c_sth->finish;
$m_sth->finish;
$c_h->disconnect;
$m_h->disconnect;

It looks longer than the average quick shell script, but most of the code is just variable and database handle setup (and that setup code can be re-used in other, similar scripts). The actual code that does the work is (excluding comments) only about half a dozen or so lines.

Answer 2

You can use this script:

#!/bin/bash
PREFIX="A2B1 "
TABLE="sqltablename"
COLUMN="sqlcolumnname"
if [[ ! -r "$1" ]]; then
   echo "unable to read file '$1'"
   exit 1
fi

cut -d, -f2 "$1" | while read phonenum; do
   newnum="$PREFIX $phonenum"
   echo "UPDATE $TABLE SET $COLUMN = '$newnum' WHERE $COLUMN = '$phonenum';"
done

This will, if you run it with your CSV file as a parameter (e. g. ./script.sh /path/to/mydata.csv), output a series of SQL statements which will update the data as you describe. Modify the script to use the correct table and column name.

Once you have verified that it gives you the statements you want, you can then pipe it into your SQL engine of choice, or save the output to an SQL file which you can execute however you prefer with ./script.sh /path/to/mydata.csv > /path/to/updatephonenumbers.sql.