Need Help with row values to Column names in BODS

[jayant_mittal2]

Hi experts,

I have a requirement to change the value of the strings to column names.

For ex:

ID     String_value    

1     name=abc;dept=123;grade=A

i want the output result as:

ID     Name     dept     Grade

1       abc          123          A

Please suggest some resolution for this.

Regards,

Jayant

[chethan_lingaraju · Accepted solution]

If input is flat file, try this:

1. Skip header in flat file definition,
2. Read it as semicolon delimited text.
3. Split text, "=" as separator and first word is column name, write to flat file. Second word is data.

If its a cell value from which you are trying to fetch data, consider the below mapping

[jayant_mittal2 · Accepted solution]

Dear Chetan,

Thanks for a solution,

i will try this out, i was concerned if the number of the values coming in the string_value are not subjected to change in every load.

for ex:

Load 1:

ID     String_value  

1     name=abc;dept=123;grade=A

Load 2.

ID     String_value  

1     name=abc;dept=123;grade=A;city=xyz

Load 3:

ID     String_value  

1     name=abc;dept=123;grade=A;phone=4444555

and so on..

ans also, since the column names are getting harcoded, is there a way to get the column names dynamically from the string_value??

[chethan_lingaraju · Accepted solution]

Hello Jayant,

This kind of situation can better be handled in VB-Script/Shell-Script.

Script should be designed to read all your input files and convert string_value of each file to multiple XML element.

When all the input file data is ready in one XML file, it should be fed to BODS job.

[former_member187605]

No need to reinvent the wheel. Check out How to split a comma separated String into multiple rows? - Enterprise Information Management - SCN ... for 2 possible solutions. It's not exactly the same use case, that's true, but I am sure it can inspire you.