Java Database Connectivity (JDBC)

Hello everyone

I am working on a new production DW/BI system where we need to read (but not update) a large number of (often large) flat files.

So we need a very fast (many files will be many Gb in size) and very reliable JDBC driver (type 4 is ideal) to read and load these files into relational databases (both Oracle and Microsoft SQL Server are targeted). Basically this driver will be part of high speed ETL process.

Specifically the input files will be both comma and also comma/double-quote separated to handle embedded commas like "string,string" is treated like single column

here is example of a one line in a file: (note that some columns are surrounded by double quotes but not all)

inputfile.txt

hello,world,my,address,"some street, some number, some state",123,456,5678.4554,"12345,678910"

this needs to be broken up into 1 row with column values like so:

hello
world
my
address
some street, some number, some state <<<<<<<<<< note embedded comma from double-quoted input
123
456
5678.4554
12345,678910 <<<<<<<<< note embedded comma, hence this is one column, input surrounded by double-quotes

-------------

Ideally the driver should work on both Windows 64-bit and on Unix/Linux 64-bit Java/JDBC clients.

Ideally the driver can handle variable number of columns within a single file i.e each line of flat file may have different number of columns like so:

input file:

str1, str2, str3
1,2,3,4,5,6,7
only one string on this line

---------------

Any suggestions before I have to write my own driver..... 8^)

thank you in advance