How to import this excel spreadsheet into MYSQL

[hfheuhf]

Could someone help me out on how I would go about importing an excel sheet that is set up like this

                13/02/2015                14/02/2015userid    starttime    endtime     starttime    endtime1           12:00          16:00           15:00       20:002                                          15:00       20:003           16:00         20:00

There are a lot of scripts out there on importing excel files however, there arent many where you could for instance merge two fields i.e i need the date and starttime to be one field so it could be inserted into the table into a field called "starttime" which is a DATETIME. I can't seem to find anything online. I've come accross PHPExcel, however, I can't get the grisp of it/understand it.

Edited February 13, 2015 by hfheuhf

[mac_gyver]

your excel layout is rather unique, so you are going to need to write some unique and specific code to import the data.

 

exporting your excel spreadsheet as a csv file and using php's csv file functions would result in the simplest code.

 

you would need to read and store the first row of dates from the file, then loop over the actual rows of data and loop over each pair of start/end times in a row and access the corresponding date from the previously read first row to produce the datetime values.

 

edit: the following should get you started, however, the fun in programming is when you devise, write, and test your own code that produces the result you want -   

$file = 'a.csv'; // the name of the csv file

$row = 1; // used to identify the rows with the dates and the actual data
if (($handle = fopen($file, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
        if($row == 1){ // get the first row of dates
            $dates = $data;
        }
        if($row >= 3){ // the actual data
            // data[1/2], [3/4], [5/6], ... corresponds to dates[1], [3], [5], ...
            $id = $data[0];
            $num = count($data);
            for($x = 1; $x <$num; $x=$x+2){
                if($data[$x] != ''){ // there's a time entered
                    $date = new DateTime(str_replace('/','-',$dates[$x])); // UK date format
                    $dt = $date->format('Y-m-d'); // convert date to YYYY-MM-DD
                    $start = $dt . ' ' . $data[$x] . ':00'; // YYYY-MM-DD HH:MM:SS format
                    $end = $dt . ' ' . $data[$x+1] . ':00';
                    echo "ID: $id Start: $start End: $end<br>"; // display the result
                }
            }
        }
        $row++;
    }
    fclose($handle);
} else {
    echo "could not open file: $file";
}

[hfheuhf]

Hello, thanks so much for replying! Didn't know how to approach this been trying to find a solution for weeks! I will have a go at your code later today and let you know how it goes

 

your excel layout is rather unique, so you are going to need to write some unique and specific code to import the data.

 

exporting your excel spreadsheet as a csv file and using php's csv file functions would result in the simplest code.

 

you would need to read and store the first row of dates from the file, then loop over the actual rows of data and loop over each pair of start/end times in a row and access the corresponding date from the previously read first row to produce the datetime values.

 

edit: the following should get you started, however, the fun in programming is when you devise, write, and test your own code that produces the result you want -   

$file = 'a.csv'; // the name of the csv file

$row = 1; // used to identify the rows with the dates and the actual data
if (($handle = fopen($file, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
        if($row == 1){ // get the first row of dates
            $dates = $data;
        }
        if($row >= 3){ // the actual data
            // data[1/2], [3/4], [5/6], ... corresponds to dates[1], [3], [5], ...
            $id = $data[0];
            $num = count($data);
            for($x = 1; $x <$num; $x=$x+2){
                if($data[$x] != ''){ // there's a time entered
                    $date = new DateTime(str_replace('/','-',$dates[$x])); // UK date format
                    $dt = $date->format('Y-m-d'); // convert date to YYYY-MM-DD
                    $start = $dt . ' ' . $data[$x] . ':00'; // YYYY-MM-DD HH:MM:SS format
                    $end = $dt . ' ' . $data[$x+1] . ':00';
                    echo "ID: $id Start: $start End: $end<br>"; // display the result
                }
            }
        }
        $row++;
    }
    fclose($handle);
} else {
    echo "could not open file: $file";
}