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

[delcas2004]

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

 

[antonioatt]

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

A simple mode to read from csv files is shown heare:
http://www.csvreader.com/java_csv_samples.php

I hope it will help you !
Antonio

 

[TechBookReport]

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

Have you considered using an ODBC driver and using that to access Excel
or a CSV? Using the JDBC-ODBC bridge it shouldn't be too difficult
(bearing in mind that Sun's JDBC-ODBC bridge isn't considered suitable
for heavy production use). There's details of how to use it here:
http://www.techbookreport.com/tutorials/jdbcodbc.html

HTH

 

[hh]

http://www.jxcell.net
it will help you to deal with xls and csv.

 

[Roedy Green]

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.

see http://mindprod.com/jgloss/csv.html
for various options to read your CSV files. Then you have the
standard problem of putting them into your database.

Often a database has a batch feature for rapid import of data that
turns off some of the journalling. Usually it accepts CSV or some
variant you can fairly easily translate to.

For example, MySQL has the mysqlImport utility.

 

[Dr ART]

you may want to see OpenXML4J at : http://www.openxml4j.org/

OpenXML4J is a Java library dedicated to the creation and manipulation
of Office Open XML (ECMA-376) and OPC based documents (for example
Office 2007 Word, Excel and PowerPoint documents). OpenXML4J provides
you a way to create and manipulate Open XML documents for a bunch of
scenarios without using any office suite.

 

[dtaylor4]

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("jdbcdbc: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

 

[Roedy Green]

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

I have added an well-cross-linked entry in the Java glossary at
http://mindprod.com/jgloss/datapipeline.html