Data Import Helper Widget

If you’ve ever done a major data import effort, either from another CRM system or spreadsheets or whatever (I had to combine a slew of Excel spreadsheets with two Access databases and bring in some dbase data as well one time, I still have nightmares), you will run into issues with special characters hosing up your import.

I see this most often when bringing in spreadsheet data or data from a non-Sql Server database.  There are frequently characters in these types of files that will choke an import into CRM.  I spent one too many hours sorting through a million line CSV file doing find and replaces for my taste an wrote a little mini-SharePoint web part that will take in a text or csv file and strip out all the special characters.

It does this via a Regular Expression white list.

string update = Regex.Replace(contents, "[^a-zA-Z0-9?!@,\\.\’\"\\-\t\r\n +]", "");

 

Now I am not exactly a Regex master, so I’ve included the source code and VS Project file in this post if someone wants to improve on this.  I am stripping out everything that could cause problems with the import, but I’m not 100% sure if I am including everything that I want in the white list yet.  Its worked so far though!

The web part will run on an internal intranet SharePoint 2010 site.  I have not tested it with MOSS or on an externally facing site.  As always, if you download code off the internet and deploy it, you do so at your own risk. 

Change the file extension to zip and extract.  You will need to deploy from Visual Studio, I haven’t packaged it as a feature as of yet.

https://xrmguy.files.wordpress.com/2011/11/speccharremove.jpg

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: