Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Remove unwanted characters from your data set quickly!

Today I worked with a client who was pulling some data in from a proprietary source and he had two special characters that showed up randomly in one of the columns in his data set. These characters were the hash tag (#) and semi-colon (;). One or both characters would appear at the beginning, end or middle of the column. There are a few different ways you could deal with this type of situation, each of them varying in their degree of complexity and difficulty.

Fortunately for the customer we provided a very easy and quick solution. In this short blog we are going to take a look at the Data Cleansing transform offered in Task Factory. First let's take a look at a before and after picture so you can visualize the data and end result.

 

 

Setting up the component

  1. Pull the Task Factory Data Cleansing Transform into your Data Flow.
  2. Connect your source data to the Data Cleansing transform.
  3. Right click on the Data Cleansing Transform and click Edit.
  4. First select the column you would like to "Replace".
  5. Next select Replace Specified characters or words with user defined value. (Screenshot 1)

     

     

     

  6. After you select an action you will receive a parameter box where you can specify the characters you would like to replace and what you want to replace them with.

     

  7. Now we will finish configuring the rest of the component. Please see screenshot below for final settings.

     

  8.  

     

  9. Finally click ok to save your settings and execute the package!

 

Conclusion:

That's it. I tried to drag it out as much as I could but some things are just that simple. Take a look at some of the other actions included and enjoy! As always thanks for looking.

Print
Categories: Miscellaneous
Tags:
Rate this article:
No rating

MitchellPearsonMitchellPearson

Other posts by MitchellPearson

Please login or register to post comments.