The moose likes Linux / UNIX and the fly likes read large CSV file and update the database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » Linux / UNIX
Bookmark "read large CSV file and update the database" Watch "read large CSV file and update the database" New topic
Author

read large CSV file and update the database

Namrta Pandey
Ranch Hand

Joined: Jul 22, 2008
Posts: 36
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

Joined: Jul 26, 2007
Posts: 10523
    
    9

Namrta Pandey wrote:
Can anybody please help me out ASAP...

EaseUp
PatienceIsAVirtue


[How to ask questions] [Donate a pint, save a life!] [Onff-turn it on!]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42612
    
  65
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?


Ping & DNS - my free Android networking tools app
Kees Jan Koster
JavaMonitor Support
Rancher

Joined: Mar 31, 2009
Posts: 251
    
    5
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


Java-monitor, JVM monitoring made easy <- right here on Java Ranch
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

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).


Customer surveys are for companies who didn't pay proper attention to begin with.
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

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/


http://home.arcor.de/hirnstrom/bewerbung
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: read large CSV file and update the database