Thread: Excel Product Import Error

We have 35,000 products to import into the database. When we attempt to load anything over 1283 excel rows it fails with the following error:

"File Uploaded Error:
Exception=Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."

If we upload anything at or under 1283 rows it imports fine. In both cases the excel file is uploaded just fine. It is just when it attempts to import the data into the DB that it fails.

This seems like a strange error to be receiving when the Permissions are obviously okay. The upload function is successful on any sized excel file, and the import is completely successful on the smaller (1283 Rows and under) files.

Here are some additional tid-bits to help with the troubleshooting. It is hosted on the Rackspace cloud. The Storefront version is ML 8.0.1.2. Let me know if more is needed to help troubleshoot this.

We have been working on this for a couple weeks now. Any help would be greatly appreciated.

Thx!

we also use the excel import
I'll throw in my two cents not knowing your exact configuration
I believe asp.net has a size limitation of 8192 the default is 4096

so we added this to the web config
<!-- this is for uploading files bigger than 4096MB web.config.coments -->
<!-- which is a global override -->
<httpRuntime maxRequestLength="8120" />

Thanks for the reply but, the file size we are uploading is not even half a meg. And the upload portion works. It is just the import into the database that fails. The file size of the Excel file that does not import is 312,320 bytes (305 KB).

One more thing that might be of interest is that the file size of the excel file that works and the one that fails is identical. Don't know how that is possible. because one file has an extra row, but the size in the properties of each file is exactly the same.

My guess is that this is more an issue with the number of rows that SQL Server will allow to process. But I have never had this issue before. So I am stumped.

Are you able to give the stack trace of the exception? I'm drawing a blank as to what would throw a SecurityPermission exception for only a certain amount of rows in the import code otherwise.

Custom Errors are turned off, but the admin import page has error trapping built around the upload Click event. I am sure the error bubbles up from the core import routines, but I don't have the official stack trace to be sure. I just have the exception message that bubbled up and was displayed nicely on the import page.

I too find the permissions exception that was produced to be very unexpected, and I am stumped. We have begun breaking the 35000 records up to 30 individual files to import as a poor work around, but I sure would like to find a solution if possible. If anyone has some additional thoughts I would sure be excited to hear them.

Thx!

Could you try replacing line 134 in importProductsFromExcel.aspx.cs with:

Code:

resetError("File Uploaded Error:<br/>" + CommonLogic.GetExceptionDetail(ex, "<br/>")+ex.StackTrace+"<br/>", true);

This modification adds the stack trace information right after the Exception message.

Here is the Error Message & Stack Trace:

Exception=Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at GemBox.Spreadsheet.WeakHashtable.Find(HashtableEle ment el) at GemBox.Spreadsheet.HashtableElement.FindExistingOr AddToCache() at GemBox.Spreadsheet.CellStyle.Consolidate() at GemBox.Spreadsheet.CellStyle.AddToQueue() at GemBox.Spreadsheet.CellStyle..ctor(CellStyle style, WeakHashtable parentCollection) at GemBox.Spreadsheet.ExcelCell.set_Style(CellStyle value) at GemBox.Spreadsheet.XLSFileReader.SetCell(ExcelWork sheet ws, Int32 rowIndex, Int32 columnIndex, Int32 styleIndex, Object valInternal) at GemBox.Spreadsheet.XLSFileReader.ImportRecords(Abs XLSRecords records, String diagnosticsFileName) at GemBox.Spreadsheet.ExcelFile.LoadXls(Stream stream, XlsOptions xlsOptions) at AspDotNetStorefrontExcelWrapper.ExcelToXml.LoadShe et(String SheetName, String LastCol, Int32 MaxRows, String LastRowColsRequired) at AspDotNetStorefrontCore.Import.Aofs38Y9g(String ) at AspDotNetStorefrontCore.Import.ProcessExcelImportF ile(String ExcelFile) at AspDotNetStorefrontAdmin.importProductsFromExcel.b tnUpload_Click(Object sender, EventArgs e)

You may want to try our App for imports to your database:
http://www.simrun.com/AspDotNetStore...Commander.aspx

Anybody have any thoughts about getting this to work in the admin site. The stack trace should help with the troubleshooting process.

Alkaline, thanks for the suggestion. I tried Commander and found it to be very rough around the edges, the program was not very polished off. It kept crashing with error messages or locking up. I love the concept if I could trust the program. It makes me nervous that it would crash in the middle of a large import.

I'm not 100% certain this will work but try copying all the cells in sheet1 and then do a paste values in the same sheet.

To be honest we had decent amount of issues with spreadsheets reading correctly. That we eventually opted to go with buying and implementing the newest version of Gembox Spreadsheet ourselves. Since doing the conversion we haven't had any spreadsheet read issues.

Originally Posted by jghonn

Anybody have any thoughts about getting this to work in the admin site. The stack trace should help with the troubleshooting process.

Alkaline, thanks for the suggestion. I tried Commander and found it to be very rough around the edges, the program was not very polished off. It kept crashing with error messages or locking up. I love the concept if I could trust the program. It makes me nervous that it would crash in the middle of a large import.

Have you tried R2? Its a complete code re-write from the original, We found that we had to create various SQL stored procedures instead of relying only on WSI calls (many of the errors can attributed to WSI call error). Commander is used by about 700 sites with good success. If you are uploading a LARGE amount of products you want to use the WSI interface instead of the excel.

http://www.aspdotnetstorefront.com/p...commander.aspx

Originally Posted by mmcgeachy

I'm not 100% certain this will work but try copying all the cells in sheet1 and then do a paste values in the same sheet.

To be honest we had decent amount of issues with spreadsheets reading correctly. That we eventually opted to go with buying and implementing the newest version of Gembox Spreadsheet ourselves. Since doing the conversion we haven't had any spreadsheet read issues.

MMCGEACHY, thanks for your replies. How long ago did you decide to update Gembox? It looks like the ASPDNSF software uses version 2.7? Which version did you purchase? Was your implementation of the new version as simple as just replacing the Gembox DLLs in the bin folder?

I appreciate your help... Thank you
~Jason

Originally Posted by jghonn

MMCGEACHY, thanks for your replies. How long ago did you decide to update Gembox? It looks like the ASPDNSF software uses version 2.7? Which version did you purchase? Was your implementation of the new version as simple as just replacing the Gembox DLLs in the bin folder?

I appreciate your help... Thank you
~Jason

We decided to update GemBox on our own about a year ago. We purchased GemBox.Spreadsheet Professional 3.3. Unfortunately no the implementation isn't as easy a replacing the GemBox DLLs. This is because there are version specific references in the code that can not be changed and the wrapper would try to apply the wrong license key. To get past the version specific references I had to put both versions of GemBox.Spreadsheet into the GAC. For the license key issue I modified the import code not to use the excel wrapper and use GemBox.Spreadsheet directly instead.