Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

oracle sql loader

Hi,

I would like to know multiline data with sql loader.
My requirement is I need to load the data from "AMP" prefixed number till I get the "row" which contains the next "AMP" prefixed number.
One record can be in two lines or 3 lines. If we load the empty columns before the AMP prefixed column also can be loaded. But in my sample create table I did not add those columns to store the null values.
 Here I attached the data file and control file and create table script. Can you please let me know how can I achieve this. I tried to use continueif clause. But if the multiline is more than 2 rows then it is not reading the 3rd record.

Thank you in advance.

Thanks.
0
GouthamAnand
Asked:
GouthamAnand
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
What happened to the files you had originally uploaded?

Also post the expected results after the data was loaded.
0
 
GouthamAnandAuthor Commented:
Hi,

I have table like below.

create table tb_item(item_no varchar2(10),weight number,volume number);

And I have csv file(semi colon separated)  containing the data like below.

Item1;1;1;
;11;11;
;111;111;
Item2;2;2;
;22;22;
Item3;3;3;
;33;33;
;333;333;

But I need to load the data as if the data file contains the data like below

Item1;1;1;
Item1;11;11;
Item1;111;111;
Item2;2;2;
Item2;22;22;
Item3;3;3;
Item3;33;33;
Item3;333;333;

ie 2nd and 3rd rows does not contain item1 (in the data file) but I need to load as if they are there. Like same for 5 th row which is not containing item2 and also 7 the 8 th rows for item3.

ie after loading my table should contain the data like below

select * from tb_item;

Item1      1      1
Item1      11      11
Item1      111      111
Item2      2      2
Item2      22      22
Item3      3      3
Item3      33      33
Item3      333      333

Can you please suggest how can I achieve this?

Thanks,
Sai
0
 
slightwv (䄆 Netminder) Commented:
I'm not sure if you can perform sort of a back-reference using sql loader.  I'm still looking to see if I can come up with some magic.

As a possible work-around:
Create a staging table.
Load the data into it.
Then insert into the regular table.

create table tb_item_stage(item_no varchar2(10),
	weight1 number,volume1 number,
	weight2 number,volume2 number,
	weight3 number,volume3 number
	);

[\code]

Control file (I used inline data but you can use a flat file):
[code]

load data
infile *
continueif next preserve (1:1) = ';'
into table tb_item_stage
truncate
fields terminated by ";"
trailing NULLCOLS
(
item_no,
weight1,
volume1,
junk1 filler,
weight2,
volume2,
junk2 filler,
weight3,
volume3
)
begindata
Item1;1;1;
;11;11;
;111;111;
Item2;2;2;
;22;22;
Item3;3;3;
;33;33;
;333;333;

Open in new window


insert into tb_item
select item_no, weight1, volume1 from tb_item_stage where weight1 is not null
union all
select item_no, weight2, volume2 from tb_item_stage where weight2 is not null
union all
select item_no, weight3, volume3 from tb_item_stage where weight3 is not null
/

Open in new window

0
 
GouthamAnandAuthor Commented:
Thank you very much.
This exactly suits my requirement.
0
 
GouthamAnandAuthor Commented:
Thank you very much.
This solution exactly suits my requirement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now