Import 600MB csv into database

[mandeep82]

Tweet

Hi all

I am a newbie and downloaded the 600mb datafeed from affiliate window. How to import this data into mysql?

if i use "wget" and try to download the file from web directly , it isnt downloading the whole file and shows it is done. Importing retailer by retailer will take ages. and i could not find any way of doing this?

Please help!!

[tbp]

Thats a big file, and will take a fair bit of memory on the server.

You`ll probably end up having to upload the file to your server, and then write a script to open the file, and read it in line by line, and then inserting each line into the database as you go. Would be the most memory efficient way of doing it, although it would take a fair amount of time to run as thats a huge number of records to be inserted!

You'll need to add:

set_time_limit(0) and ignore_user_abort() 

to your script to keep it running for that amount of time without timing out (although it's possible that the hosts settings may over rule that).

I think your best bet would be to contact your host and get them to help you with it. Otherwise, you may need to split it into several smaller files, and then process each one.

What exactly is it thats 600mb? Is it a file containing every single retailer on Affiliate Window or similar, as thats a huge amount of data! Assuming each record is 1/2Kb thats 1,228,800 records. If that is the case it would be much better to download all the files separately, and then write a loop to read in each file and add it.

[mandeep82]

thanks

yes i have the file on the server but to use php for inserting it into database , need to allocate more then 2GB of memory which is not good at all.

it is the sheet with the products of most of the retailers on affiliate window.

is there anything like "insert into ...... limit rows" to do this or i need to break the file into small parts n do the same??

[tbp]

I think the best way would be to break it down into smaller chunks.

You can retrieve product feeds from Affiliate Window through a URL, so you could create a PHP script that had a list of all the product feed url's, and then loop through them downloading the feed and importing it one by one. This way it would still be automated, but because you're working with much smaller files it won't use anywhere near as much memory.

Alternatively, you could download the products in XML format, and their are parsers that will read only the current chunk of data into memory at a time. Although it would still take a long time to run, memory and resources wise it shouldn't use that much.

It's the type of script that should be run from a command line though, rather than through a browser which is why it's probably going to be better to get your host to run it for you.

[mcol]

wow - that's one hell of a niche site.

You really, really don't want to do this. If the load fails fails for any reason, you're probably going to end up looking for a duff record in the middle of a 60mb file. Have you ever tried editing a 60mb file? Or maybe you'll just run out of resources somewhere along the line and have to start all over again, and again, and again. What if one merchant's data is duff and you need to exclude them?

As previously suggested it would be much better to download merchant feeds to a folder and then write a script to loop round the folder adding each in turn.

[mandeep82]

thanks a lot

i will do what you have suggested.

another thing is , is there any search engine that can be used for site having that much of data???
i had a lot less products earlier n was using mysql full text search..

but really need something better n fast.

Please suggest.

[forgettherest]

My advice is to use the LOAD DATA command from the mysql command line, this will avoid having to process it programatically (youll likely run into memory problems too by doing it this way..)

[cDc1979]

another vote for LOAD DATA in mysql (or BULK INSERT in SQL Server...)
its the only efficient way..

[tbp]

The only problem is that by the sounds of it mandeep is on a shared hosting account, so won't have access to the MySQL command line.

Thats the reason I said for him to speak to his host, as they would need to do it for him.