willothewisp Posted December 12, 2012 Share Posted December 12, 2012 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! Quote Link to comment https://forums.phpfreaks.com/topic/271898-php-mysql-is-there-an-alternative-to-load-data-infile/ Share on other sites More sharing options...
Muddy_Funster Posted December 12, 2012 Share Posted December 12, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/271898-php-mysql-is-there-an-alternative-to-load-data-infile/#findComment-1398883 Share on other sites More sharing options...
willothewisp Posted December 12, 2012 Author Share Posted December 12, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/271898-php-mysql-is-there-an-alternative-to-load-data-infile/#findComment-1398886 Share on other sites More sharing options...
trq Posted December 12, 2012 Share Posted December 12, 2012 You should have full control of a vps. If you don't, move. Quote Link to comment https://forums.phpfreaks.com/topic/271898-php-mysql-is-there-an-alternative-to-load-data-infile/#findComment-1398887 Share on other sites More sharing options...
PFMaBiSmAd Posted December 12, 2012 Share Posted December 12, 2012 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.) Quote Link to comment https://forums.phpfreaks.com/topic/271898-php-mysql-is-there-an-alternative-to-load-data-infile/#findComment-1398911 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.