PHP MYSQL CSV Upload

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
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

PHP MYSQL CSV Upload

Post by Flashart »

Hi all

I know this has been done to death, however a lot of scripts I have looked at simply don't work! So I am appealing for help from knowledgeable sages.

Simply put I have a csv file which is downloaded automatically onto my computer. I need a php script to upload this to my mysql server. The csv file has 3 lines which i don't need. The 1st row with a report name, 2nd row with column headings and final row (right at the end) with totals. I won't be able to amend these files before they get uploaded.

I have tried various scripts online (went through 2 pages of google results) and they either don't work or break at some point and I cannot work out why. Can someone show me a simple script that does what I need?

Kind regards
Peter
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MYSQL CSV Upload

Post by califdon »

You don't really need a script to do that. You can simply use the LOAD DATA INFILE command of MySQL. Or you can do it very simply using phpMyAdmin, if that's available to you. However, if you need to do this automatically, for example, and want to use a script, it can be almost a one-liner, again using LOAD DATA INFILE. It will even take care of ignoring your choice of lines at the beginning of your file. Read http://dev.mysql.com/doc/refman/4.1/en/load-data.html
Last edited by califdon on Wed Mar 30, 2011 2:15 pm, edited 1 time in total.
Reason: Just noticed that I incorrectly named phpMyAdmin!
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: PHP MYSQL CSV Upload

Post by Flashart »

Hi thanks

Essentially I want to automate this, hence me doing it with php. Though I see you mention LOAD DATA INFILE which I shall read a bit more.

I do have a script but it only loads the last line. It doesn't load any others! I have echoed the query and it the array does grab all the data from the csv. In case anyone can spot something really simple that I have missed, here is the code:

Code: Select all

<?php

/********************************/
$databasehost = "localhost";
$databasename = "dbname";
$databasetable = "tablename";
$databaseusername ="uname";
$databasepassword = "pword";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "tv_g_ppc_data.csv";

	
$addauto = 1;


if(file_exists($csvfile)){
	$contents = array();
	if (($handle = fopen($csvfile, "r")) !== FALSE) {
	    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	       $contents[] = $data;
	    }
	    fclose($handle);
	}
	//echo '<pre>'.print_r($contents, true).'</pre>';
	unset($contents[0], $contents[1]);//take off the first and second array values
	array_pop($contents);//push the end one off the end of the array

	$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
	@mysql_select_db($databasename) or die(mysql_error());
	
	foreach($contents as $k=>$v){
	
		$linemysql = implode("','",$v);

		if($addauto){//i always put in { as its easier to follow the if statement
			$query = "insert into $databasetable values('','$linemysql')";
		}else{
		$query = "insert into $databasetable values('$linemysql')";
	}
		$queries .= $query . "\n";	
	}

	@mysql_query($query) or die(mysql_error());
	@mysql_close($con);


	if($save) {
	
		if(!is_writable($outputfile)) {
			echo "File is not writable, check permissions.\n";
		}
		
		else {
			$file2 = fopen($outputfile,"w");
			
			if(!$file2) {
				echo "Error writing to the output file.\n";
			}
			else {
				fwrite($file2,$queries);
				fclose($file2);
			}
		}
		
	}


}else{
	echo "File not found. Make sure you specified the correct path.\n";
}

?>
I don't really understand why it only loads the first row. I would be grateful for someone to look this over.

Regards
Peter
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: PHP MYSQL CSV Upload

Post by Flashart »

Sorted. For those wishing to know, here is the code:

Code: Select all

<?php

/********************************/
$databasehost = "localhost";
$databasename = "xxxx";
$databasetable = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "tv_g_ppc_data.csv";

	
$addauto = 1;


