filter/sort table
[dcaas.git] / data / import.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<?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";
}
 
?>