Question Status

Suggested Answer
Augusto asked a question on 3 May 2011 10:31 AM

In CRM4 you could update existing records via bulk import. This functionality is theoretically still there in 2011 but it does not seem to work.

As per SDK documentation:

The configuration information that is required for importing data is contained in the import (data import) (Import) entity and the import file (ImportFile) entity.

To configure data import, do the following:

  • Use the Import.ModeCode attribute to specify whether to create or update data during import. If you are using early bound types, you can use the ImportModeCode enumeration. For a list of the ModeCode values, see Import (Data Import) Entity OptionSet Attribute Metadata.
  • Set Import.IsImport to true.
  • Use the ImportFile.FileTypeCode attribute to specify the type of the import file. If you are using early bound types, you can use the ImportFileType enumeration. For a list of the FileTypeCode values, see ImportFile Entity OptionSet Attribute Metadata. This attribute is only available in Microsoft Dynamics CRM Online.
  • Use the ImportFile.DataDelimiterCode attribute to specify the single character data delimiter in the import file. If you are using early bound types, you can use the ImportDataDelimiter enumeration. For a list of the ImportDataDelimiter values, see ImportFile Entity OptionSet Attribute Metadata.
  • Use the ImportFile.FieldDelimiterCode attribute to specify the single character field delimiter in the import file. If you are using early bound types, you can use the ImportFieldDelimiter enumeration. For a list of the FieldDelimiterCode values, see ImportFile Entity OptionSet Attribute Metadata.
  • Set ImportFile.IsFirstRowHeader to true to indicate that the first row in the source file contains column headings or to false to indicate that the first row contains actual data. If set to false, default column headings are generated.
  • Set ImportFile.ImportId to the ID of the import (data import) that the import file is associated with.
  • Set ImportFile.ImportMapId to the ID of the associated import map (data map).
  • Set ImportFile.EnableDuplicateDetection to true to enable duplicate detection during data import.
  • Read the content of the source file into the ImportFile.Content.”
 
In short the combination ModeCode=1 (Update) and FileTypeCode=0 (CSV) is rejected with an error message that says that this file format is not valid for update.
Even if you try to use XML it does not work unless you use a pre-exported XML file. Pre-exporting a file works perfect if you are a user updating his records, certainly not if it's a import job that needs to update hundreds of thousands of records!
 
Reply
Suggested Answer
Gus Gonzalez responded on 2 Mar 2012 10:55 AM

Guys,

What about doing it via XML files?

Take a look at this video:

Would that suffice?

Reply
Suggested Answer
Gus Gonzalez responded on 6 Mar 2012 7:23 PM

Thanks for adding those tips Alayton.

Please let us know if these steps have solved the issue for you guys.

Thanks.

Reply
Emile Bosch responded on 20 Jun 2011 12:52 AM

Hi,

I have the same issue. It is unbelievably hard to get an answer from any of ms employees to see if this is possible.As it looks now CSV files are not allowed for updates.

I did some digging into the API using reflector and found out that API uses method ValidateInputAndFillDefaultsForUpdateMode in the Microsoft.Crm.ObjectModel.ImportFileService. That checks wheter the filetypecode = 1, and otherwises throws an error. FileTypeCode = 1 one means XML. CSV=0.

Therefore, if my observations are correct, CSV updates are not possible using CRM2011. However this would be a huge dissapointment for me my and my team so i hope i am wrong.

Reply
blehargh responded on 1 Mar 2012 6:47 PM

Did anyone figure out if this is true or not? it seems like I am having issues with updating using CSV as well.

Reply
Suggested Answer
Gus Gonzalez responded on 2 Mar 2012 10:55 AM

Guys,

What about doing it via XML files?

Take a look at this video:

Would that suffice?

Reply
Suggested Answer
Alayton responded on 6 Mar 2012 4:21 AM

The video by Gus shows the way, and I have used this successfuly quite a few times now.

However a couple of gotcha's that have bitten me are;

It works if you type data straight into the export file columns that you want to update.

it works if you copy/paste special with Values from source cells that you have manually entered the data.

It DOESNT work if you copy/paste from source cells that have been, say,  exported from a SQL Query.  Still trying to figure out a way round this, any suggestions are very welcome.

[addition]  I have found if you copy the values you want to update into Notepad it strips out all formatting, you can then copy the values from Notepad into the export file columns and the import works fine. 

If you have any de duplication rules on the entity you want to update, then select Allow Duplicates on your Import.  Dont worry, it uses the hidden colums (i.e. GUID in column A) to update the correct record.

If you or someone else has updated one of the records between your Export and subsequent re import it wont process that record (it uses hidden colum C Modified On to prevent that).  You will need to export the records again.

If you watchout for these you can certainly use Bulk Import to successfully update your data.

Reply
Suggested Answer
Gus Gonzalez responded on 6 Mar 2012 7:23 PM

Thanks for adding those tips Alayton.

Please let us know if these steps have solved the issue for you guys.

Thanks.

Reply
Sebastian Iturrioz responded on 24 Mar 2012 2:50 PM

Has anybody found a way to disable those validations?

I have migrated from 4.0 and we used to use CSV to update records, I can not change to excel format.

The Modified On validation is also giving me trouble, I definitly need to skip that validation.

I'll keep you updated if I can figure out how to handle.

Reply
Jeep responded on 6 Aug 2014 10:54 AM

Hi Gus,

Very nice video.

However, I fall into a different problem when it comes to editing the data.  In the exported list, I need to be able to do some auto-editing based on a new column formula.

Example:

Export of Leads with columns: E-mail, field with 2 options (Allow and Do Not Allow)

I need to be able to find e-mails that matches another list of e-mails and set the field to Do Not Allow in order to re-import it back to CRM.

How would I do that on the XML document?

I hope you understand.

J-P

Reply