1

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

  • You probably want to look into awk which is probably best suited for these tabular information operations. sed and cut are more useful for isolated incidents where you have a single delimited line and want to extract fields from it. Anytime you get more elaborate with it, you should switch over to awk – Bratchley Jul 13 '16 at 2:38
  • FYI This whole thing could be done in one 'real' SQL statement after one or maybe a few setup depending on your DBMS, and if the volume of data is large it would likely perform much better because DBMSs have sophisticated optimizers to deal with just this kind of data operation whereas shell does exactly and only what you tell it. But you don't want that so I won't suggest it. – dave_thompson_085 Jul 14 '16 at 6:53
0

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.

  • Thanks a lot for your reply . What exactly is (while read phonenum) is doing? By phone num do you refer to the file name or column or line ? – itp dusra Jul 13 '16 at 2:27
  • But I want the update to be made in the DB . Will this script do that ? – itp dusra Jul 13 '16 at 2:31
  • cut [...] | while read phonenum takes the output of cut and uses it as the input for the while loop. read phonenum will read the input into the variable phonenum. The script will generate SQL, which you can review onscreen by just running the script, or you can redirect into an .SQL file you can execute in an SQL client, or you can pipe the output of the script directly into your SQL client (e. g. ./script.sh /path/to/data.csv | mysql -uuser -ppasswd dbname). – DopeGhoti Jul 13 '16 at 2:45
  • Before the while loop can I create a db link and inside the while loop can I use the follwing sql statement : update table_users set phonenumber = concat(prefix, phonenumber) – itp dusra Jul 13 '16 at 2:54
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.

  • Thanks a lot . But could you tell me how to do it with shell scripting ? My requirement is to have a shell script that does this. Looking forward to your reply.. – itp dusra Jul 14 '16 at 4:07
  • @DopeGhoti answered with how to do it in a shell script. Which is about as good as you're going to get with shell and there's nothing I want to add to it. My answer was focused on using existing programming libraries to do the job properly (because parsing CSV correctly or working with a database is NOT something that needs to be or should be reinvented). I wanted an answer to point at the next time someone asks "How can I import a CSV file into mysql". – cas Jul 14 '16 at 5:32
  • My advice is to NOT use shell for this...but if you really want some examples of using mysql from a shell script, you could look at my mythtv scripts on github. They show how to build up SQL commands (with optional WHERE etc clauses), how to execute them, how to extract data from mysql, etc. – cas Jul 14 '16 at 5:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.