if(file_exists($csvfile)){
	$contents = array();
	if (($handle = fopen($csvfile, "r")) !== FALSE) {
	    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	       $contents[] = $data;
	    }
	    fclose($handle);
	}
	//echo '<pre>'.print_r($contents, true).'</pre>';
	unset($contents[0], $contents[1]);//take off the first and second array values
	array_pop($contents);//push the end one off the end of the array

	$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
	@mysql_select_db($databasename) or die(mysql_error());
	
	foreach($contents as $k=>$v){

$linemysql = implode("','",$v);

if($addauto){//i always put in { as its easier to follow the if statement
$query = "insert into $databasetable values('','$linemysql')";
}else{
$query = "insert into $databasetable values('$linemysql')";

}
echo $query. '<br />';
@mysql_query($query) or die(mysql_error());
$queries .= $query . "\n";
}

	@mysql_close($con);


	if($save) {
	
		if(!is_writable($outputfile)) {
			echo "File is not writable, check permissions.\n";
		}
		
		else {
			$file2 = fopen($outputfile,"w");
			
			if(!$file2) {
				echo "Error writing to the output file.\n";
			}
			else {
				fwrite($file2,$queries);
				fclose($file2);
			}
		}
		
	}


}else{
	echo "File not found. Make sure you specified the correct path.\n";
}

?>
This script loses the top 2 rows and the last row. You may wish to increase or decrease this part for your csv file, or comment it all out if you don't want any rows deleted.

Code: Select all

unset($contents[0], $contents[1]);//take off the first and second array values
	array_pop($contents);//push the end one off the end of the array
If anyone has any ideas on how to improve this script (faster, leaner) I would be very keen to hear them.

Best
Peter
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MYSQL CSV Upload

Post by califdon »

Again, I think you can do it with the MySQL command that is specifically designed to do what I understand you are trying to do: LOAD DATA INFILE. It's essentially a one-line script!
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: PHP MYSQL CSV Upload

Post by Flashart »

I will look into this. I'm not sure immediately how i would implement this as the php page is set as a cron job to run every day. I haven't got a huge amount of experience with mysql and my challenge was coding it in php as that is what I am concentrating on learning.

However just because I can do it in php doesn't mean I should, and of course the most efficient way is usually better so if the LOAD DATA INFILE is that much less work, then it makes sense to implement. The angle I'm coming at is I'm trying to improve my php knowledge, and learning these ways of doing things will help cement my knowledge. If i tail off to other things, I may not get a handle on anything!

However the pointer is much appreciated and as I said, I will look into this. If you would be obliging to post how you would code using load data, then I would be very grateful.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MYSQL CSV Upload

Post by califdon »

I think we're not communicating well, and it's perhaps as much my fault as yours. I didn't mean to imply that you couldn't or shouldn't use PHP. What I'm saying is that MySQL has a command (which can be called by PHP) that loads data from a CSV (or some other format) into a MySQL table with just one command. So your entire PHP script might be as simple as:

Code: Select all

<?php
mysql_connect("hostname", "dbusername", "password") or die(mysql_error());
mysql_select_db("databasename") or die(mysql_error());
$sql = "LOAD DATA LOCAL INFILE csvfilename
  INTO TABLE destinationtablename 
  FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    ESCAPED BY '\'
  LINES TERMINATED BY '\n' ";
mysql_query($sql) or die(mysql_error());
echo "Done!";
That's just off the top of my head, so you shouldn't sue me if there's an error! And of course you must substitute your actual names and passwords. And since I don't know the details of your project, there could be some complication that I haven't thought of.

Read this: http://support.modwest.com/content/6/25 ... mysql.html and the MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/load-data.html.
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: PHP MYSQL CSV Upload

Post by Flashart »

I understood what you were referring to. Apologies if my reply came across oddly.

I see how you mean and it certainly looks simple. I will look into that further with those links you supplied as the data within the csv file has a few lines I need to delete (2 top rows and a bottom row) and from your example that wouldn't happen however there no doubt is a way of that being done.

Thank you for providing me with the insight there. It is much appreciated!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MYSQL CSV Upload

Post by califdon »

When you read those references, you will see that the command can include IGNORE n lines at the beginning. You may have to resort to a bit of PHP code to remove the last line, but it's very simple.
Post Reply