O'Reilly logo

PHP Hacks by Jack D. Herrington

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #46. Load Your Database from Excel

Use Excel 2003's XML capability to load your SQL database from an Excel spreadsheet.

More than a few times, I have had an Excel spreadsheet full of data that I needed to load into my database. Before Office 2003, I had to export each sheet as a CSV, and then use a custom loader to insert the records into the database. With Excel 2003's ability to save spreadsheets, macros, and even formatting as XML, that custom loader can go the way of eight-track tapes. The script in this hack turns Excel XML data into SQL that you can feed to your database. Figure 5-17 shows how the Excel-generated XML, taken as input to the gen.php script, is converted to SQL, which is then fed into the database.

The flow between the Excel XML and the database

Figure 5-17. The flow between the Excel XML and the database

The Code

Save the code in Example 5-36 as gen.php.

Example 5-36. The code to generate SQL from an Excel XML file

<?php $tables = array(); $indata = 0; function encode( $text ) { $text = preg_replace( "/'/", "''", $text ); return "'".$text."'"; } function start_element( $parser, $name, $attribs ) { global $tables, $indata; if ( $name == "WORKSHEET" ) { $tables []= array( 'name' => $attribs['SS:NAME'], 'data' => array() ); } if ( $name == "ROW" ) { $tables[count($tables)-1]['data'] []= array(); } if ( $name == "DATA" ) { $indata = 1; } } function text( $parser, $text ) { global $tables, $indata; if ( $indata ) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required