While using the native CRM 2011 data import tool, the default file size that is acceptable is 8 MB. Yes, that is correct - 8MB. Not much, isn't it? Here are a few options to consider when the data to be imported exceeds the file import size.
Even though the default file import size in 8 MB, there is a supported way to increase the data file size to upto 32 MB, by making a change to the config database. But just increasing the file size is not enough, you also need to set the registry settings which would prevent a timeout due to the import running for a long time (I had some imports running for close to 2 hours). Here is a short description of the two registry entries that need to be added:
The OLEDBTimeout value
The unit of the OLEDBTimeout value is seconds. By default, the OLEDBTimeout value is 30 seconds. The OLEDBTimeout value controls the SQL time-out value that is used for a single SQL query. The increased OLEDBTimeout value is useful when the SQL server is overloaded. Additionally, the query takes a longer time to process.
The ExtendedTimeout value
The unit of the ExtendedTimeout value is milliseconds. By default, the ExtendedTimeout value is 1000000 milliseconds. The ExtendedTimeout value controls the ASP.NET time-out value that is used for import requests. The time-out value must be larger than the time for finishing the whole import process. The increased ExtendedTimeout value is useful when the import process takes a long time.
Now there might still be situations when the file size is bigger than 32MB. While researching how to break down the csv, I came across this really cool tool called CSV Splitter.
The CSV Splitter link will download a zip file that contains the compiled exe along with source code. To run the executable, you will need .Net Framework Version 1.1 installed.
Instructions:
1. Fill in necessary information:
CSV file: the path to the CSV that you wanted to split.
Number of lines: the maximum number of lines/rows in each splitted piece.
Max Pieces: limit the number of output files. '0' is unlimited.
2. Click "Split Now!". You can find the split pieces in the a new folder of the same directory of the CSV file.
When you open up the split pieces, you will notice that the first row still contains the right field names. Great time saver!
Even though the default file import size in 8 MB, there is a supported way to increase the data file size to upto 32 MB, by making a change to the config database. But just increasing the file size is not enough, you also need to set the registry settings which would prevent a timeout due to the import running for a long time (I had some imports running for close to 2 hours). Here is a short description of the two registry entries that need to be added:
The OLEDBTimeout value
The unit of the OLEDBTimeout value is seconds. By default, the OLEDBTimeout value is 30 seconds. The OLEDBTimeout value controls the SQL time-out value that is used for a single SQL query. The increased OLEDBTimeout value is useful when the SQL server is overloaded. Additionally, the query takes a longer time to process.
The ExtendedTimeout value
The unit of the ExtendedTimeout value is milliseconds. By default, the ExtendedTimeout value is 1000000 milliseconds. The ExtendedTimeout value controls the ASP.NET time-out value that is used for import requests. The time-out value must be larger than the time for finishing the whole import process. The increased ExtendedTimeout value is useful when the import process takes a long time.
Now there might still be situations when the file size is bigger than 32MB. While researching how to break down the csv, I came across this really cool tool called CSV Splitter.
The CSV Splitter link will download a zip file that contains the compiled exe along with source code. To run the executable, you will need .Net Framework Version 1.1 installed.
Instructions:
1. Fill in necessary information:
CSV file: the path to the CSV that you wanted to split.
Number of lines: the maximum number of lines/rows in each splitted piece.
Max Pieces: limit the number of output files. '0' is unlimited.
2. Click "Split Now!". You can find the split pieces in the a new folder of the same directory of the CSV file.
When you open up the split pieces, you will notice that the first row still contains the right field names. Great time saver!
No comments:
Post a Comment