From experience, your best bet is to load the flat file into a staging
table in your database, process the data in that table (convert to upper
case, trim strings, fix dates, etc.) then insert into your "live" tables.
In Data Warehousing, this process is called ETL (extract transfer and
load). Two Java ETL solutions:
http://octopus.objectweb.org/
and
http://cloveretl.berlios.de/
I have no experience with these, but it might help.
You can also use your database utility to load flat files. As Nathan
pointed out, Oracle's tool is SQL*Loader. MySQL allows you to load files
through the LOAD DATA command, PostgreSQL uses a COPY command. Pretty
much every RDBMS has a way of doing it, it's just a matter of reading
the documentation...
Hope it helps,
Philippe
Nathan Maves wrote:
Oracle also has a utility called SQL*Loader
http://www.orafaq.com/faqloadr.htm
On Sep 22, 2005, at 12:06 PM, Larry Meadors wrote:
There are jdbc drivers for csv files, that could simplify one end of
the task.
http://tinyurl.com/bwrpn
You may want to look to your db vendor, too - I know M$ SQL has tools
for importing this sort of data...and i suspect others do, too.
Larry
On 9/22/05, Warren Bell <[EMAIL PROTECTED]> wrote:
My company has several vendors who send us invoices in the form of
comma
delimited flat text files via FTP. My question is, is there a way using
Ibatis to pul in the data from the text file and insert it into a
table. I
can get the data in manually and use Ibatis to insert into the
table, but it
would sure be nice if I could use Ibatis and map the fields in the
flat file
to my objects the same way they are maped from tables or say do a query
like:
INSERT INTO table SELECT field1, field2 ... FROM flat_file
These vendors have no other way to get us our invoices except paper.
Thanks,
Warren Bell
