Bridging data between excel and mysql.

[Stefan]

Ill start by saying that I`m really just looking for directions on the subject matter and not just crying for help.

 

The problem is this, collecting about 150 static data entries by email

and retyping them to excel can be a real pain.

 

Having all my data moved to a database can solve a few minor problems and tons of time.

 

Importing the data via csv is not going to help me! The idea is to make this important task less redundant.

 

I would like to bridge the data in excel to my database or from outlook to my database would be even better.

I say again, if any one has any information on the subject, it would be greatly appreciated.

 

Thank you in advance, regards

 

Stefan

[Mchl]

Someone told me this class is useful

 

http://sourceforge.net/projects/phpexcelreader/

 

I've never tried it though.

[fenway]

Plus, in Mysql 5, you can use CSV directly as a engine type.

[Stefan]

Thank you for your the replays.

 

fenway

Q: How do I check My MySQl version number and could you please elaborate on the subject you mentioned.

 

Mchl

Thanks, i`ll check it out.

 

At the moment I have tons of other work to do and this problem has to wait but Ill check in from time to time.

 

thanks again to everyone hoe helped and ill keep you posted on any updates.

 

Regards, Stefan

[Mchl]

How do I check My MySQl version number

 

mysql_connect(/*put your db credentials here*/);
echo mysql_get_server_info();

[Stefan]

After much needed research on the subject line, my finds are . . well, inconclusive.

 

As for the class PHPExcelReader:

My thanks go out to Mchl for the recommendation

 

The Author/s Vadim Tkachenko and David Sanders have really put great thought into this project! Which is very much appreciated.

 

To Quote S.Bera

This is a nice tool for reading Microsoft Excel files called PHPExcelReader in PHP.

It works with .xls files up to Excel version 2003, which are based on the BIFF format .

It is written in native PHP and does not require any third-party libraries or the MS Office package.

 

Which went as far as to feature this class

at: http://www.scribd.com/doc/2577414/Parsing-Excel-by-PHP#document_metadata

 

That said:

The php script reads each fields e.g. A1 value and its then up too the user/programmer to do with the data what he/she wishes. I`m no expert but I got as far as to include some code to one of the examples given, to add a few values to a MySQL database.

 

// INSERT TABLE
$table = ('CREATE TABLE test(
            id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            value VARCHAR(6) NOT NULL )');

$handel = mysql_query( $table, $connect ) or die('Could not create table: ' . mysql_error());

for($i=0; $i<10; $i++)  { 

// Test CVS
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read("example.xls");
$page = 0;
error_reporting(E_ALL ^ E_NOTICE);
for ($i = 1; $i <= $data->sheets[$page]['numRows']; $i++) { 
for ($j = 1; $j <= $data->sheets[$page]['numCols']; $j++) {
          if($data->sheets[$page]['cells'][$i][$j] != NULL)

$value = " ".$data->sheets[$page]['cells'][$i][$j]." ";

      $info = trim($value);
      $query = "INSERT INTO testtbl (id, value) VALUES ('', '".$info."')";

$handel = mysql_query($query, $connect);
if(!$handel)
{
  die('Could NOT <b>enter</b> data: ' . mysql_error());
}
echo "data Entered successfully<br>";
		 }
		 }
}

 

Aldo, using more than one table field gives you the error 'Column count doesn't match value count at row 1' and it only gives one value if inserted into a array yet if echoed it loops all info into one string ???

 

As a beginner to php, the logic around this . . baffled me.

 

Does anyone know of a article on the subject matter(I couldn`t find any) as I feel, that I would like to learn more rather than just using others scripts.

 

Regards, Stefan.

[Stefan]

Fenway!

 

You never mentioned what you mean by

CSV directly as a engine type

? :-\

[fenway]

See here.