Home » RDBMS Server » Server Utilities » Importing file with column header
Importing file with column header [message #229241] Fri, 06 April 2007 03:41 Go to next message
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
Re: Importing file with column header [message #229262 is a reply to message #229241] Fri, 06 April 2007 05:01 Go to previous messageGo to next message
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.
Re: Importing file with column header [message #229270 is a reply to message #229241] Fri, 06 April 2007 06:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Importing file with column header [message #229273 is a reply to message #229270] Fri, 06 April 2007 06:59 Go to previous messageGo to next message
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
Re: Importing file with column header [message #229274 is a reply to message #229273] Fri, 06 April 2007 07:08 Go to previous message
Mahesh Rajendran
Messages: 10672
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).
Previous Topic: Import from 9i to 8i
Next Topic: Scripts for export and import
Goto Forum:
  


Current Time: Wed Dec 07 03:04:44 CST 2016

Total time taken to generate the page: 0.12444 seconds