DSXchange

Posted: **Wed Jun 08, 2016 5:48 am**

Hi,

I have simple job, where i am reading excel sheet using unstructured stage and writing to sequential file. My requirement is get data as it is from excel to output which is CSV file.In the sheet two of columns have numeric data (cell format is general) and Issue was they are converting to exponential form when writing to sequential file

"1.1076079E7","2.3132061E7"

Tried using the source and target datatypes are VARCHAR, NVARCHAR and didn't get expected numeric output.

When i use other than Varchar, NVarchar job is failing . Can you please suggest me on how to get data as it is from input.

Thanks.

Posted: **Wed Jun 08, 2016 6:58 am**

Apologies in advance, Oracle, but I'm going to use you as an example of something Not To Do, something I "fix" over and over. This time decided to point it out and then do the dirty later. Nothing personal! Thanks for coming up and standing on the stage with me for a moment.

When you create a post here, please feel free to make use of the Additional info field for pertinent information that may help - exact DataStage version comes to mind. Other details / things of that nature. Please don't simply copy your Subject and paste it there, how in the heck is that any kind of "additional information"?

Your Moderator thanks you for your attention in this matter. Helps preserve what little is left of my sanity.

Posted: **Wed Jun 08, 2016 7:03 am**

Now, having said that, on to your actual question.

Sadly, that is default Excel behavior that has driven people nuts from the beginning of time and there are plenty of discussions out there on it for ther Googling. There is no option to disable that, unfortunately. The only "workaround" seems to be not using General for cell format as that gives Excel free rein to do what it wants with the data. Not sure how much this will help the Unstructured stage, if at all, but try changing the format to number or text and see if that solves your problem.

Posted: **Mon Jun 13, 2016 4:11 pm**

I can't help with the Unstructured Stage, but I assume you could do a scientific notation conversion into decimal fields in the Sequential File stage. Here is how I've converted scientific notation recently:

Code: Select all

IF NullToValue(nodupes.BOUNDARY_AREA, '') = '' THEN SetNull()  
ELSE IF Num(nodupes.BOUNDARY_AREA) AND Index(nodupes.BOUNDARY_AREA,'e',1) > 0 
THEN DecimalToDecimal(Field(nodupes.BOUNDARY_AREA,'e',1) * 10 ^ Field(nodupes.BOUNDARY_AREA,'e',2))
ELSE IF Num(nodupes.BOUNDARY_AREA) AND Index(nodupes.BOUNDARY_AREA,'E',1) > 0 
THEN DecimalToDecimal(Field(nodupes.BOUNDARY_AREA,'E',1) * (10 ^ Field(nodupes.BOUNDARY_AREA,'E',2)))
ELSE IF Num(nodupes.BOUNDARY_AREA) THEN StringToDecimal(nodupes.BOUNDARY_AREA)
ELSE SetNull()

Hope this helps.
John

DSXchange

converting numeric data to exponential form in unstructured

converting numeric data to exponential form in unstructured