Re: Reading CSV or Excel files in java and mapping values to database columns

On Jun 20, 10:42 am, "delcas2...@xxxxxxxxx" <delcas2...@xxxxxxxxx>
wrote:
Dear colleagues,

I am looking for a free and simple mechanism for reading data from
files (could be CSV or XLS ) and inserting values that are read into a
database. I know that I could import a csv/xls file directly from a
database, but this is different. There is a Document Management
System in the mix. The use case is like this:

1) User creates csv/xls file with certain information ( i.e:
"male,usa,hispanic,married")
2) Java program reads file and maps string values to attribute
properties in the DMS

I've seen the Apache POI and a few other (Oster utils, etc) but I
haven't found something simple and flexible enough to allow me to
incorporate business logic as well as basic file i/o and string
manipulation.

If you have any information, tips or advice, I would greatly
appreciate it.

Kind regards,
Fer


Fer,

You can also try my company's Data Pipeline product: http://northconcepts.com/

Here's an example from our cookbook:

-- code snippet ---------------------------------------------

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;
import java.sql.Connection;
import java.sql.Driver;
import java.util.Properties;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.core.Job;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.jdbc.JdbcWriter;

public class WriteACsvFileToDatabase1 {

public static void main(String[] args) throws Throwable {
// connect to the database
Driver driver = (Driver)
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Properties properties = new Properties();
properties.put("user", "scott");
properties.put("password", "tiger");
Connection connection = driver.connect("jdbc:odbc:dp-
cookbook", properties);

DataReader reader = new CSVReader(new File("credit-
balance.csv"))
.setFieldNamesInFirstRow(true);

DataWriter writer = new JdbcWriter(connection,
"dp_credit_balance")
.setCloseConnectionOnClose(true);

Job.runNow(reader, writer);
}

}

-- code snippet ---------------------------------------------

You can download the cookbook & software from
http://northconcepts.com/products/data-pipeline/downloads.html

If you have any questions, feel free to post it here or in our fourms.


Good luck,
Dele

.

Relevant Pages

  • Re: How to avoid side effects following functional style, when you need to write adn read data to/f
    ... reading data is alike set and get external variables, ... every side-effect function should have a fake argument - a functions, ... For example, function a0writes to a database, function b0reads ... For example, to interact with a file, you need ...
    (comp.lang.functional)
  • Re: How to avoid side effects following functional style, when you need to write adn read data to/fr
    ... reading data is alike set and get external variables, ... Are there any ideas how to walk around this risk, ... every side-effect function should have a fake argument - a functions, ... For example, function a0writes to a database, function b0reads ...
    (comp.lang.functional)
  • Re: Driver retries disk errors.
    ... >> suspect this would avoid some issues reading data off CD as well, ... > something in the 2.6 code) I can't do that from a driver. ... send the line "unsubscribe linux-kernel" in ...
    (Linux-Kernel)
  • Re: Pipes and bash scripts
    ... It is reading data from the log-file, ... so that it can eventually go into the database. ... >> It runs great, but when called from a bash script, it won't exit when ... >> starting a service, when the service dies or stops, it still is ...
    (comp.unix.shell)
  • Re: Encoding about GetString method of DataReader
    ... In terms of Oracle as database, I do know what OCI will do when reading data from Oracle server. ... OCI on the client will do a proper byte conversion according to NLS_LANG setting on data stream from server. ...
    (microsoft.public.dotnet.framework.adonet)