Spreadsheet conversion

From Plings Info

Jump to: navigation, search

These are some scripts for converting spreadsheets supplied by local authorities so that they can be imported using CSV2Plings. Spreadsheet can also be scraped straight to plings xml - see Scrapers.

Contents

Doncater 

<?php
# Copyright (c) 2010 Ben Webb <bjwebb67@googlemail.com>
# Released as free software under the MIT license.
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE

/*
Array
(
    [0] => TIME
    [1] => DAY
    [2] => ACTIVITY / SESSION
    [3] => AGE
    [4] => VENUE
    [5] => VENUE ADDRESS AND POSTCODES
    [6] => COST
    [7] => CONTACT DETAILS
    [8] => Description
    [9] => Organisation
)
*/
 
$files = scandir(".");
$oheader = array();
$out = array();
foreach ($files as $file) {
    if (substr($file, -3 -1) == ".csv") {
        $in = fopen($file, "r");
        $header = fgetcsv($in);
        print_r($header);
        while ($row = fgetcsv($in)) {
            if (!array_key_exists($row[9], $out)) {
                $out[$row[9]] = array();
                $out[$row[9]]['ar'] = fopen("out/".$row[9]."_ar.csv", "w");
                fputcsv($out[$row[9]]['ar'], array());
                $out[$row[9]]['v'] = fopen("out/".$row[9]."_v.csv", "w");
                fputcsv($out[$row[9]]['v'], array());
            }
 
            $ar[0] = md5($row[2]);
            $ar[1] = $row[2];
            $ar[2] = $row[8];
            $ar[3] = ""; $ar[4] = "";
            $ar[5] = $row[1];
            $ar[6] = "Weekly";
            $t = explode("-",$row[0],2);
            $ar[7] = $t[0];
            $ar[8] = $t[1];
            $ar[9] = "";
            $ar[10] = $row[7];
            $ar[11] = ""; $ar[12] = "";
            $a = explode("-",$row[3],2);
            $ar[13] = $a[0];
            $ar[14] = $a[1];
            $ar[15] = $row[6];
            $ar[16] = ""; $ar[17] = ""; $ar[18] = "";
            $ar[19] = md5($row[4]);
 
            $v[0] = md5($row[4]);
            $v[1] = $row[4];
            preg_match("/^[0-9]+/", $row[5], $m);
            if ($m[0]) $v[2] = $m[0];
            else $v[2] = $row[4];
            preg_match("/([A-Z0-9]{3,4}[ ]*[A-Z0-9]{3})\.*$/", $row[5], $m);
            $v[3] = $m[1];
 
            fputcsv($out[$row[9]]['ar'], $ar);
            fputcsv($out[$row[9]]['v'], $v);
        }
    }
}
 
?>

Norfolk 

<pre>
<?php
# Copyright (c) 2010 Ben Webb <bjwebb67@googlemail.com>
# Released as free software under the MIT license.
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE

$template = Array (
        0 => "Activity ID",
        1 => "Name",
        2 => "Description",
        3 => "Start Date",
        4 => "End Date",
        5 => "Day",
        6 => "Frequency",
        7 => "Starts",
        8 => "Ends",
        9 => "ContactName",
        10 => "ContactNumber",
        11 => "ContactEmail",
        12 => "ContactAddress",
        13 => "MinAge",
        14 => "MaxAge",
        15 => "Cost",
        16 => "Categories",
        17 => "Keywords",
        18 => "ECMCodes",
        19 => "Venue ID",
        20 => "Organisation ID"
    );
$template_venues = Array (
        0 => "Venue ID",
        1 => "Name",
        2 => "BuildingNameNo",
        3 => "Postcode",
        4 => "ContactForename",
        5 => "ContactSurname",
        6 => "ContactTelephone",
        7 => "Contactemail",
        8 => "ContactFax",
        9 => "description",
        10 => "website",
        11 => "parking",
        12 => "CyclePark",
        13 => "DisabledFacilitiesNotes"
    );
 
$in = fopen("raw.csv", "r");
$header = fgetcsv($in);
print_r($header);
 
$out_ar = fopen("norfolk_ar.csv", "w");
fputcsv($out_ar, $template);
 
$out_v = fopen("norfolk_v.csv", "w");
fputcsv($out_v, $template_venues);
 
$venues = array();
$v = 0;
 
function bnn($venue, $venueaddr) {
    $vas = explode(" ", $venueaddr);
    if (is_numeric($vas[0])) return $vas[0];
    else return $venue;
}
 
