Jump to content

How to import this excel spreadsheet into MYSQL


hfheuhf

Recommended Posts

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 by hfheuhf
Link to comment
Share on other sites

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";
}
Link to comment
Share on other sites

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";
}

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.