Home » RDBMS Server » Server Utilities » SQL Loader Question (Oracle 10g)
SQL Loader Question [message #353112] Sat, 11 October 2008 10:58 Go to next message
skumar08
Messages: 4
Registered: September 2008
Junior Member
Dear All,
I have attached a data file that I need to import into Oracle. I am using SQL loader to do this load. But the issue I am facing is that I need to separate out the C_NUMBER, RNUMBER,
CH_AMOUNT , subject, to, etc from the data file into separate columns in an Oracle table. Unfortunately, the locations of these fields vary. So, I was wondering if the experts here could guide me on how to load these individual elements into separate columns in Oracle.

As always thanks in advance for your help on this.

Santhosh.
  • Attachment: DataFile.txt
    (Size: 1.33KB, Downloaded 1401 times)

Report message to a moderator

Re: SQL Loader Question [message #353128 is a reply to message #353112] Sat, 11 October 2008 11:45 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I wouldn't know whether it is possible to do that using SQL*Loader because file format is kind of awkward.

Perhaps you could insert the whole file into a table with one (large enough) character column. Then, using SQL capabilities (SUBSTR, LIKE, regular expressions, something else), search through this table and insert values into the "original" target table.

Or, use the input file as the external table and do the same as suggested above.

Report message to a moderator

Re: SQL Loader Question [message #353129 is a reply to message #353112] Sat, 11 October 2008 11:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
About all you can do is load each record into one column in a staging table, using either SQL*Loader or an external table, then use SQL to parse and insert into your target table.

Report message to a moderator

Re: SQL Loader Question [message #353138 is a reply to message #353112] Sat, 11 October 2008 19:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
skumar08 wrote on Sat, 11 October 2008 11:58
I need to separate out the C_NUMBER, RNUMBER,
CH_AMOUNT , subject, to, etc from the data file


What does "etc" consist of ? You mention C_NUMBER, RNUMBER, CH_AMOUNT and then jump to subject, to. Do you need to capture all the data in the data file ? How big are the data files typically ?

Report message to a moderator

Re: SQL Loader Question [message #353159 is a reply to message #353138] Sun, 12 October 2008 01:26 Go to previous messageGo to next message
skumar08
Messages: 4
Registered: September 2008
Junior Member
Thanks for your response. Maybe I can clarify a bit more on my question. As you can see the first record contains C_NUMBER=5001,RNUMBER=1234567890,CH_AMOUNT=50.00 and other entries in new line. I would like to combine these into one row like C_NUMBER=5001,RNUMBER=1234567890,CH_AMOUNT=50.00 separated by a separator and so on. Unfortunately, the records are not always consistent as shown in the data file. The second row (starting with 2250308) and the third row (starting with 2250309) are totally different but the concept is the same which is basically, combine the new line entries in each record into a delimited row separated by a separator.

The size of the file varies. We have currently about 1 mil records which are in the format shown in the sample data file.

Thanks.

Report message to a moderator

Re: SQL Loader Question [message #353186 is a reply to message #353159] Sun, 12 October 2008 10:56 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just wondering: who is responsible for input file format? Did he/she, by any chance, receive an award for it?

There should be some standards, agreed by both "them" (who provide the file) and "you" (who should read it). Negotiations didn't go well, eh? So now you're in problems ... pitty it is 1 million records. Who knows what you'll get next time.

Sorry for being unable to help you.

Report message to a moderator

Re: SQL Loader Question [message #353192 is a reply to message #353186] Sun, 12 October 2008 12:39 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I have to agree with Littlefoot. With such a large, sporadic data file, you don't have a lot of options for loading and parsing the file.

Report message to a moderator

Previous Topic: Error inserting images into table
Next Topic: How to export data for single employee?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 24 10:36:42 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.