• 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
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

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: 11445
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: 43026
76
  • 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: 24325
167
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: 24325
167
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.
 
You don't like waffles? Well, do you like this tiny ad?
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic