Extracting excel data into the mysql data using php

[starrylitgal]

does ne1 knw how to extract ata stored in excel sheets into a MySQL database using PHP and not using some kind of wizards/software tools wich is available on the internet.

 

i have to make a web interface where i will take an excel sheet as the input file and the tables stored in the excel sheet will be stored in the appropriate MySQL database.

 

This backend processing is to be in PHP.

 

Does ne1 knw how to do this or does ne1 have the code or script to do this task??

 

plzz help coz its really urgent!

[Maq]

You could either just explode each line or convert it into a CSV file.

 

Post a sample of what the data looks like.

[starrylitgal]

 

You could either just explode each line or convert it into a CSV file.

 

 

I am new to php ,so can u tell me how to do this conversion of the data into a CSV file. And can u make this point clear that how can i then use the CSV file to put the data into the MySQL tables. Because all these things hav to be done using PHP.

 

Also wht do u mean by exploding each line?

Post a sample of what the data looks like.

 

Sample Data:

 

I have tables in a MySQL database.

For putting data into the tables, i have to use the excel sheets(.xls format)(thts d project requirement).

The excel sheet will hav the following sample format:

 

Say, the Table is Table1:

Column1 Column2

Val1Val2

 

This is the data present in the excel sheet.

 

Now i hav to put these values into the MYSQL database.

I have a table called table1(say) which has the field column1 & column2.

 

So wht i require to do ,using PHP, is that put the values from the excel sheet under each appropriate column i.e. the data "val1" under the "column1" field in the "Table1" table in the database.

 

i hope nw the idea is clear.

 

Plzz if u knw (or ne1 knws) do help me.

[Mark Baker]

does ne1 knw how to extract ata stored in excel sheets into a MySQL database using PHP and not using some kind of wizards/software tools wich is available on the internet.

Whoever set this project is really imposing a serious restriction. Reading Excel requires a lot of development work... and if time is short, that's close to impossible. Several man months of work has gone into each format reader within PHPExcel... your last message indicates an xls file (BIFF 5 or BIFF , rather than the xlsx used by Excel 2007, so you need to read a proprietary, binary format... unless you can get permission to use an existing software tool (or a package such as PHPExcel) to access the Excel data, I thnk you've got several months of long nights ahead of you.

 

[starrylitgal]

 

Whoever set this project is really imposing a serious restriction. Reading Excel requires a lot of development work... and if time is short, that's close to impossible.

 

Ok. But are there ne ready made codes available? If yes then plz let me knw.

 

Several man months of work has gone into each format reader within PHPExcel... your last message indicates an xls file (BIFF 5 or BIFF , rather than the xlsx used by Excel 2007, so you need to read a proprietary, binary format... unless you can get permission to use an existing software tool (or a package such as PHPExcel) to access the Excel data, I thnk you've got several months of long nights ahead of you.

 

Over here u spoke abt reading a proprietary,binary format....i cudnt understand that part...

 

The excel sheets tht i will be using will mostly be in .xls format but may also be in .xlsx format.

 

Aren't there any codes for handling both these formats?

 

Also, if i got permission to use some existing tool how will i embed it in my website..coz everything is going to happen using the web interface??

 

[Maq]

Convert this file into a CSV (save as your_file.csv) and use this code.

 

$handle = @fopen("your_file.csv", "r");
if ($handle) {
    while (!feof($handle)) {
      $buffer = fgets($handle);
	$pieces = explode(",", $buffer);
	$column_1 = $pieces[0];
	$column_2 = $pieces[1];

	echo "$col_1 $col_2 
";

	mysql_query("INSERT INTO table (field_1, field_2) values ('$column_1', '$co,lumn_2')") or die(mysql_error());
}

}
?>

 

[Mark Baker]

Ok. But are there ne ready made codes available? If yes then please let me knw.

There are. The one that I mentioned in my post, called PHPExcel, and that also appears in my signature.

It's a package that is designed to both read and write Excel files from PHP.

 

To start, you need to create a form that will allow the user to upload an Excel file to the web server.

Once the file is on the web server, you can then open it using PHPExcel.

 

/** Include path **/
set_include_path(get_include_path().PATH_SEPARATOR.'./PHPExcel/Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';


$filename = './myWorkBook.xls';
$fileInfo = pathinfo($filename);

switch (strtolower($fileInfo['extension'])) {
case 'xlsx'	: $fileType = 'Excel 2007';
		  $fileReader = 'Excel2007';
		  break;
case 'xls'	: $fileType = 'Excel 5 (BIFF)';
		  $fileReader = 'Excel5';
		  break;
}

$objReader = PHPExcel_IOFactory::createReader($fileReader);
$objPHPExcel = $objReader->load($filename);

This will read the Excel file into a PHPExcel object called $objPHPExcel.

 

You can then access the information in that workbook.

//  Select the current worksheet
$objPHPExcel->setActiveSheetIndex(0);
//  Read value of cells A2 and B2
$cell_value1 = $objPHPExcel->getActiveSheet()->getCell('A2')->getValue();
$cell_value2 = $objPHPExcel->getActiveSheet()->getCell('B2')->getValue();
...
etc

 

 

[Maq]

values ('$column_1', '$co,lumn_2')") or die(mysql_error());

 

Should be:

 

values ('$column_1', '$column_2')") or die(mysql_error());

 

Sorry, I don't really have a testing environment right now 

[starrylitgal]

Wow ! Thnx maq and mark for the quick reply.

 

I will try both the methods.

 

If i hav any problem i will post it here, do help me then too!

 

Thnx a lot!

[Maq]

If you're restricted to use the excel sheets then you have to use mark bakers solution.  What he suggested are just packages that have a special API to handle .xsl(x) files.

 

Good luck!