Php + Mysql - Is There An Alternative To Load Data Infile?

[willothewisp]

Hi,

 

I've written a website which downloads a large CSV file from a trusted affiliate marketing source (once every 24 hours, using a Cron script), then puts the content of the CSV file into a products table in a MySQL database. The CSV file consists of 50,000 - 100,000 products.

As the CSV file is so large, I've written the script to use MYSQL command LOAD DATA LOCAL INFILE, which should automatically read the CSV file and import it into the products table for me. I've got it working locally, but not on the production server.

At first I couldn't understand why it wouldn't work, then my hosting company told me that as a matter of company policy, they didn't allow load data local infile statements.

I asked them for alternatives and so far, they've come up with nothing.

 

One obvious alternative would be to read the CSV file and iterate through it line by line, creating an SQL Insert statement for every line. But I'd imagine that would be very slow, and with 50 - 100,000 products, put an enormous load on the server.

 

Does anyone know of an alternative method that I could use to read in this large file? Or alternatively, a hosting company that will let me use load data local infile?

 

Thanks!

[Muddy_Funster]

You can insert multiple lines in a single query, using a php script to build the query and execute it. Although it will be more taxing on the server, as you say. As for alternatives, it all depends on how much you are willing to spend for your hosing, Rackspace is a premium provider here in the UK, but the service is second to none.

[willothewisp]

Hi Muddy_Funster,

I'd thought about doing it that way, but gave up on it as 50K - 100K of products is too many to do that.

I have a VPS with my current host, and I'm hoping that I can enable load data infile on that. If not, I'll have to invest in Premium hosting I guess.

Willothewisp

[trq]

You should have full control of a vps. If you don't, move.

[PFMaBiSmAd]

To use a multi-value INSERT or REPLACE query, you would typically form a query with 5k-10k rows at a time (the limit is the mysql max_allowed_packet setting, default is 1MB, which can be changed if needed.)