View Full Version : Inserting Excel Sheet data into Mysql; does not work

11-29-2006, 07:51 AM

I am trying to insert excel sheet data into mysql database. First, I convert that file into comma delimited CSV and then apply this code.

$link = mysql_connect("localhost", "user", "pass");

$file = $_FILES['excel_file']['name'];
@copy($_FILES['excel_file']['tmp_name'], "files/$file");
$query = 'LOAD DATA INFILE /www/trisha/admin/"'.$file.'" INTO TABLE student_info FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";';


My purpose is
1; upload csv file
2; run load command and insert that file data into DB
2; remove that uploaded file

Please advise what I am doing mistake.


11-29-2006, 11:09 AM
What kind of error or warning did you recieve when you try to insert csv file

11-29-2006, 12:23 PM
The path where the uploaded file is copied to is something like current_folder_this_script_is_in/files/ The path that is being put into the query should at least end in .../files/ if it is referring to the same location.

The copy(...) statement has error output suppressed with an @ sign. The upload could be failing, which would cause the copy to fail, or the copy could be failing due to a missing folder, or a folder/file permissions problem. The code needs to check for errors as the result of the upload (did the file get uploaded) and check if the copy worked, before attempting to load the file into the database.

Only the .$file. portion of the path/file in the query is surrounded with quotes. The whole path/file needs to be within quotes. From the mysql manual -

LOAD DATA [LOCAL] INFILE 'file_name' INTO TABLE tbl_name ...

Without the LOCAL keyword, the file must be placed within the mysql server's file system and be directly accessible and readable by the mysql server. If this is not the case, you need to use the LOCAL keyword to get this to be able to read the file from where it is accessible by PHP.

The mysql query has no error checking and reporting -

mysql_query($query) or die('Query Failed: ' . mysql_error());