Uploading large file into database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Uploading large file into database

Post by marnieg »

I have a script that is trying to read in a large csv file (7MB) and insert the data into my database (MySQL). I use the same script for smaller files and it works so I'm thinking it is one of my php variables not set high enough to read in the data. Here is my php5.ini file

Code: Select all

register_globals = on
allow_url_fopen = off

expose_php = Off
max_input_time = 2400
max_execution_time = 2400
upload_max_filesize = 192M
memory_limit = 256M
variables_order = "EGPCS"
extension_dir = ./
upload_tmp_dir = /tmp
precision = 12
SMTP = relay-hosting.secureserver.net
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=,fieldset="
Here is my script file.

Code: Select all

							 $lineseparator = "\n";
                             $fieldseparator = ",";
                             $csvfile = "upload/products_1.csv";
							 if (!file_exists($csvfile))
							 {
							 echo "file not found. Make sure the products_1.csv file is in the upload folder.\n";
							 exit;
							 }
							 $file = fopen($csvfile,"r");
							 if (!file) 
							 { echo "error opening data file. \n"; 
							 exit;
							 }
							 $size = filesize($csvfile);
							 if (!size) { 
							 echo "file is empty.\n";
							 exit;
							 }
							 $csvcontent = fread($file,$size);
							 fclose($file);
							 $lines = 0;
							 $queries= "";
							 $linearray = array();
							 foreach(split($lineseparator,$csvcontent) as $line)
							 {
							 $lines++;
							 $line = trim($line,"\t");
							 $line = str_replace("\r","", $line);
							 $linearray = explode($fieldseparator,$line);
							 $linemysql = implode("','", $linearray);
							
							 $query = "insert into prevc_products values ('$linemysql');";
							 $queries .= $query . "\n";
							 @mysql_query($query);
Here is my phpinfo.php settings for this site and hosting company (GoDaddy)
http://www.datacaresol.com/phpinfo.php

Thanks for any suggestions with this problem.
Marnie
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Uploading large file into database

Post by califdon »

I'm not sure why your script isn't working, but the question that immediately comes to my mind is: If you have a CSV file, why aren't you simply using MySQL's LOAD DATA INFILE LOCAL syntax? I would expect just one line of code to replace substantially all of your code.
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Re: Uploading large file into database

Post by marnieg »

I have read about "Load Data" syntax but wasn't sure my hosting account would allow me to use that. I thought that was only allowed on dedicated servers, not shared hosting. I know that if I go into my database through my GoDaddy account and use phpmyadmin it has the option for "Load Data" but didn't think I could use that in my script.

Please advise.
Thanks,
Marnie
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Re: Uploading large file into database

Post by marnieg »

I believe the issue is still with my php settings of variables. I reduced the size of my input file from 35,000 records to 20000 and the script worked. Therefore my script is correct I just need more direction on which variables are not set correctly. Again look at my phpinfo.php of my site and the variables I have set in the php5.ini file. See first post for this information.
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Re: Uploading large file into database

Post by marnieg »

I also have noticed that if my fields have any commas in them it is disrupting the insert statement. I have the fieldseparator in my script set to "," but I think it needs to be something else in the case where I have this situation.

For example one of my fields is a text field. The original field has "R90L,1.5G,2GF/1GR,US", but the script is treating it as '"R90L','1.5G','2GF/1GR','US"' when it performs the explode or implode.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Uploading large file into database

Post by califdon »

That's correct, you must use a field separator that will definitely NOT be included in your data.
Post Reply