DSXchange

Hi,

I am having data as follows in the sequential file which is the source.

"39368,39399,39429","TEY/MTH10/2007","2007 October - 2007 December"

The source file is a comma separated file (CSV file). I have to load this data to an oracle table.

I could not load this to the oracle table as I am getting problem with the first field

"39368,39399,39429"

I tried specifying the datatype as number and varchar,but invain.

could someone please suggest me a way to load this data.

"39368,39399,39429"

Is the above value is treated as three different columns by DataStage? I hope you have comma as delimiter.

Yes, comma is the delimiter used . It returned an oracle error :

ORA-01722: invalid number

I am not sure if it is due to comma. please confirm.

hemaarvind1 wrote: I tried specifying the datatype as number and varchar,but invain.

Did you try the above in Source or in target?

Is your view data option showing right data as expected?

Since you have enclosed with in double quotes and assuming you have done this setting in your Seq file as well (i.e. anything enclosed with in double quotes as a one column value).

Now you can read the first column as a varchar and remove any comma's and load the data into the oracle object.

thanks!

I am using the sequential file at the source level.

I could not remove the commas in between the data as it is part of the data. I have to load the data as it is .

Did you specify that the fields are quoted ?

Did you specify that the fields are quoted ?

Hi

Change the delimiter to | then you can actually insert all the data you want

Hi Sainath,

Yes I have specified that the fields are quoted.

Hi Manoj,

the problem is I cannot change the files as these are sent by the client. hence I could not change the delimiter.

please suggest if I can let the client know that I cannot load the data if it is in the current format due to comma present in the data.

hemaarvind1 wrote:...
"39368,39399,39429","TEY/MTH10/2007","2007 October - 2007 December"

If "," is your separator then the first column will be read as a string regardless of whether or not you specify a quote character and you cannot load that value into a numeric field.

Or is the data supposed to be 3 numeric fields then 2 more fields?

However it is a perfectly valid varchar so unclear why that would have been "in vain". What is the data type in Oracle of the target field for that value?

The datatype in oracle is supposed to be a number. However, I tried with using datatype as varchar also. For either number or varchar, it is giving the same error.

"39368,39399,39429" data is supposed to be falling in a single field.