data conversion problem

hemaarvind1 · Post by **hemaarvind1** » Mon Jun 14, 2010 2:40 am

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.

ETLJOB · Post by **ETLJOB** » Mon Jun 14, 2010 2:53 am

"39368,39399,39429"

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

hemaarvind1 · Post by **hemaarvind1** » Mon Jun 14, 2010 2:56 am

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.

ETLJOB · Post by **ETLJOB** » Mon Jun 14, 2010 3:21 am

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?

syeed · Post by **syeed** » Mon Jun 14, 2010 3:21 am

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!

hemaarvind1 · Post by **hemaarvind1** » Mon Jun 14, 2010 4:22 am

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 .

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 14, 2010 4:40 am

Did you specify that the fields are quoted ?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 14, 2010 4:41 am

Did you specify that the fields are quoted ?

daramanoj · Post by **daramanoj** » Mon Jun 14, 2010 4:41 am

Hi

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

hemaarvind1 · Post by **hemaarvind1** » Mon Jun 14, 2010 4:45 am

Hi Sainath,

Yes I have specified that the fields are quoted.

hemaarvind1 · Post by **hemaarvind1** » Mon Jun 14, 2010 4:47 am

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.

ArndW · Post by **ArndW** » Mon Jun 14, 2010 5:22 am

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?

chulett · Post by **chulett** » Mon Jun 14, 2010 6:03 am

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?

hemaarvind1 · Post by **hemaarvind1** » Tue Jun 15, 2010 3:37 am

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.

hemaarvind1 · Post by **hemaarvind1** » Tue Jun 15, 2010 3:39 am

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

DSXchange

data conversion problem

data conversion problem

Re: data conversion problem

Re: data conversion problem