Data cleaning before loading

[Anonymous]

Hello!

I have an issue in data. From one of the application I am loading project related information in DIM_PROJECT. But few columns has following format:

PROJECTNUMBER	BUILDING
10001	["New","Rehab","Annex"]
10002	["New"]
10003	["New","Rehab"]
10004	["New"]
10005	["Rehab","Vehicles"]

I want to remove this -- [""] from the values. 

PROJECTNUMBER	BUILDING
10001	New,Rehab,Annex
10002	New
10003	New,Rehab
10004	New
10005	Rehab,Vehicles

How can I achieve this?

Regards

Priya

[Anonymous · Accepted solution]

This bit of code should work for you. It is using a standard piece of Java String manipulation. If your data is in a column called "data" from a row called "row1", your code would look like this...

row1.data.replaceAll("[\\[\\]\\\"]", "")

[Anonymous]

@rhall Thanks! That worked!