Page 1 of 1

need some help guys

Posted: Mon Feb 06, 2012 7:58 pm
by jauson
Hi Guys!

I want to import datetime data from excel sheet going to mysql.
everything works fine until datetime from mysql didnt read the data from my excel file.

it shows from mysql.

0000-00-00 00-00-00

any i deas?

Re: need some help guys

Posted: Tue Feb 07, 2012 6:27 pm
by Eric!
I assume you're using PHP to import from excel. Can you post some code with some more data like, what are you reading from the file and how are you storing it in to mysql. Also is that field a DATETIME field? And what does PHP think the data is (echo it out) before it writes it to the database?

Re: need some help guys

Posted: Wed Feb 08, 2012 12:54 am
by jauson
below is my code to read excel files. xls i have just edit the columns to parse exactly on my data columns in excel.

<?php

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('Book1.xls');

$conn = mysql_connect("localhost","root","root");
mysql_select_db("database",$conn);

for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
$id = $data->sheets[0]["cells"][$x][1];
$empID = $data->sheets[0]["cells"][$x][2];
$timein = $data->sheets[0]["cells"][$x][3];
$timeout = $data->sheets[0]["cells"][$x][4];
$sql = mysql_query("INSERT INTO mytable (id, empID, timein, timeout) VALUES ('id', '$empID', '$timein', '$timeout')");
//echo $sql ."\n";
if($sql){
echo 'Data successully stored in databbase. '.$empID.'<br>';
} else {
echo 'Data <font color="RED">'.$empID.'</font> not stored in database. <br>';
}
}
?>

Assume that below is my data on Book1.xls

id empID timein timeout
1 02062012A 2012-01-01 2012/01/11 17:29:49
2 02062012A 2012/01/13 07:56:56 2012/01/13 17:17:59
3 02062012A 2012/01/14 09:00:49 2012/01/14 16:14:11
4 02062012A 2012/01/16 08:19:48 2012/01/16 17:13:51
5 02062012A 2012/01/17 07:54:14 2012/01/17 17:19:45
6 02062012A 2012/01/18 08:13:16 2012/01/20 17:14:39
7 02062012A 2012/01/21 09:32:58 2012/01/21 16:07:42
8 02062012A 2012/01/23 08:26:30 2012/01/23 17:04:33
9 02062012A 2012/01/23 17:04:40 2012/01/23 17:09:52
10 02062012A 2012-01-24 08:23:02 2012/01/24 17:17:59
11 02062012A 2012/01/24 1/24/2012

now everything is fine except timein and timeout.
I heard that excel didnot support mysql datetime. really need help

mysql shows below:


id empID timein timeout
1 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
2 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
3 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
4 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
5 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
6 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
7 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
8 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
9 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
10 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
11 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00

thanks

Re: need some help guys

Posted: Wed Feb 08, 2012 9:21 am
by Eric!
Ok, and what does echo $timein or echo $timeout show?

And you'll need to convert those strings into the proper DATETIME format YYYY-MM-DD HH:MM:SS before mysql can store them properly:

Code: Select all

$timein=preg_replace('/\//', '-', $timein);//change / to - in date strings
(I think I have the regex right for finding / with /\//)

(PS please use the code tags when posting code!)

Re: need some help guys

Posted: Wed Feb 08, 2012 6:55 pm
by jauson
I will explain further, below is my code in reading excel files. see Annex A

Annex A:
<?php

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('Book1.xls');

$conn = mysql_connect("localhost","root","root");
mysql_select_db("database",$conn);

for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
$id = $data->sheets[0]["cells"][$x][1];
$empID = $data->sheets[0]["cells"][$x][2];
$timein = $data->sheets[0]["cells"][$x][3];
$timeout = $data->sheets[0]["cells"][$x][4];
$sql = mysql_query("INSERT INTO mytable (id, empID, timein, timeout) VALUES ('id', '$empID', '$timein', '$timeout')");
//echo $sql ."\n";
if($sql){
echo 'Data successully stored in databbase. '.$empID.'<br>';
} else {
echo 'Data <font color="RED">'.$empID.'</font> not stored in database. <br>';
}
}
?> Annex A is work perfectly, now assumed that below is my data on my excel files. Book1.xls. Annex B:

