• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

read large CSV file and update the database

 
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 11497
19
Android Google Web Toolkit Mac Eclipse IDE Ubuntu Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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


EaseUp
PatienceIsAVirtue
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
JavaMonitor Support
Posts: 251
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Saloon Keeper
Posts: 27763
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
Ranch Hand
Posts: 1923
Scala Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 27763
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
To avoid criticism do nothing, say nothing, be nothing. -Elbert Hubbard. Please critique this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic