Importing XLS File with 18,000 products

Quote

Post by Sheldon.Kirk » Thu May 19, 2011 4:46 pm

Hey all,

I currently have the following problem with one of our client's websites, in which they
can't Export or Import the XLS Spreadsheet using the XLS Import/Export module.

We try to export the XLS spreadsheet now, which it used to do fine, but now it just exports a 0.4KB file and that's it.

Inside of the XLS file it has the following error:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 78 bytes) in /home/betterbo/public_html/system/database/mysql.php on line 29
There is 18,000 products in the store which is the most we have ever dealt with,but we know it should be possible to export these to an XLS update them and Import them again.

We also have the problem with it importing, we can't import, it times out, the XLS file we try to import to the database is 21.6MB which is quite large.

Is anybody here at all capable of fixing this so my client can export and import fine on our server? Our Server Settings are set to as follows: (Quite High)

I have changed the following and it still doesn't work!

Max cPanel process memory from “512” to “1024”.
UcPanel PHP max execution time from “90” to “500”.
cPanel PHP max POST size from “55” to “110”.
cPanel PHP max upload size from “50” to “2047”.

Any ideas peeps? Willing to pay you to fix this for us.

Thanks
Sheldon

Image

Sparx Web Solutions - Affordable Web Design & Internet Marketing

New Zealand Web Design

User avatar
Sheldon.Kirk
Active Member
Posts
113
Joined
Fri Jul 03, 2009 5:58 am
Location - Tauranga, New Zealand
Top

Re: Importing XLS File with 18,000 products

Quote

Post by Mathijs de Vries » Thu May 19, 2011 6:11 pm

can't you upload it by ftp?
All of my uploaded spreadsheets show up at ftp also.(I don't know where exactly).
Don't know if it will work, but it might be worth a try.

Yours,
Mathijs

My projects:

- http://www.vskins.nl (own site)
- http://www.devriesict.nl
- http://www.zwedenrelax.nl
- http://www.actietrend.nl

Version in use: 1.5.0.5

User avatar
Mathijs de Vries
Active Member
Posts
231
Joined
Fri Feb 11, 2011 2:37 am
Location - Friesland, The Netherlands
Top

Re: Importing XLS File with 18,000 products

Quote

Post by Sheldon.Kirk » Thu May 19, 2011 6:19 pm

Hi Mathijs,

Where would the spreadsheet show on the server?

If I could upload this via FTP, and then pull into the website that would be awesome!

Thanks
Sheldon

Image

Sparx Web Solutions - Affordable Web Design & Internet Marketing

New Zealand Web Design

User avatar
Sheldon.Kirk
Active Member
Posts
113
Joined
Fri Jul 03, 2009 5:58 am
Location - Tauranga, New Zealand
Top

Re: Importing XLS File with 18,000 products

Quote

Post by Mathijs de Vries » Thu May 19, 2011 7:39 pm

I tried to find it, but they got removed since I changed my serverlocation.

But I thought of another solution.
1. Open the file with Excel.
2. Split up the products in groups of (for example 1 or 2k) and make new files by yourself.
3. Make sure the product ids stay the same.
4. Use the import/export function and upload them per 1 - 2k products.(that might solve the memory problem)

Give that a try, so you might have a temporary solution.

Yours,
Mathijs

My projects:

- http://www.vskins.nl (own site)
- http://www.devriesict.nl
- http://www.zwedenrelax.nl
- http://www.actietrend.nl

Version in use: 1.5.0.5

User avatar
Mathijs de Vries
Active Member
Posts
231
Joined
Fri Feb 11, 2011 2:37 am
Location - Friesland, The Netherlands
Top

Re: Importing XLS File with 18,000 products

Quote

Post by Sheldon.Kirk » Thu May 19, 2011 7:45 pm

Hi Mathijs,

Thanks for taking time to respond.

The only problem with doing that is that the Excel replaces EVERYTHING in the database, it doesn't just add to the existing amount, that's the problem!

So that unfortunately wouldn't work.

Thanks
Sheldon

Image

Sparx Web Solutions - Affordable Web Design & Internet Marketing

New Zealand Web Design

User avatar
Sheldon.Kirk
Active Member
Posts
113
Joined
Fri Jul 03, 2009 5:58 am
Location - Tauranga, New Zealand
Top

Re: Importing XLS File with 18,000 products

Quote

Post by Mathijs de Vries » Thu May 19, 2011 7:51 pm

Bummer.
You're welcome.

Maybe someone else got a solution.

My projects:

- http://www.vskins.nl (own site)
- http://www.devriesict.nl
- http://www.zwedenrelax.nl
- http://www.actietrend.nl

Version in use: 1.5.0.5

User avatar
Mathijs de Vries
Active Member
Posts
231
Joined
Fri Feb 11, 2011 2:37 am
Location - Friesland, The Netherlands
Top

Re: Importing XLS File with 18,000 products

Quote

Post by Qphoria » Thu May 19, 2011 8:27 pm

This has been a long known issue. JN was looking into breaking it into smaller chunks at one point I think, and also looking at an "append" mode.. but I have no idea how complex that would be. Perhaps if there was a way to ftp it and then run server-side executions to load it in.

Image

User avatar
Qphoria
Administrator
Posts
20056
Joined
Tue Jul 22, 2008 3:02 am
Top

Re: Importing XLS File with 18,000 products

Quote

Post by SXGuy » Thu May 19, 2011 8:45 pm

I know of a solution, bit long winded, but only working solution as of now that i can think of.

Firstly you need to set up your pc with:

XAMPP
install plugins for phpmyadmin and mysql (should come pre packed)

Back up your sql database from your live store, and download your whole store into a folder
place that folder in your XAMPP/htdocs/ folder

run phpmyadmin from xampp control panel.
import backup of your sql database.
Navigate to http://localhost/yourfoldername

now you have your live site running on your local server.

Import your excel file via the locally installed website.

back up your database.
import database to your online sql database.

Do the same for exports but in reverse order.

Hope that helps.
SXGuy
Active Member
Posts
1909
Joined
Sun Nov 08, 2009 2:07 am
Top

Re: Importing XLS File with 18,000 products

Quote

Post by Sheldon.Kirk » Fri May 20, 2011 7:00 am

Thanks for that solution, I have been doing that up until now, but the client really just wants to be able to it themselves.

Thanks very much - will have to just let them know it can't be done at this stage!

My server technicians manage to fix the Exporting problem, just back to the importing problem now.

If I do get it fixed I will post up here with a solutions.

Thanks
Sheldon

Image

Sparx Web Solutions - Affordable Web Design & Internet Marketing

New Zealand Web Design

User avatar
Sheldon.Kirk
Active Member
Posts
113
Joined
Fri Jul 03, 2009 5:58 am
Location - Tauranga, New Zealand
Top

Re: Importing XLS File with 18,000 products

Quote

Post by jaggy » Fri May 20, 2011 12:45 pm

Have you tried allocating memory a bit higher?

e.g. @ini_set('memory_limit', '40M');

modify admin/index.php and add that after <?php

if error still occurs .. just set a bit higher.
jaggy
New member
Posts
21
Joined
Mon May 04, 2009 7:02 pm
Location - Philippines
Top

Re: Importing XLS File with 18,000 products

Quote

Post by frank5050 » Fri Jan 13, 2012 5:47 am

Hi Sheldon,

You wrote : "My server technicians manage to fix the Exporting problem"

How did you manage to resolve the export issue?

Strangely, I can import 15,000 products, but can't export the file. I'm getting the same error as you reported.

Thanks
frank5050
Newbie
Posts
4
Joined
Fri Jan 13, 2012 5:44 am
Top

Re: Importing XLS File with 18,000 products

Quote

Post by madimar » Fri Jan 13, 2012 6:07 am

Guys, for the wellknown memory issues, there are no solutions... You should need a dedicated server. No hosting providers allow to have the huge php memory limit required for that number of records.

The only solution I know ( and I'm using) to overcome the general issue is to not use xls import/ export tool... You can obtain more or less the same(for import, not for export) using csv import tools (total import pro seems promising) for that.
This is my humble opinion
Cheers,
M

Sent from my Desire HD using Tapatalk

-----------------------------------------------------------------------
My last mods: Partita IVA e CF | Pro EU VAT Number | Sales Agents | Pricelist Pro
-----------------------------------------------------------------------

User avatar
madimar
Active Member
Posts
1477
Joined
Thu Sep 24, 2009 6:27 pm
Top

Re: Importing XLS File with 18,000 products

Quote

Post by frank5050 » Sat Jan 14, 2012 1:30 am

I've set up opencart on localhost, everything works fine. But for some reason I am even unable to export 15000 products.

Exporting 15,000 products was the first reason why I set up OC on localhost.

Can anyone suggest anything? Are there any settings that need to be changed?

Thanks
frank5050
Newbie
Posts
4
Joined
Fri Jan 13, 2012 5:44 am
Top

Re: Importing XLS File with 18,000 products

Quote

Post by frank5050 » Sat Jan 14, 2012 1:31 am

Also I wanted to ask: how long does it usually take to export a list of 15,000 products on localhost. I know these durations aren't exact, just trying to get an idea.
frank5050
Newbie
Posts
4
Joined
Fri Jan 13, 2012 5:44 am
Top

Re: Importing XLS File with 18,000 products

Quote

Post by i2020vision » Fri Feb 10, 2012 10:30 pm

SXGuy wrote:I know of a solution, bit long winded, but only working solution as of now that i can think of.

Firstly you need to set up your pc with:

XAMPP
install plugins for phpmyadmin and mysql (should come pre packed)

Back up your sql database from your live store, and download your whole store into a folder
place that folder in your XAMPP/htdocs/ folder

run phpmyadmin from xampp control panel.
import backup of your sql database.
Navigate to http://localhost/yourfoldername

now you have your live site running on your local server.

Import your excel file via the locally installed website.

back up your database.
import database to your online sql database.

Do the same for exports but in reverse order.

Hope that helps.
I'm trying to do this but having no luck. I still get the memory limit error. I've bumped up the memory to 2096 and set the timeout to 0. It's been an hour and it's still exporting.
i2020vision
New member
Posts
21
Joined
Fri Nov 18, 2011 12:19 am
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: No registered users and 67 guests