Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

set field separator for whitespace

Saurabh Gupta-OCFeb 21 2012 — edited Feb 22 2012

Hi,

We have a set of flat files (around 12000) and it need to be load using oracle external table. we have concatenated all the files into one single file and loading the files into oracle database successfully.

But there is a problem, in the flat files we have identified that the field separator is "whitespace" and we gave whitespace as field separator in oracle external table statement. But there are some fields where there is null values for many rows and since the field separator is whitespace so the next field's value is being inserted into the null value field in place of null.

I am stuck in a very hard situation and really do not know how to come out of it. I have little idea that using awk or sed utilities we can set field separator.

Do you have any idea how to set the field separator in this case?

Your assistance is highly appreciated.

Below is the sample of one of those flat files:

051 419040086626885 55407914 P 00016731 01007644 02 Internet 0160638830 11121906182700021 016 00204 I TEST MNC004.MCC419.GPRS 094.124.161.023 094.124.006.197 094.124.006.010 1275741768 99999

Thanks in advance.