?
Solved

Upload .CSV file into MySql DB using PHP

Posted on 2009-04-19
23
Medium Priority
?
2,779 Views
Last Modified: 2012-05-06
I am new at this & is looking for a csv file upload script using php. The file before being uploaded need to go through some validation (eg, file type & check the various column for integer & date type) before inserting into DB. once validation is done, the file will be inserted into 2 tables in DB.
Can someone please help?
0
Comment
Question by:ARC_UM
23 Comments
 
LVL 6

Expert Comment

by:segurah
ID: 24181375
More that a cvs uploades you probably need and ETL (extraction, transforming and loading) see at : http://en.wikipedia.org/wiki/Extract,_transform,_load
0
 

Author Comment

by:ARC_UM
ID: 24181590
i need the script that will reae the contents of the csv file  & store it into the 2 tables in MySql DB
0
 
LVL 21

Expert Comment

by:K V
ID: 24181867
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:dazweeja
ID: 24182821
This should get you pretty close:
<?php
$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
 
if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}
 
$handle = @fopen("inputfile.csv", "r");
 
if ($handle) {
    while (!feof($handle)) {
        $buffer = fgets($handle, 4096);
        $values = explode(",", $buffer);
		if(count($values == 2)) { // make sure the line is valid
			$col1 = trim($values[0]);
			$col2 = trim($values[1]);
			if(
				($col1 != '') &&
				(is_int($col1)) &&
				($col2 != '') &&
				(is_string($col2))
			) { // sample validation routine
				$sql = "INSERT INTO table_name
					(column_name1, column_name2)
					VALUES
					($col1, $col2);";
	
				$result = mysql_query($sql);
 
				if (!$result) {
					echo "Could not successfully run query ($sql) from DB: " . mysql_error();
					exit; // comment this out if you'd like to continue after errors
				}
			}
			else {
				echo "Data is invalid";
			}
		}
		else {
			echo "Incorrect number of values on line";
		}
    }
    fclose($handle);
}
 
mysql_free_result($result);
?>

Open in new window

0
 
LVL 3

Expert Comment

by:dazweeja
ID: 24182842
Of course, change line 20 to match the number of values you expect on each line of your csv and duplicate the code in lines 29-39 for the second table.
0
 

Author Comment

by:ARC_UM
ID: 24189723
hi dazweeja, thanks for the suggestion. i have the csv file uploaded on the server -- will your code be able to read the file from there. what is 4096 in line 18?
0
 
LVL 3

Expert Comment

by:dazweeja
ID: 24189800
It will be able to read files on the same server - just give it the correct file path. It will also be able to read files on other servers, ie. using a URL as the path, but only if allow_url_fopen is On for that server (the default). See my comments here:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_24334585.html?cid=1066#a24183619

4096 is the number of bytes to reads into memory for each line. This value should handle most situations - it's at least 2048 characters per line - so is commonly used. You can make this value smaller if your lines are short. I think you'd only bother if memory/performance of your website was critical.
0
 

Author Comment

by:ARC_UM
ID: 24191149
it doesn't save the records in DB table
0
 

Author Comment

by:ARC_UM
ID: 24191251
there is also this error i forgot to mention earlier:
Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home1/uploader.php on line 89
0
 
LVL 3

Expert Comment

by:dazweeja
ID: 24191280
My mistake, that line doesn't need to be there so get rid of it. It only applies if you are using SQL SELECT statements and it's in the wrong place anyway. I accidentally copied from another piece of code I had.

Are you getting any other error messages?

Are you sure it is opening your csv file correctly? If you change '@fopen' to 'fopen', you should see an error message on the screen or in your PHP error log if it's unable to open the CSV file. The '@' symbol is there to suppress error messages but at the coding stage, you probably want to see them. When the site is live, you would probably want to change it back to '@fopen' and put this at line 51:

else {
   echo "Problem opening csv file";
}

If the file is opening correctly, have you changed the SQL to match your table structure? As a debugging step, insert this line at line 33 to make sure your SQL command is what you expect:

echo $sql;

In the development stage, you might want to put echo statements (or error_log statements) all through your code to see exactly where the problem is or use a PHP debugger like XDebug or ZendDebugger.
0
 
LVL 3

Expert Comment

by:dazweeja
ID: 24191287
To clarify, I meant delete the line (should be line 89 in your code):

mysql_free_result($result);
0
 

Author Comment

by:ARC_UM
ID: 24200502
i am receiving the following error:
Resource id #13INSERT INTO `gipps_student_info` (SUBMITTER_ID, PROJECT_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, gipps, 200, 2002);Could not successfully run query (INSERT INTO `gipps_student_info` (SUBMITTER_ID, PROJECT_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, gipps, 200, 2002);) from DB: Unknown column 'gipps' in 'field list'

i dont need to add values in sequential order as in the DB table since i am mentioning which columns the values go under? any idea how to solve the problem above?

in my csv if I have row headers, how do i omit while inserting in DB
0
 

Author Comment

