Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

read large CSV file and update the database

 
Namrta Pandey
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I am totally new to Shell scripting, In my project i need to perform following task in shell script :

Read csv files from ftp location and according to some conditional value update the records in database :

for ex. ftp csv file

eid,ename,salary,status
1,abc,50000,Active
2,xyz,35000,InActive


now if the status is active then check the existence of ename in our database table, if it exists then, update a field in that table corresponding to that record.

Can anybody please help me out ASAP...
Thanks in Advance
Namrta
 
Maneesh Godbole
Saloon Keeper
Posts: 11021
12
Android Eclipse IDE Google Web Toolkit Java Mac Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Namrta Pandey wrote:
Can anybody please help me out ASAP...

EaseUp
PatienceIsAVirtue
 
Ulf Dittmer
Rancher
Pie
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using the "awk" language and tool, extracting the values from the CSV file is probably no big deal, but how are you going to access a DB from a shell script?
 
Kees Jan Koster
JavaMonitor Support
Rancher
Posts: 251
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The code below was typed *without testing*, so you need to resolve the syntax errors etc yourself. See also http://www.freebsd.org/cgi/man.cgi?query=awk

Place the following in a file named update-active.awk:


Then invoke as follows (note the use of -F to specify that , is the field separator):

% awk -F , -f update-active.awk < file.csv
 
Tim Holloway
Saloon Keeper
Pie
Posts: 18024
47
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ulf Dittmer wrote:Using the "awk" language and tool, extracting the values from the CSV file is probably no big deal, but how are you going to access a DB from a shell script?


Usually via its command-line client program (SQL*PLUS, isql, pgsql, mysql, db2, etc.)

However, I recommend looking at the Pentaho Kettle ETL utility. It's open-source Java (parts of which I've improved myself), can read and write a Panamax-load of formats, allows for logic, calculations and transformations, is extensible, and commercial support is available. I'm probably going to be installing a copy for a client this morning, in fact, going in the other direction (DB2-to-CSV).
 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may use grep and cut for such tasks:

-f := field
-d := delimiter

Problem 1: User 'John ,Active Doe'
Problem 2: User 'Robert'); DROP TABLE salary;'

http://xkcd.com/327/
 
Tim Holloway
Saloon Keeper
Pie
Posts: 18024
47
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I do recommend using something like Kettle (Pentaho DI) for stuff like this where there's a lot of data. The overhead for starting up, running, and shutting down a database utility program on a per-row basis can be considerable. And while you can just script-and-pipe the whole file into a single invocation of the database utility, Kettle affords a more elegant (and more general) solution. Plus, it has other useful features such as the ability to run multiple transforms in parallel and works on a general basis using JDBC drivers.

Co-incidentally, a book on Pentaho DI has just come out, and it's being discussed in the JDBC Forum.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic