SQL loader control file help required

[vinothjanarthan]

Hi there,

I have a text file like below,

:20:abc
:10:bbc,
ccc,
999
:30:bba

I need to load the above data in to table like below,

10 20 30
--- --- ---
bbcccc,999 abc bba

10, 20, 30 are the columns in the table.

Please help me to create a control file for this.

Thanks
Vinoth

[PAVB]

Logic is not clear to me but assuming is something like building rows taking specific columns from different text file lines I would suggest to define the text file as an "external table" then write a query that does what is needed.

[gandolf989]

If you feel adventurous you can use some combination of the following to merge the line back into a delimited format: sed, awk, cut, grep.

Where I was working before I wrote some SQL to reformat the outout of openLDAP used to store connect strings into a colon delimited format. It worked but it was damn ugly!!!

[shanevic7]

Logic is not clear to me but assuming is something like building rows taking specific columns from different text file lines I would suggest to define the text file as an "external table" then write a query that does what is needed.

PAVB your right, make it a external table it should come out the best

[shanevic7]

PAVB your right, make it a external table it should come out the best

Just wanted to thank you again, this suggestion for the external table has made it a lot more simpler for me and saves time.

[PAVB]

Just wanted to thank you again, this suggestion for the external table has made it a lot more simpler for me and saves time.

Glad to help.