Home » RDBMS Server » Server Utilities » Help with sql loader
icon5.gif  Help with sql loader [message #336667] Mon, 28 July 2008 10:42 Go to next message
simulcop
Messages: 5
Registered: July 2008
Junior Member
Hi all, need help, i have a csv file with this structure:

headerfield1,headerfield2,headerfield3,headerfield4
detail1field1,detail1field2,detail1field3,detail1field4,detail1field5
detail2field1,detail2field2,detail2field3,detail2field4,detail2field5
detail3field1,detail3field2,detail3field3,detail3field4,detail3field5
detail4field1,detail4field2,detail4field3,detail4field4,detail4field5
detail5field1,detail5field2,detail5field3,detail5field4,detail5field5
detail6field1,detail6field2,detail6field3,detail6field4,detail6field5
.
.
.
detailnfield1,detailnfield2,detailnfield3,detailnfield4,detailnfield5


It's a file that have information about purchasing order, where the first line is the header of the order and the other lines are the items of the order.

The idea is to load a table with the next structure using sqlloader:

headerfield1,headerfield2,headerfield3,headerfield4,detail1field1,detail1field2,detail1field3,detail1field4,detail1field5
headerfield1,headerfield2,headerfield3,headerfield4,detail2field1,detail2field2,detail2field3,detail2field4,detail2field5
headerfield1,headerfield2,headerfield3,headerfield4,detail3field1,detail3field2,detail3field3,detail3field4,detail3field5
headerfield1,headerfield2,headerfield3,headerfield4,detail4field1,detail4field2,detail4field3,detail4field4,detail4field5
headerfield1,headerfield2,headerfield3,headerfield4,detail5field1,detail5field2,detail5field3,detail5field4,detail5field5
headerfield1,headerfield2,headerfield3,headerfield4,detail6field1,detail6field2,detail6field3,detail6field4,detail6field5
.
.
.
headerfield1,headerfield2,headerfield3,headerfield4,detailnfield1,detailnfield2,detailnfield3,detailnfield4,detailnfield5

Thanks in advanced for your helpfull advice

Regards

Javier R.

Report message to a moderator

Re: Help with sql loader [message #336668 is a reply to message #336667] Mon, 28 July 2008 10:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

As far as I know SQL*Loader does not handle this data format.

Programatically how are header records differentiated from detail records.
In other words, how is the s/w supposed to recognize the last detail record for previous header?

Report message to a moderator

Re: Help with sql loader [message #336669 is a reply to message #336667] Mon, 28 July 2008 10:55 Go to previous messageGo to next message
simulcop
Messages: 5
Registered: July 2008
Junior Member
Anacedent, thanks for reply.

That's the easy part, in a file i have only one header all the item lines belong to the same header. In other words, i have only one order for each csv file.

Thanks again

regards

Javier R.

Report message to a moderator

Re: Help with sql loader [message #336671 is a reply to message #336667] Mon, 28 July 2008 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table.

Regards
Michel

Report message to a moderator

Re: Help with sql loader [message #336674 is a reply to message #336671] Mon, 28 July 2008 11:29 Go to previous messageGo to next message
simulcop
Messages: 5
Registered: July 2008
Junior Member
Thanks Michel, so there is not way to do this directly from sql loader?

Regards

Javier R.

Report message to a moderator

Re: Help with sql loader [message #336675 is a reply to message #336674] Mon, 28 July 2008 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think so but Barbara is our SQL*Loader expert maybe she will have one of her neat ideas.

Regards
Michel

Report message to a moderator

Re: Help with sql loader [message #336677 is a reply to message #336667] Mon, 28 July 2008 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
[oracle@amo1test tmp]$ cat clever.sh
head -1 sample.dat > header1.raw
HEAD=`cat header1.raw`
export HEAD
echo "${HEAD}"
rm sample.new
awk -v var1=$HEAD '{print var1","$1}' <sample.dat > sample.new
cat sample.new
[oracle@amo1test tmp]$ ./clever.sh
headerfield1,headerfield2,headerfield3,headerfield4
headerfield1,headerfield2,headerfield3,headerfield4,headerfield1,headerfield2,headerfield3,headerfield4
headerfield1,headerfield2,headerfield3,headerfield4,detail1field1,detail1field2,detail1field3,detail1field4,detail1field5
headerfield1,headerfield2,headerfield3,headerfield4,detail2field1,detail2field2,detail2field3,detail2field4,detail2field5
headerfield1,headerfield2,headerfield3,headerfield4,detail3field1,detail3field2,detail3field3,detail3field4,detail3field5
headerfield1,headerfield2,headerfield3,headerfield4,detail4field1,detail4field2,detail4field3,detail4field4,detail4field5
headerfield1,headerfield2,headerfield3,headerfield4,detail5field1,detail5field2,detail5field3,detail5field4,detail5field5
headerfield1,headerfield2,headerfield3,headerfield4,detail6field1,detail6field2,detail6field3,detail6field4,detail6field5
[oracle@amo1test tmp]$

Report message to a moderator

Re: Help with sql loader [message #336683 is a reply to message #336677] Mon, 28 July 2008 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clever indeed. Smile

Regards
Michel

Report message to a moderator

Re: Help with sql loader [message #336755 is a reply to message #336683] Mon, 28 July 2008 21:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
Unless you modify your data file, as anacedent suggested, I don't see any way to do this in one pass with SQL*Loader or external tables. You would need to load the header fields into one staging table, load the detail fields into another staging table, then do an insert using a cartesian join into your target table.

Report message to a moderator

Re: Help with sql loader [message #336979 is a reply to message #336667] Tue, 29 July 2008 09:29 Go to previous messageGo to next message
simulcop
Messages: 5
Registered: July 2008
Junior Member
Thank you all for valuable help.

Regards

Javier R.

[Updated on: Tue, 29 July 2008 09:29]

Report message to a moderator

Re: Help with sql loader [message #337319 is a reply to message #336677] Wed, 30 July 2008 09:49 Go to previous messageGo to next message
simulcop
Messages: 5
Registered: July 2008
Junior Member
How can i do this with a batch (.bat) file on windows, it's possible?
Thanks for your help.

Javier R.

anacedent wrote on Mon, 28 July 2008 12:05
[oracle@amo1test tmp]$ cat clever.sh
head -1 sample.dat > header1.raw
HEAD=`cat header1.raw`
export HEAD
echo "${HEAD}"
rm sample.new
awk -v var1=$HEAD '{print var1","$1}' <sample.dat > sample.new
cat sample.new
[oracle@amo1test tmp]$ ./clever.sh
headerfield1,headerfield2,headerfield3,headerfield4
headerfield1,headerfield2,headerfield3,headerfield4,headerfield1,headerfield2,headerfield3,headerfield4
headerfield1,headerfield2,headerfield3,headerfield4,detail1field1,detail1field2,detail1field3,detail1field4,detail1field5
headerfield1,headerfield2,headerfield3,headerfield4,detail2field1,detail2field2,detail2field3,detail2field4,detail2field5
headerfield1,headerfield2,headerfield3,headerfield4,detail3field1,detail3field2,detail3field3,detail3field4,detail3field5
headerfield1,headerfield2,headerfield3,headerfield4,detail4field1,detail4field2,detail4field3,detail4field4,detail4field5
headerfield1,headerfield2,headerfield3,headerfield4,detail5field1,detail5field2,detail5field3,detail5field4,detail5field5
headerfield1,headerfield2,headerfield3,headerfield4,detail6field1,detail6field2,detail6field3,detail6field4,detail6field5
[oracle@amo1test tmp]$


Report message to a moderator

Re: Help with sql loader [message #337368 is a reply to message #337319] Wed, 30 July 2008 12:17 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that the key to the solution is knowledge of DOS batch scripting. As it appears, you don't know much about it (just as well as I don't), perhaps you should ask for help on one of DOS batch scripting related forums; at this point, it is no longer Oracle related problem.

Report message to a moderator

Re: Help with sql loader [message #337375 is a reply to message #336667] Wed, 30 July 2008 12:40 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The same could be accomplished by a PERL script of under 10 lines.

Report message to a moderator

Previous Topic: Import data using Oracle Data Pump
Next Topic: Direct path load
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 07:24:20 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.