DSXchange

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.

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.

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

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).

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?