Replacing a character for the whole file

[haristha]

Hi,

I have a delimited file which is a pipe delimiter but prefixed and suffixed with $$ (including the column headers). so the first row of data looks like this

$Field1$|$Field2$|$Field3$.....|$Field25$

$a$|$b$|$c$|.......|$25$

etc...

Could someone please tell me how to eliminate the special characters...I have used the file as CSV and a delimiter of | but however I am unable to get the Find/replace to $=null for all the column values including the headers.

Could anyone suggest some advise please?

Thanks,

[danrh]

I'd use something like this:

1- Bring in the data WITHOUT using the first row of data as column headings.  De-select the option in the input tool.

2- Use a find and replace to replace all '$' characters with ' ' (blank).

3- Use Text to Columns using the pipe delimiter.

4- Cleanup leading/trailing whitespaces that we introduced in step 2.

5- Use dynamic rename to make the first row of data the column names.

I added a select tool in there to drop the original column after it's parsed out to separate fields.

Hope it helps!

[Claje]

Hi,

I'm assuming you want to remove all "$" characters from your file and field names, and that there are no valid "$" Characters in the dataset.

The first thing we'll want to do is clean up the data elements inside of each field.  To do this, the Multi-Field formula will be great.

You can use the following formula, and apply it to all text fields:

REPLACE([_CurrentField_],'$','')

This will get rid of every $ in your entire dataset.

Then we need to deal with field names.

There's a tool called the Dynamic Rename tool which is similar to the Multi-Field formula tool.

You caan select all fields and use the same formula as above, and it should clean up your field names dynamically.

[haristha]

Hi,

Thanks for the quick reply. I have seen your example file and its working fine however when I replicated the same information it doesnt...I could see the $ in all the fields... (TBH I have used $ as an anonymise, actually its ^). I couldnt get the clean file..

:(

Thanks

[danrh]

You should be able to change out '^' for '$' in the 'Find' field of the Text Input tool ... could you post a couple rows of your actual data?  Maybe scramble it some if it needs to be kept anonymous/private?

[haristha]

Hi Danrh,

Thank you for your quick reply again.

I have attached a few records as an attachment. 

Thanks again for your help.

Best Regards,

[danrh · Accepted solution]

I had to make a couple changes to the workflow, but it the flow should still work.

1- In the Input Data, I changed the Field Length to 500.  You have a lot of fields, and they were getting cut off as they came in.

2- In the Text Input, I changed the "$" to "^" in the Find column.

3- In the Text to Columns, I changed the number of Columns to 19, matching the data you attached.

4- In the Data Cleansing, I made sure I had all fields selected.

5- And finally, I think the data in the CSV got broken into two columns in the attachment.  I put them back into one column, assuming the issue was only with the dummy copy.  If your actually data is split into two columns, we can work with that as well.

I'd also double check the Select tool to make sure just Field_1 is deselected.

Hope this clarifies!

[haristha]

Thank you for your help guys..