<?php |
<?php |
|
|
define('__ROOT__', dirname(dirname(__FILE__))); |
define('__ROOT__', dirname(dirname(__FILE__))); |
include(__ROOT__ . "/config.php"); |
include(__ROOT__ . "/config.php"); |
/************************************** |
/************************************** |
* Drop tables * |
* Drop tables * |
**************************************/ |
**************************************/ |
|
|
// Drop table messages from file db |
// Drop table messages from file db |
$db->exec("DROP TABLE IF EXISTS dcaas_services"); |
$db->exec("DROP TABLE IF EXISTS dcaas_services"); |
|
|
$row = 0; |
$row = 0; |
$headers = Array(); |
$headers = Array(); |
$csvfile = '../data/DCaaS+Catalogue+September+2013 PublicDCaaS_AFI_Part_3_Sec_5_Service_.csv'; |
$path = "../data/"; |
|
foreach (glob($path."*.csv") as $filename) { |
|
$csvfile = $path.$filename; |
|
} |
if (($handle = fopen($csvfile, "r")) !== FALSE) { |
if (($handle = fopen($csvfile, "r")) !== FALSE) { |
while (($data = fgetcsv($handle, 4096, ",")) !== FALSE) { |
while (($data = fgetcsv($handle, 7000, ",")) !== FALSE) { |
if ($row == 0) { |
if ($row == 0) { |
/************************************** |
/************************************** |
* Create tables * |
* Create tables * |
**************************************/ |
**************************************/ |
// Create table messages |
// Create table messages |
$create = "CREATE TABLE IF NOT EXISTS dcaas_services ("; |
$create = "CREATE TABLE IF NOT EXISTS dcaas_services ("; |
// id INTEGER PRIMARY KEY,"; |
// id INTEGER PRIMARY KEY,"; |
foreach ($data as $column) { |
foreach ($data as $column) { |
$column = str_replace(" ", "_", strtolower($column)); |
$column = str_replace(" ", "_", strtolower($column)); |
$create .= $column . " text " . (($column == 'acnabn' || $column == 'service_name') ? " not null " : "") . ","; |
$create .= $column . " text " . (($column == 'acnabn' || $column == 'service_name') ? " not null " : "") . ","; |
$headers[] = $column; |
$headers[] = $column; |
} |
} |
//$create .= " PRIMARY KEY ( acnabn,service_name) )"; |
//$create .= " PRIMARY KEY ( acnabn,service_name) )"; |
$create .= "id INTEGER PRIMARY KEY )"; |
$create .= "id INTEGER PRIMARY KEY )"; |
echo $create; |
echo $create; |
$db->exec($create); |
$db->exec($create); |
// Prepare INSERT statement to SQLite3 file db |
// Prepare INSERT statement to SQLite3 file db |
/* Create a string for the parameter placeholders filled to the number of params */ |
/* Create a string for the parameter placeholders filled to the number of params */ |
$placeholders = implode(',', array_fill(0, count($headers), '?')); |
$placeholders = implode(',', array_fill(0, count($headers), '?')); |
$insert = "INSERT INTO dcaas_services (" . implode($headers, ", ") . ") |
$insert = "INSERT INTO dcaas_services (" . implode($headers, ", ") . ") |
VALUES (" . $placeholders . ")"; |
VALUES (" . $placeholders . ")"; |
echo $insert; |
echo $insert; |
$stmt = $db->prepare($insert); |
$stmt = $db->prepare($insert); |
$db->beginTransaction(); |
$db->beginTransaction(); |
} else { |
} else { |
if (count($data) < count($headers)) { |
if (count($data) < count($headers)) { |
$values = $data + array_fill(count($data), count($headers) - count($data), NULL); |
$values = $data + array_fill(count($data), count($headers) - count($data), NULL); |
} else { |
} else { |
$values = $data; |
$values = $data; |
} |
} |
print_r($values); |
print_r($values); |
$stmt->execute($data); |
$stmt->execute($data); |
} |
} |
$row++; |
$row++; |
} |
} |
// end transaction |
// end transaction |
$db->commit(); |
$db->commit(); |
|
|
fclose($handle); |
fclose($handle); |
echo "$row rows imported"; |
echo "<br/>\n $row rows imported <br/>\n"; |
} |
} |
|
|
?> |
?> |
|
|