[SOLVED] reading excel sheet and storing in mysql

[shane07]

Hello to all

Plz somebody help me with reading excel sheet and storing the data in the mysql table.

Waiting for your reply.

Thanx

[vbnullchar]

Simply export data from excel as TXT or CSV.

 

then

 

LOAD DATA INFILE 'path/file.csv' INTO TABLE your_table; 

[shane07]

Simply export data from excel as TXT or CSV.

 

then

 

LOAD DATA INFILE 'path/file.csv' INTO TABLE your_table; 

 

Is that the sql statement that can be used in php script?

Thank you for ur reply but what should I do if I have more than two tables referenced to same excel sheet

[pdkv2]

use php pear classes for that

 

or else if you are using this feature on windows you can create the datasource with excel and query the excel sheet to retrieve the values.

[shane07]

use php pear classes for that

 

or else if you are using this feature on windows you can create the datasource with excel and query the excel sheet to retrieve the values.

 

Thank you I m using windows for testing. But using linux for online testing. Does the php pear class is supported by both windows and linux?

[pdkv2]

Yes you can use php pear classes across the platforms

 

[shane07]

Yes you can use php pear classes across the platforms

 

 

I searched for that class in pear.php.net but only found spreadsheet excel writer. Actually I have never used pear classes before. Can u suggest me more on this class?

[ratcateme]

try saving your excel sheet in txt format then writing php to insert it into your database

 

Scott.

[ratcateme]

I haven't tested this but it should work for a sheet saved in txt format

 

<pre>
<?php
$con=mysql_connect();
mysql_select_db('test');
$sheet='test.txt';
$file=fopen($sheet,'r');
while(!feof($sheet)){
$line=fgets($sheet);
$line=explode("\t",$line);
$query='INSERT INTO `table` VALUES (';
foreach($line as $key => &$value){
	if($$key==0){
		$query.="'".$value."'";
	}else{
		$query.=", '".$value."'";
	}
}
$query.=');';
echo $query."\n";
mysql_query($query,$con);

}
fclose($file);


?>
</pre>

Or if you save it as CSV change the explode from "\t" to ","

 

Hope this helps.

Scott.

[shane07]

I haven't tested this but it should work for a sheet saved in txt format

 

<pre>
<?php
$con=mysql_connect();
mysql_select_db('test');
$sheet='test.txt';
$file=fopen($sheet,'r');
while(!feof($sheet)){
$line=fgets($sheet);
$line=explode("\t",$line);
$query='INSERT INTO `table` VALUES (';
foreach($line as $key => &$value){
	if($$key==0){
		$query.="'".$value."'";
	}else{
		$query.=", '".$value."'";
	}
}
$query.=');';
echo $query."\n";
mysql_query($query,$con);

}
fclose($file);


?>
</pre>

Or if you save it as CSV change the explode from "\t" to ","

 

Hope this helps.

Scott.

 

this means I have to remove the titles(field titles) from the excel sheet. Otherwise the field titles are also inserted into the database, right?

 

But the code have two errors:

while(!feof($sheet)){ //should be $file instead of $sheet, right?
$line=fgets($sheet);

 

Thank you for helping me

[shane07]

I haven't tested this but it should work for a sheet saved in txt format

 

<pre>
<?php
$con=mysql_connect();
mysql_select_db('test');
$sheet='test.txt';
$file=fopen($sheet,'r');
while(!feof($sheet)){
$line=fgets($sheet);
$line=explode("\t",$line);
$query='INSERT INTO `table` VALUES (';
foreach($line as $key => &$value){
	if($$key==0){
		$query.="'".$value."'";
	}else{
		$query.=", '".$value."'";
	}
}
$query.=');';
echo $query."\n";
mysql_query($query,$con);

}
fclose($file);


?>
</pre>

Or if you save it as CSV change the explode from "\t" to ","

 

Hope this helps.

Scott.

 

this means I have to remove the titles(field titles) from the excel sheet. Otherwise the field titles are also inserted into the database, right?

 

But the code have two errors:

while(!feof($sheet)){ //should be $file instead of $sheet, right?
$line=fgets($sheet);

 

Thank you for helping me

The topic is solved but is there a way to exclude the field titles of the excel sheet while reading? Waiting for ur reply.

Thank You

[ratcateme]

If all your titles are on the first or second lines you could exclude those lines from the the function

change your code to this.

 

<pre>
<?php
$con=mysql_connect();
mysql_select_db('test');
//number of lines to skip
$skip=1;
$current_line=1;


$sheet='test.txt';
$file=fopen($sheet,'r');
while(!feof($file)){
$line=fgets($file);
//add this
if($current_line>$skip){


	$line=explode("\t",$line);
	$query='INSERT INTO `table` VALUES (';
	foreach($line as $key => &$value){
		if($$key==0){
			$query.="'".$value."'";
		}else{
			$query.=", '".$value."'";
		}
	}
	$query.=');';
	echo $query."\n";
	mysql_query($query,$con);
//add this
}
$current_line++;


}
fclose($file);


?>
</pre>

 

Sorry my server is still down so i cannot test it.

but i have fixed the $sheet to $file sorry about that.

 

Scott.

 

[shane07]

Well that was so easy. I didn't think that way. Thanx very much for your help.