Thread: Excel Import Error

I am trying to import a large amount of data through the Products -> Import/Export -> Excel Import feature of the Admin website. There are more than 30000 products to import and I have made sure the Excel file meets the requirements for the import. However once I click the Import button I get the following error back: Unable to read beyond the end of the stream. I know the error happens at the LoadSheet method of the ExcelToXml class but I am unable to debug that since there is no source code for that particular library. I have to say the import works fine with about 10000-11000 records so I could break my data into 4 smaller files and import them individually but wanted to post the question just in case there is any other solution to that. Any information on this is greatly appreciated!

You can try setting the maximum request length higher with these directions, but you'll most likely just have to run that in smaller sections.

Have you tried doing a direct input into SQL using INSERT statements?

We really don't recommend direct INSERTs for importing product data. You're dealing with at least 2 tables (Product & ProductVariant) where data has to match up properly, and more to handle mapping the products to entities. Using any of the 3 built-in import tools (Excel, XML, or WSI) will make sure that everything is added correctly.

I agree, I'd rather use the import since I am also importing categories and there will be too much of manual work to set up all the relationships. I have already increased the max request timeout but the error is still happening. Something happens at the conversion logic (Excel to XML) but cannot tell what is causing it. Anyway I will just break the file into smaller pieces since this is working fine.
Thank you for replying!

how big is the xls file? The way we actually process xls imports also is to convert it to a WSI xml import file

you can see the xml intermediary file in your /images dir, so take a look there and see how big it is.

WSI therefore actually powers all imports (even from xls)

The error happens before the xml file gets created. More specifically it happens when the LoadSheet method of the ExcelToXml class is called:
XmlDocument xmlDoc = exf.LoadSheet("Sheet1", "CA", MaxRowsInSpreadsheet, "A,AS");

The Excel file is almost 18 Mb.

Never ran into this kind of issue before, as I haven't tried xls import with that size. Have you tried doing it by batch instead? Like, split it into 4 xls files or something like that? We can take a look at this if you can send a ticket support with the import file attached.

Thank you! Yes, breaking the file into 4 smaller ones works fine but I wanted to find out if there is another solution to this.

The tool just isn't designed to handle import files that large - 18MB is a heck of a lot to process when it's just text data. :-)

Just break the import into smaller files, or use WSI - that's really the preferred tool for large imports anyway, and is much more flexible.