用php把access資料庫匯入到mysql

標籤：blog   os   io   檔案   資料   for   cti   ar   

<?phpheader("content-Type: text/html; charset=utf-8");//////把access資料庫轉換成mysql的SQL語句///請在命令列運行/////串連access資料庫$db = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./test.mdb"), "", "");//產生sql檔案$file = "test.sql";save_file($file, "SET NAMES utf8;\n\n");$tables = get_tables($db);for($i=0; $i<count($tables); $i++){$table = $tables[$i];echo("$i\t$table\n");$res = odbc_exec($db, $sql = "SELECT * FROM `$table`");$fields = get_table_fields($res);$structure_sql = get_table_structure($table, $fields)."\n\n";$data_sql = iconv("GBK", "UTF-8", get_table_data($res, $table)."\n\n\n");save_file($file, $structure_sql);save_file($file, $data_sql);}echo("ok");/////////////////////////////////函數////////////////////////////////////////儲存檔案function save_file($file, $data){$fp = fopen($file, ‘a+‘);fwrite($fp, $data);fclose($fp);}//擷取資料表function get_tables($db){$res = odbc_tables($db);$tables = array();while (odbc_fetch_row($res)){if(odbc_result($res, "TABLE_TYPE")=="TABLE")$tables[] = odbc_result($res, "TABLE_NAME");}return $tables;}//擷取表欄位function get_table_fields($res){$fields = array();$num_fields = odbc_num_fields($res);for($i=1; $i<=$num_fields; $i++){$item = array();$item[‘name‘] = odbc_field_name($res, $i);$item[‘len‘] = odbc_field_len($res, $i);$item[‘type‘] = odbc_field_type($res, $i);$fields[] = $item;}return $fields;}//產生建表SQLfunction get_table_structure($table, $fields){$primary_key = ‘‘;$sql = array();foreach($fields as $item){if($item[‘type‘]==‘COUNTER‘){$sql[] = "\t`".$item[‘name‘]."` int(".$item[‘len‘].") NOT NULL AUTO_INCREMENT";$primary_key = $item[‘name‘];}else if($item[‘type‘]==‘VARCHAR‘){$sql[] = "\t`".$item[‘name‘]."` varchar(".$item[‘len‘].") NOT NULL DEFAULT ‘‘";}else if($item[‘type‘]==‘LONGCHAR‘){$sql[] = "\t`".$item[‘name‘]."` text NOT NULL";}else if($item[‘type‘]==‘INTEGER‘){$sql[] = "\t`".$item[‘name‘]."` int(".$item[‘len‘].") NOT NULL DEFAULT ‘0‘";}else if($item[‘type‘]==‘SMALLINT‘){$sql[] = "\t`".$item[‘name‘]."` smallint(".$item[‘len‘].") NOT NULL DEFAULT ‘0‘";}else if($item[‘type‘]==‘REAL‘){$sql[] = "\t`".$item[‘name‘]."` tinyint(1) NOT NULL DEFAULT ‘0‘";}else if($item[‘type‘]==‘DATETIME‘){$sql[] = "\t`".$item[‘name‘]."` datetime NOT NULL";}else if($item[‘type‘]==‘CURRENCY‘){$sql[] = "\t`".$item[‘name‘]."` float NOT NULL DEFAULT ‘0‘";}else{$sql[] = "\t`".$item[‘name‘]."` varchar(255) NOT NULL DEFAULT ‘‘";}}return "CREATE TABLE IF NOT EXISTS `$table` (\n".implode(",\n", $sql).($primary_key?",\n\tPRIMARY KEY (`".$primary_key."`)\n":"\n").") ENGINE=MyISAM DEFAULT CHARSET=utf8 ;";}//擷取表資料function get_table_data($res, $table){$row_sql = array();while( $row = odbc_fetch_array($res) ) {$row_sql[] = get_row_sql($table, $row);}return implode("\n", $row_sql);}//產生插入SQLfunction get_row_sql($table, $row){$keys = array_keys($row);$values = array_values($row);for($i=0;$i<count($values);$i++){$values[$i] = addslashes($values[$i]);}return "INSERT INTO ".$table."(`".implode("`,`", $keys)."`) VALUES(‘".implode("‘,‘",$values)."‘);";}?>