Annex B:
id empID timein timeout
1 02062012A 2012/01/13 07:56:56 2012/01/11 17:29:49
2 02062012A 2012/01/13 07:56:56 2012/01/13 17:17:59
3 02062012A 2012/01/14 09:00:49 2012/01/14 16:14:11
4 02062012A 2012/01/16 08:19:48 2012/01/16 17:13:51
5 02062012A 2012/01/17 07:54:14 2012/01/17 17:19:45
6 02062012A 2012/01/18 08:13:16 2012/01/20 17:14:39
7 02062012A 2012/01/21 09:32:58 2012/01/21 16:07:42
8 02062012A 2012/01/23 08:26:30 2012/01/23 17:04:33
9 02062012A 2012/01/23 17:04:40 2012/01/23 17:09:52
10 02062012A 2012-01-24 08:23:02 2012/01/24 17:17:59
11 02062012A 2012-01-24 08:23:02 2012/01/24 17:17:59

Now, when i try to execute the codes. it echoes see Annex C.

Annex C
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A

Now it shows that everything is well and stored in database. But assumed that below is the data stored in phpmyadmin, it show see Annex D:

Annex D:
id empID timein timeout
1 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
2 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
3 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
4 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
5 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
6 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
7 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
8 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
9 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
10 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
11 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00

What else can i do? to read timein and timeout on my excel going to phpmyadmin.
what would be the code to read exactly the timein and timeout on my excel going to database. thank you.

Re: need some help guys

Posted: Fri Feb 10, 2012 7:42 am
by Eric!
echo $timein and echo $timeout to see if you're really reading what you think you are from the excel file. And I don't see where you have changed the date format as I described in my earlier post.

Also use the

Code: Select all

 tags when posting code and [text] tags when posting output here on this message board.

Re: need some help guys

Posted: Tue Feb 14, 2012 6:26 pm
by jauson
Helo,

I have done what you advised here, I echoed $timein and $timeou theres no other error, except the datetime in excel doesnt read on mysql,

I want to convert datetime of excel to support the datetime of mysql.

Regards,

Re: need some help guys

Posted: Thu Feb 16, 2012 8:46 am
by Eric!
and have you changed the format for your two variables like I suggested earlier:

Code: Select all

$timein=preg_replace('/\//', '-', $timein);//change / to - in date strings

Re: need some help guys

Posted: Thu Feb 16, 2012 6:21 pm
by jauson
below is my code. where do i need to input the regex?

<?php

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('Book1.xls');

$conn = mysql_connect("localhost","root","root");
mysql_select_db("database",$conn);

for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
$id = $data->sheets[0]["cells"][$x][1];
$empID = $data->sheets[0]["cells"][$x][2];
$timein = $data->sheets[0]["cells"][$x][3];
$timeout = $data->sheets[0]["cells"][$x][4];
$sql = mysql_query("INSERT INTO mytable (id, empID, timein, timeout) VALUES ('id', '$empID', '$timein', '$timeout')");
//echo $sql ."\n";
if($timein){
echo 'Data successully stored in databbase. '.$timein.'<br>';
} else {
echo 'Data <font color="RED">'.$empID.'</font> not stored in database. <br>';
}
}
?>

Re: need some help guys

Posted: Sat Feb 18, 2012 7:41 pm
by Eric!
Before you use it in your msql_query. And you have to do it for both $timin and $timeout.

Also please use code tags when posting [ syntax=php ]PASTE CODE HERE[/ syntax] (without the spaces)

Re: need some help guys

Posted: Mon Feb 20, 2012 12:02 am
by jauson
nothings happen still. i tried to input the regex syntax but the problem occurs.