| Loading into multiple rows from same record from csv file [message #179983] |
Thu, 29 June 2006 10:12  |
swamy99
Messages: 30
Registered: June 2006
|
Member |
|
|
I am loading a datafile from csv delimited. Few of the records are to be loaded as multiple records. I am using decode to insert based on the terminator within that column and splitting that into multiple rows. Here is an example from a CSV file record.
1,1001,ABCD;BCDE;CDEF A1001;B1002;C1003
The record should be split such that the data looks like this
1 1001 'ABCD A1001' --as one column in a row
1 1001 'BCDE B1002' -- as second column in a row
1 1001 'CDEF C1003' -- as third column in a row
I tried using decode, substr and instr functions but able to load only the first row, but unable to get the other two rows as above. I know it can be achived by decode, substr and instr function where the position is to be calculated which I am missing here.
What if the data is variable like
1,1001,ABC;ABCD A200;A1001
1,1002,ABD;BCD;CDEF;DEFG A201;B102;C1003;D4001
......
and the column data looks like
1 1001 'ABC A200'
1 1001 'ABCD A1001'
1 1002 'ABD A201'
1 1002 'BCD B102'
1 1002 'CDEF C1003'
1 1002 'DEFG D4001'
.....
Any Help is appreciated. Thanks
|
|
|
|
| Split the string into several records [message #180185 is a reply to message #179983] |
Fri, 30 June 2006 09:25  |
swamy99
Messages: 30
Registered: June 2006
|
Member |
|
|
How do we split the string in column C to have multiple records. The first characters before ';' should be concatenated with the first few characters after the space to the end of ';'. The same for the second set of characters, 'ABCD' which is after the first ';' should be concatenated with the string comming after the first ';' after the space. Here is the sample data and expecting to get the desired output.
Table TEST1
A B C
1 1001 ABC;ABCD A200;A1001
1 1002 ABD;BCD;CDEF;DEFG A201;B102;C1003;D4001
into table TEST2
A B C
1 1001 ABC A200
1 1001 ABCD A1001
1 1002 ABD A201
1 1002 BCD B102
1 1002 CDEF C1003
1 1002 DEFG D4001
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
