Wednesday, October 26, 2011

Built in imports in CRM

I have been working with the built in import functionality for imports a bit lately and I must say that it has been improved a quite a lot. It as now reached a level where it is quite usable but there are still problematic areas you have to look out for.

I found this blog posting (http://blogs.msdn.com/b/crm/archive/2010/03/15/importing-multiple-files-using-zip-import.aspx) which was quite interesting and enlighted me to the fact that you can import zip:ed files which is really good since there are some size limitation on file uploads to CRM. Since you usually get really good compression rates on csv and xml files this is really good news.

For those of you who don't know these limitations, the maximum upload file size to CRM is 8 MB. But if you zip the files the total size of files that can be contained within the zipfile can be up to 35 MB. Just to give you an example of the compression rates that can be achieved, we tried compressing a 70 MB xml based excel document and the zip-file we got was only 0.5 MB. That is a ratio of 1/140, less that 1% of the original size.

Zipping the import files is also good for uploading multiple files with at once, for instance when the files have dependencies or when there are attachments as this reduces the manual work a lot.

A bit of advice though. I would strongly advice against using the csv-format since it is very volatile and often brakes and gives rise to a lot of strange errors. It is also dependent on the local regional settings, for instance, in Sweden, it is not comma separated, but semi-colon separated, as this is the standard numeral separator. Very confusing.

On top of this you often have problems with codepages, if you work in other languages than english, and you have to re-save the file in UTF-8 to make it import properly. Today we also had some issues with Excel insisting on formating the Swedish personal number (Social Security Numbers) as 7.41231E+12 instead of the correct 7412311234. We had to manually force it with different kinds of formatting to save the number correctly.

It didn't end there, Excel also found it fitting to add a few empty columns to the end of our CSV-file making it impossible to import into CRM.

So, we have been working to iron these things out and we were getting there but somewhere along the line we decided to skip the CSV and to go all in for the excel as xml format instead. The files that are saved are a lot bigger but with the zip feature it is managable.

Despite the fact that the new xml-format support and the zip-support exist I think the next time I will be even more vigourous in my recommendation for an import tool such as QuickBix Integration Suite, Scribe, or Import Manager. The new import features are great but for smaller imports, not for migrations.

Gustaf Westerlund
CEO, Chief Architect and co-Founder at CRM-konsulterna AB
www.crmkonsulterna.se