You can translate the question and the replies:

Delimited file sources- rows to columns

Hi, I'm having an issue with a delimited file source. The file is actually a column from a big query wich contains a CLOB, that clob has the next format : parameter1:value1;parameter2:value2;parameter[n]:value[n]; ........ What i want is to convert the rows to columns and values in this way : | parameter1 | parameter2 | parameter[n] | | -------- -------| ---------------- | ----------------- | | value1 | value2 | value[n] | Can you please help me out with an ideea regarding this ? I tried a few solutions but i end up with 2 columns, parameter and value and multiple rows with data it self. For this i used as *Column delimiter *** :** and as *End of line delimiter* **;** | Column 1 | Column 2 | | ------------- | -------------- | | parameter1| value1 | Using pivot functionality would be an ideea but the data inside the clob is not FIXED, it's dynamic and i have to find a solution to make it work on large scale. Any ideeas, hints are appreciated. Thank you ! Have a good day!

2 Answers

Hi, In order to retrieve the results in a format which has each 'parameter' as the column name and has its corresponding 'value' in a row, I would develop a custom wrapper using [Denodo4E](https://community.denodo.com/docs/html/browse/7.0/denodo4e/install_denodo4e/install_denodo4e#installing-denodo4e) extension to achieve this scenario. Also, note that the schema of a view must be static and it cannot be dynamic. You could take a look at [Developing Custom Wrappers](https://community.denodo.com/docs/html/browse/7.0/vdp/developer/developing_extensions/developing_custom_wrappers/developing_custom_wrappers#developing-custom-wrappers) section under the Virtual DataPort Developer Guide for more details. You could also find samples for Virtual DataPort Custom Wrapper in the directory '<DENODO_HOME>\samples\vdp\customWrappers' for your reference. Hope this helps!
Denodo Team
16-09-2019 05:26:49 -0400
Hi, We ended up developing a java function wich transformed the data from CLOB into a JSON format wich could be easily used after. Thanks for answer!
user
02-10-2019 09:51:06 -0400
You must sign in to add an answer. If you do not have an account, you can register here