by:ARC_UM
ID: 24200553
ERROR MSG:
Resource id #13INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2002)Could not successfully run query (INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2002)) from DB: Duplicate entry '2002' for key 2

looks like it is reading the first line over again
0
 

Author Comment

by:ARC_UM
ID: 24200574
disregard the prev message

i get this error but data is also inserted in the DB:
Resource id #13INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2002)1Data is invalidINSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2003)1Data is invalidINSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (, , )Could not successfully run query (INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (, , )) from DB: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , )' at line 1

how can i solve this bit?
0
 
LVL 3

Expert Comment

by:dazweeja
ID: 24200631
If you are inserting a value into a column and the datatype of that column in your database is not numeric, eg. char, varchar, text, etc., you have to put quotation marks around your values in your SQL. You can also put quotation marks around numeric data as well but it's optional.

For example:
$sql = "INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES ('" . $submitter_id . "','" . $school_name . "','" . $student_code . "');";
 
echo $sql;

Open in new window

0
 
LVL 3

Expert Comment

by:dazweeja
ID: 24200643
Also, your last message seems to suggest that an INSERT is being attempted even when the data is empty or not valid. Have a look at your validation code and put in checks like ($school_name != "") if necessary.
0
 

Author Comment

by:ARC_UM
ID: 24200849
i've uploaded the code as well as a sample csv template i want to insert. i couldn't upload .csv file so you may need to save it as .csv file. users may not have to insert all fields, some may be kept blank.
when reading i want the insertion to exclude/skip the heading row in the csv file
$handle = @fopen($target_path, "r");
 
if ($handle) {
    while (!feof($handle)) 
    {
        $buffer = fgets($handle, 4096);
        $values = explode(",", $buffer);
                if(count($values == 10)) 
                { 
                        $col1 = trim($values[0]);
                        $col2 = trim($values[1]);
                        $col3 = trim($values[2]);
                        $col4 = trim($values[3]);
                        $col5 = trim($values[4]);
                        $col6 = trim($values[5]);
                        $col7 = trim($values[6]);
                        $col8 = trim($values[7]);
                        $col9 = trim($values[8]);
                        $col10 = trim($values[9]);                 
						
			$sql = "INSERT INTO `$_SESSION[student_info]` (SCHOOL_NAME, STUDENT_CODE, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, YEAR_LEVEL, CLASS, GENDER, TEACHER_TITLE, TEACHER_FIRST_NAME, TEACHER_LAST_NAME, PROJECT_ID, SUBMITTER_ID) 
				VALUES ($col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10, '$_SESSION[project_id]','$_SESSION[teachersid]');";
			$result = mysql_query($sql);
			
                        if (!$result) {
                		echo "Could not successfully run query ($sql) from DB: " . mysql_error();
                       		exit; 
                                }
                        
                        else {
                                echo "Data is invalid";
                        }
                }
                else {
                	echo "Incorrect number of values on line";
                }
    }
    fclose($handle);
}
?>

Open in new window

Template-for-insertion.xls
0
 

Author Comment

by:ARC_UM
ID: 24200927
the code inserts the data in teh DB but has the following error:
Data is invalidData is invalidData is invalidData is invalidData is invalid

it inserts an extra blank record for student.
How can i skip reading teh header in the csv file?
0
 
LVL 3

Expert Comment

by:dazweeja
ID: 24200952
I won't be able to test this until I get home but start by putting quotation marks around all the values in line 22:

VALUES ('$col1', '$col2', '$col3',...

It won't hurt if the variables are blank.

If you want to skip the first line, you could do:
    $header_line = true;
    while (!feof($handle)) 
    {
        $buffer = fgets($handle, 4096);
        if($header_line) {
           $header_line = false;
           continue;
        }
        $values = explode(",", $buffer);
                if(count($values == 10))

Open in new window

0
 

Author Comment

by:ARC_UM
ID: 24201063
sorry i put the wrong code in. it inserts properly & there is no error, BUT INSERTS A BLANK RECORD AFTER FINISHING INSERTING FROM THE CSV FILE.
I'LL TRY YOUR CODE TO SKIP THE FIRST LINE
0
 

Author Comment

by:ARC_UM
ID: 24201479
it's not reading header, but inserting a blank record
0
 
LVL 3

Accepted Solution

by:
dazweeja earned 2000 total points
ID: 24201561
Of these columns, is there one that can't be left blank? That is, can you identify one column that you wouldn't want to insert the values into the database if it's not given?

For example, if the data is useless without a SCHOOL_NAME, you could wrap lines 21-31 of your code above with:

if($col1 != "") {
// put code here
}

Otherwise, you could wrap these lines in:

if(($col1 != "") && ($col2 != "") && ($col3 != "")) { // etc.
}

With all the values so it would need at least one value to be given before it inserted a row.

Lastly, lines 10-19 could be condensed into:

$values = array_map("trim", $values);
0
 

Author Comment

by:ARC_UM
ID: 24201602
if i have more problems I'll let you know. Thanks a lot for your help. I already put 1 column to be not empty before you said :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question