OraFAQ Forum: Server Utilities » Importing file with column header

Home » RDBMS Server » Server Utilities » Importing file with column header

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Importing file with column header [message #229241]

Fri, 06 April 2007 03:41

moschen
Messages: 25
Registered: April 2007

Junior Member

Hello everybody,

I have a small question. In a current project we have the requirement to import csv files, which have their column names as a header row. Example:

FIRSTNAME,LASTNAME,BIRTHDATE,ACCOUNTNO,SALARY
John,Do,10-10-1900,1111,11
Johana,Do,10-10-1910,222222,22

However, the order of the columns and the number of provided columns might differ from file to file (max number of columns and their names is known). We need to load this information into a single table.

The question is, how can I handle this most efficiently? Is it possible to use SQL loader for this or maybe even external tables?

Looking forward to receiving your comment.

Best regards,
Mo

Report message to a moderator

Re: Importing file with column header [message #229262 is a reply to message #229241]

Fri, 06 April 2007 05:01

aciolac
Messages: 242
Registered: February 2006

Senior Member

There are possible to user SQL Loader. Ha have option to change the order of columns to insert data. See parameters of SQL Loader.

Report message to a moderator

Re: Importing file with column header [message #229270 is a reply to message #229241]

Fri, 06 April 2007 06:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You can change the order (using FILLER. seach forum for examples). But not dynamically ( without looking into datafile / editing the controlfile). TO do it dynamically, you need to employ some scripting methods (perl/shell) with some pattern matching mechanisms. For a one time job, it may be an overkill Smile

External files is better. Treat the files as staging tables and use sql methods to rearrange the columns while loading into the original table.

Report message to a moderator

Re: Importing file with column header [message #229273 is a reply to message #229270]

Fri, 06 April 2007 06:59

moschen
Messages: 25
Registered: April 2007

Junior Member

Thanks for the replies.

As the project requires to load files periodically (so unfortunately not a one time job) I would like to go a bit deeper into this topic.

SQL Loader
* I could create a table with the all the possible fields
* I could then analyze the header row of the data file to generate a control file that loads that data into the created table
* I am ready to go

external tables
* I would need to create a tables that stores the possible fields and theire datatypes
* I can then create dynamically the 'create table ... external ...' statement and execute it.
* I am ready to go

Is this correct?

Mo

Report message to a moderator

Re: Importing file with column header [message #229274 is a reply to message #229273]

Fri, 06 April 2007 07:08

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Something like that.
alternatively,
You could use some scripting methods to 'fix' the datafile first, in a certain order you prefer and update controlfile if there are new columns and load it.
If time permits, i will write a test case for this (On unix).

Report message to a moderator

Previous Topic:	Import from 9i to 8i
Next Topic:	Scripts for export and import

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 25 21:09:17 CST 2024