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