Easiest ways to import a gig and a half of pipe-delimited text into an Oracle 9i table
Posted on 2005-02-25
I have 8 text files of data, all exactly the same format (which happens to be both pipe-delimited *and* fixed width mysteriously enough, i.e. 30 characters, pipe, 20 characters, pipe etc). The sizes of these files range from 11 Megs to 832 Megs, total about a gig and a half of data (about 500,000 rows).
I want to append all of the rows from these text files to an existing table in an Oracle 9i database. Some of the fields are VARCHAR, some NUMBER, some FLOAT, and one DATE. This is the only "transformation" that's necessary - just converting the all-text file to the appropriate datatypes.
Not all of the fields in the destination table will be populated during the import. There is a trigger on the table that will put default values in some of the fields and the remainder can just be left blank.
What are some of the easiest ways to accomplish this?