CSV Upload using MySQL

[karenn1]

Hi everyone,

 

I have a page where my client can upload a CSV and the data then populates a table in the database. Here's my code:

 

//Upload new data from CSV
$sqlcsv = "LOAD DATA LOCAL INFILE '".$_FILES['file']['tmp_name']."' 
			INTO TABLE members 
			FIELDS TERMINATED BY ','
			(fund_indicator, employer_indicator, name, surname, pension_number, id_number, date_joined_company, date_joined_fund,
			 date_birth, benefit_calc_date, current_annual_salary, gross_monthly_member_contrib, member_contrib_percentage,
			 gross_monthly_comp_contrib, empl_contrib_percentage, monthly_avc_contrib, total_avc_contrib, transfer_in_amount,
			 withdrawel_benefit, early_retire_date, early_retire_benefit_amount, normal_retire_date, normal_retire_benefit_amount, 
			 late_retire_benefit, retrench_benefit, ill_health_benefit, death_benefit, spouse_pension, child_pension, housing_loan
			 )
		  "; 
//30 fields
$resultcsv = mysql_query($sqlcsv); 

 

This works perfectly fine for a smaller size file but as soon as I upload a 38MB file, it doesn't seem to do anything. It just hangs. I've changed the settings on my server to allow for large files. Is there a better way to do this?

 

Also, is there a way to have a upload progress bar of some kind to indicate how far the upload is?

 

 

Thanks in advance!

 

Karen

[PFMaBiSmAd]

Does your code have error checking and error reporting logic in it to test if the upload worked without error before you attempt to use the upload file in the query and to tell you why the upload failed if it did not work?

 

Edit: for the upload progress question - http://sourceforge.net/projects/upu

[karenn1]

No, I don't quite know how to do that. Like I said, the upload didn't fail. In IE, the green blocks at the bottom go to the end but after that, nothing happens.

[PFMaBiSmAd]

The only information your browser can supply is an indication that the file is/was being transfered, it does not know what happens on the web server after the file has been received. So I'll ask again, does your code contain any error checking and error reporting logic to get it to tell you if the upload worked or not or if the mysql query failed and returned an error?

[karenn1]

Thanks for your continued help! This is my full code:

 

//Upload new data from CSV
$sqlcsv = "LOAD DATA LOCAL INFILE '".$_FILES['file']['tmp_name']."' 
			INTO TABLE members 
			FIELDS TERMINATED BY ','
			(fund_indicator, employer_indicator, name, surname, pension_number, id_number, date_joined_company, date_joined_fund,
			 date_birth, benefit_calc_date, current_annual_salary, gross_monthly_member_contrib, member_contrib_percentage,
			 gross_monthly_comp_contrib, empl_contrib_percentage, monthly_avc_contrib, total_avc_contrib, transfer_in_amount,
			 withdrawel_benefit, early_retire_date, early_retire_benefit_amount, normal_retire_date, normal_retire_benefit_amount, 
			 late_retire_benefit, retrench_benefit, ill_health_benefit, death_benefit, spouse_pension, child_pension, housing_loan
			 )
		  "; 
//30 fields
$resultcsv = mysql_query($sqlcsv); 



exit(header("Location: success.php?type=csv_updated"));

 

All I have is an exit header sending the user to a success page. How can I implement the error checking to troubleshoot this?

[karenn1]

Is there anybody else that can please help with this?

 

 

Thanks!

Karen

[PFMaBiSmAd]

For debugging purposes only, to try and find out what is happening once the file has been uploaded, add the following code immediately after your first opening <?php tag and post the resulting output -

 

ini_set("display_startup_errors", "1");
ini_set("display_errors", "1");
error_reporting(E_ALL);
echo "<pre>";
echo "POST:";
print_r($_POST);
echo "FILES:";
print_r($_FILES);
echo "</pre>";

[karenn1]

Thanks for the code. I put this in my page and tried the upload again. But still, after a while, I get a "Internet Explorer cannot display the webpage".

 

Any ideas?

[PFMaBiSmAd]

You need to try different methods to pin down what is happening.

 

1) Try a browser other than IE.

 

2) Temporarily comment out the msyql_query() statement to see if the problem is caused by the mysql portion of the code.

 

3) Putting a header() statement inside of an exit() is highly suspect. Exit() expects either a string or an integer value from the parameter. Header() returns void. Use a separate header() statement followed by an exit; statement (I do vaguely remember someone doing something similar and the resulting server response created a problem for IE.) Also, if that is all the code on the page, there is no need for the exit statement at all and/or why not just put the success logic on the same page where you process the form?

 

4) That cannot be all your code because there is nothing to make the connection to the database server. Something you might be doing in the code leading up to the code you did post could be causing the problem. When you are having a page-wide problem and you don't post all your actual code, it takes forever to solve problems.

[swatisonee]

hi

 

would you be good enough to post your code - i've been trying to work something similar only my files sizes dont exceed 10 mb but i have a problem with the @sql.  the code doesnt find $_FILES and error reporting doesnt show up anything either.

 

thanks