Home » SQL & PL/SQL » SQL & PL/SQL » Parsing CSV file into external tables
Parsing CSV file into external tables [message #271413] Mon, 01 October 2007 11:06 Go to next message
dgoelfuelquest
Messages: 6
Registered: October 2007
Junior Member
I have an input CSV file with 2 different types of formats for the records. I want to load these into an external table. What is the best way to do this?
Re: Parsing CSV file into external tables [message #271415 is a reply to message #271413] Mon, 01 October 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you explain a little bit more and put a test case maybe we can help you more.

Regards
Michel
Re: Parsing CSV file into external tables [message #271420 is a reply to message #271415] Mon, 01 October 2007 11:20 Go to previous messageGo to next message
dgoelfuelquest
Messages: 6
Registered: October 2007
Junior Member
OK..here is the situation. This is my first time doing this.

There is an input file with 2 different type of records. Attached is a file.

Currently records of type 1 are being processed and loaded into oracle internal tables.

Question is how do I modify the current script to load records of type 2.

Also, records of type 1 and type 2 are randomly placed in the file
Re: Parsing CSV file into external tables [message #271422 is a reply to message #271420] Mon, 01 October 2007 11:21 Go to previous messageGo to next message
dgoelfuelquest
Messages: 6
Registered: October 2007
Junior Member
I hope I have explained it well. Please let me know if you have further questions.

Thanks
Re: Parsing CSV file into external tables [message #271440 is a reply to message #271420] Mon, 01 October 2007 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can Oracle knows if it is record of type 1 or 2?

Regards
Michel
Re: Parsing CSV file into external tables [message #271443 is a reply to message #271413] Mon, 01 October 2007 12:22 Go to previous messageGo to next message
dgoelfuelquest
Messages: 6
Registered: October 2007
Junior Member
Exactly, thats what I wanted to know. Is there was a way to tell Oracle to distinguish between these type of records? Seems like there is none.

Regards
Dhara
Re: Parsing CSV file into external tables [message #271449 is a reply to message #271443] Mon, 01 October 2007 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, insert a field, for instance at column 1, to tell which record is type 1 and which one is type 2.

Regards
Michel
Re: Parsing CSV file into external tables [message #271450 is a reply to message #271413] Mon, 01 October 2007 12:34 Go to previous messageGo to next message
dgoelfuelquest
Messages: 6
Registered: October 2007
Junior Member
Thanks! But the problem is we get these files from a third party. That is how they format and send it to us and they expect us to parse it. Any further ideas?

Thanks
Dhara
Re: Parsing CSV file into external tables [message #271461 is a reply to message #271450] Mon, 01 October 2007 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not possible.
How YOU determine what is the different type and if it is a type 1 record badly formed like a type 2 or a real type 2?
If you can say that and can put it in words then MAYBE it is possible to tell it to Oracle.
Can you do it?
If not, then you're out.

Regards
Michel
Re: Parsing CSV file into external tables [message #271500 is a reply to message #271461] Mon, 01 October 2007 14:29 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I hope this "Third party" isn't from outer space; call them, or write a few words to explain the problem and suggest a solution (for example, inserting a separate column as Michel suggested).

Otherwise, I'm afraid you're out of luck here.
Re: Parsing CSV file into external tables [message #271502 is a reply to message #271413] Mon, 01 October 2007 14:31 Go to previous messageGo to next message
dgoelfuelquest
Messages: 6
Registered: October 2007
Junior Member
Thanks for your help!
Re: Parsing CSV file into external tables [message #271550 is a reply to message #271440] Tue, 02 October 2007 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Mon, 01 October 2007 19:19
How can Oracle knows if it is record of type 1 or 2?

Regards
Michel


Does recordtype 1 always start with a field with value < 120? and recordtype 2 >= 120?
Re: Parsing CSV file into external tables [message #271747 is a reply to message #271413] Tue, 02 October 2007 14:21 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Since the two types of data are intermixed and there is no way to determine what type of row it is except by looking at the number of columns, Personally, instead of using external files or sqlloader, I would use UTL_FILE and load the table line by line. You can easily use program logic to parse the data and determine what type of data it is and insert it into the correct table.
Previous Topic: create unique index
Next Topic: split data of one column on bases of another column (multi merge of an IM illiterate)
Goto Forum:
  


Current Time: Mon Dec 05 05:07:37 CST 2016

Total time taken to generate the page: 0.24009 seconds