function add_venue($row) {
    global $venues, $v, $out_v;
    if ($vid = $venues[$row[9]]) return $vid;
    else {
        $v++;
        $n = explode(" ", $row[22], 2);
        fputcsv($out_v, array (
                0 => $v,
                1 => $row[9],
                2 => bnn($row[9], $row[10]),
                3 => $row[11],
                4 => $n[0],
                5 => $n[1],
                6 => $row[23],
                7 => $row[24]
            ));
        $venues[$row[9]] = $v;
        return $v;
    }
}
 
while ($row = fgetcsv($in)) {
    $nrow = array (
            0 => $row[3],
            1 => $row[4],
            2 => $row[8].($row[19]?"\n\nAimed at: ".$row[19]:''),
            3 => $row[15],
            4 => $row[16],
            5 => $row[17],
            6 => $row[18],
            7 => $row[20],
            8 => $row[21],
            9 => $row[22],
            10 => $row[23],
            11 => $row[24],
            12 => $row[25],
            13 => $row[36],
            14 => $row[37],
            15 => $row[46],
            16 => $row[47],
            17 => $row[35],
            18 => ""/*$row[6] ECM codes in words not numbers*/,
            19 => add_venue($row)
        );
    fputcsv($out_ar, $nrow);
}
 
?>
</pre>

Salford 

<?php
# Copyright (c) 2010 Ben Webb <bjwebb67@googlemail.com>
# Released as free software under the MIT license.
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE

function getNameNo($venue, $add1) {
    if (preg_match("/^[0-9]+/", $add1 , $m)) {
        return $m[0];
    }
    else {
        return $venue;
    }
}
 
$template = Array (
        0 => "Activity ID",
        1 => "Name",
        2 => "Description",
        3 => "Start Date",
        4 => "End Date",
        5 => "Day",
        6 => "Frequency",
        7 => "Starts",
        8 => "Ends",
        9 => "ContactName",
        10 => "ContactNumber",
        11 => "ContactEmail",
        12 => "ContactAddress",
        13 => "MinAge",
        14 => "MaxAge",
        15 => "Cost",
        16 => "Categories",
        17 => "Keywords",
        18 => "ECMCodes",
        19 => "Venue ID",
        20 => "Organisation ID"
    );
$template_venues = Array (
        0 => "Venue ID",
        1 => "Name",
        2 => "BuildingNameNo",
        3 => "Postcode",
        4 => "ContactForename",
        5 => "ContactSurname",
        6 => "ContactTelephone",
        7 => "Contactemail",
        8 => "ContactFax",
        9 => "description",
        10 => "website",
        11 => "parking",
        12 => "CyclePark",
        13 => "DisabledFacilitiesNotes"
    );
 
$in = fopen("salford_raw.csv", "r");
$header = fgetcsv($in);
 
$out_ar = fopen("salford_ar.csv", "w");
fputcsv($out_ar, $template);
 
$out_v = fopen("salford_v.csv", "w");
fputcsv($out_v, $template_venues);
 
$out_o = fopen("salford_o.csv", "w");
fputcsv($out_o, array("ID", "Name"));
 
while ($row = fgetcsv($in)) {
    if (preg_match_all("/(.*?)([0-9]{1,2}:[0-9]{2}[ap]m[ ]*-[ ]*[0-9]{1,2}:[0-9]{2}[ap]m)/", $row[19], $m)) {
        #print_r($m);
        for ($i = 0; $i < sizeof($m[0]); $i++) {
            $day = str_ireplace(array("days", "and", "to"), array("day", ",", "-"), trim($m[1][$i], " -,:;"))."\n";
            $times = explode("-",$m[2][$i]);
            fputcsv($out_ar, array(
                0 => $row[0],
                1 => $row[1],
                2 => $row[3],
                3 => "",
                4 => "",
                5 => $day,
                6 => "Weekly",
                7 => $times[0],
                8 => $times[1],
                9 => "",
                10 => $row[16],
                11 => $row[18],
                12 => "",
                13 => $row[4],
                14 => $row[6],
                15 => $row[8],
                16 => "",
                17 => $row[24],
                18 => "",
                19 => $row[11],
                20 => $row[10]
            ));
            fputcsv($out_v, array(
                0 => $row[11],
                1 => $row[11],
                2 => getNameNo($row[11], $row[12]),
                3 => $row[15],
                4 => "",
                5 => "",
                6 => "",
                7 => "",
                8 => "",
                9 => "",
                10 => "",
                11 => $row[20],
                12 => "",
                13 => $row[22]
            ));
            fputcsv($out_o, array($row[10], $row[10]));
        }
    }
}
Retrieved from "http://www.plings.info/wiki/index.php/Spreadsheet_conversion"
Personal tools