DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
sarathchandrakt
Participant



Joined: 29 Aug 2014
Posts: 47
Location: Mumbai
Points: 500

Post Posted: Tue Oct 08, 2019 7:46 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi,

I have a scenario where I need to perform some estimations by picking data from different rows in a file.

For example,
I have 100 rows. I have to use data from rows 1 to 10 to determine value in row 20. But then I have to use that data in 20 to determine value from 11 to 19.

So instead of calculating the columns, I wrote formulas and populated csv and xlsx files. When we open the file in excel, csv shows the right value but when we upload it in the destination DB it loads formulas instead. And when I open xlsx, it shows me formulas in cells instead of data.

Is there a way or workaround to convert this formulas to data instead.

Thanks in advance and any help is appreciated.
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43035
Location: Denver, CO
Points: 222143

Post Posted: Tue Oct 08, 2019 3:13 pm Reply with quote    Back to top    

I am a bit lost. Once you convert a spreadsheet to a csv flat file, that "comma separated values" file has no clue what any formulas were that created the output data. It's just data. So there's no way that uploading the csv file into a database that the formulas appear in place of the data. Is that the "it" you meant when you said "but when we upload it in the destination DB"? The it is the csv file? If so, I really don't see how that is possible. Confused

I'm also assuming the "XSV file" mentioned in your original subject was supposed to say "CSV file"...

_________________
-craig

I've all my wisdom teeth, Two up top, two beneath
And yet I'll recognize my mouth says things that aren't so wise
Rate this response:  
Not yet rated
sarathchandrakt
Participant



Joined: 29 Aug 2014
Posts: 47
Location: Mumbai
Points: 500

Post Posted: Wed Oct 09, 2019 11:50 am Reply with quote    Back to top    

Hi Chulett, thank you for the response. Yes, it is not XSV, it is CSV. Sorry about that. I am not converting spreadsheet to csv. My source is a DB. I am manually writing formulas in transformer. But when I output those Formulas either to CSV or XLSX spreadsheet, they look like formulas when we open the file and when we try to upload the file in DB, it is loading formulas instead of values. When I read the XLSX spreadsheet I created in above step, even the datastage is reading them as formulas(I selected 'Values' in the Unstructured Data stage).
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43035
Location: Denver, CO
Points: 222143

Post Posted: Thu Oct 10, 2019 10:40 am Reply with quote    Back to top    

Okay, I corrected your original subject.

On the actual issue, I've no experience with the Unstructured Data stage so can't help with anything specific there. Heck, I couldn't even find any mention of the property where you specified "Values" in the online documentation for the stage. Hopefully, someone who has actual experience with the stage will wander by and help out.

I do have to say that I'm not at all surprised with the behavior you are seeing. In the csv in particular, those "formulas" are just strings of text and are stored as such. For the actual spreadsheet, I'm guessing you are seeing the same thing because you chose "Values" which I assume just dumps the contents of the cell as is. Since I couldn't find anything online, what is the name of that property and what are your other choices for it?

_________________
-craig

I've all my wisdom teeth, Two up top, two beneath
And yet I'll recognize my mouth says things that aren't so wise
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours