Hi,
Hope I won't be repetitive for asking. What I am trying to build a parallel job which is reading a sequential file, sorting its data and then writing it to an unstructured data (into an excel file). My excel format of the data is not fixed length and includes both decimal and varchar data types. Its format looks like below and I have no trouble to build the excel file :
Label1
Label2
Label3 Month1 Month2.... Month12
Area1 Amount1 Amount2...Amount3
Area2 ............
Label4
Label5 etc...
What my requirement is writing the columns data types as it is (amounts as decimal, other fields as string) into the excel file. I didn't manage to do this, I assumed all the columns as varchar.
I think RCP doesn't work in my case and this problem is beyond my knowledge.
Any help appreciated, Thanks
Seval
Dynamically selecting columns(and its data type)
Moderators: chulett, rschirm, roy
Sorry but I'm not really following what the problem is that you are having. What "doesn't work" when you use RCP for this? And by saying you are "dynamically selecting columns" does that mean you need RCP for this as the number of columns and data types change from file to file?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Let me clear. I didn't even try to use RCP, because as far I read about it, it makes you to select a complete schema of the file at run time.
What I need is, writing a column into a file (using unstructured data stage) which can be either decimal or varchar based on the input data selectively.
(Because the users of the excel files make some calculations over the decimal cells after the file sent to them, If the cells are defined as varchar, that won't be possible unfortunately)
I hope it helps (my mother language is not english, sorry)
Thanks
What I need is, writing a column into a file (using unstructured data stage) which can be either decimal or varchar based on the input data selectively.
(Because the users of the excel files make some calculations over the decimal cells after the file sent to them, If the cells are defined as varchar, that won't be possible unfortunately)
I hope it helps (my mother language is not english, sorry)
Thanks
Your English is just fine, no worries there.
I haven't worked with the Unstructured stage writing to Excel but have done other things interfacing with it and Excel is a bit of a harsh mistress. It doesn't really have "varchar" fields per se unless you are saying they end up as Text fields rather than say, as General ones. Is that what is happening and you are certain they can't do "calculations" on them, as in that is a tested issue rather than an assumed one?
Since Excel loves to convert anything that looks even remotely like a number to a number, I was thinking it would be something you really wouldn't need to worry about.
I haven't worked with the Unstructured stage writing to Excel but have done other things interfacing with it and Excel is a bit of a harsh mistress. It doesn't really have "varchar" fields per se unless you are saying they end up as Text fields rather than say, as General ones. Is that what is happening and you are certain they can't do "calculations" on them, as in that is a tested issue rather than an assumed one?
Since Excel loves to convert anything that looks even remotely like a number to a number, I was thinking it would be something you really wouldn't need to worry about.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hey, People Of Earth Who Have Actually Done This Before... as in use this stage as a target, feel free to jump in and add your expertise.Yes, I am pretty sure that numbers are stored as text (they are aligned to left), as I mentioned before because of the nature of my input data, I couldn't make it possible to declare both varchar fields and decimal fields as it was, to support both of them I had to define them as varchar
Thanks for having time. BTW I have learnt too much insight from your and Ray.wurlood (name I'm not sure)'s comments over the 2,5 years since I have started to experience DS. This is a great website :D
Thanks for having time. BTW I have learnt too much insight from your and Ray.wurlood (name I'm not sure)'s comments over the 2,5 years since I have started to experience DS. This is a great website :D
I can only offer the following regarding RCP, and how its worked in my experience.
It would seem that it does indeed create everything in the spreadsheet as General (Except dates to which it gives a custom style). However values that were decimals seem to be treated as numbers, and values that were text seem to be interpreting as text (even if the text field actually contained numbers).
The set up of my job is a generic read from a DB, fully RCP and loaded to a excel file.
Since you are not using RCP, you should define columns that are decimal as decimals and text as varchar prior to loading your excel file and it should work?
It would seem that it does indeed create everything in the spreadsheet as General (Except dates to which it gives a custom style). However values that were decimals seem to be treated as numbers, and values that were text seem to be interpreting as text (even if the text field actually contained numbers).
The set up of my job is a generic read from a DB, fully RCP and loaded to a excel file.
Since you are not using RCP, you should define columns that are decimal as decimals and text as varchar prior to loading your excel file and it should work?