Piotr wrote:
Hi,
I have following problem:
I use a form in excel to send data into mysql server, everything is ok
unless I have to deal with decimals or data fields, this simple are not
recognized.
For example
In excel in mySQL
45,45 -> 45
2005-01-01 -> 0000-00-00
what is the problem ? How can I solve this problem ?
regards
The first problem is probably caused by the fact that you defined the
field as an integer in the table definition. You can try to find out (if
the user you are accessing the database with has the right privileges)
what the definition of the table is:
1. Log in with a mysql client to the server
2. Select your database
3. Issue the
SHOW CREATE TABLE tablename;
command to see what the field definitions are.
If your definition is correct (not integer, but float or double) another
cause of this problem might be that you use to represent the number in
another locale setting than MySQL desires. Normally floating numbers are
represented by 45.45 in MySQL and not as 45,45.
The date problem is probably a problem with the way Excel works to
represent the date. the value of a date is a integer value for Excel and
it can be formatted to read as yyyy-mm-dd, however the value of the cell
still is an integer value. You can actually see this if you specify the
cell and set its format back to general formatting instead of the date
format.
You might be able to solve this if you convert the date to a string
value using Excel worksheet functions CONCATENATE(), YEAR(), MONTH() and
DAY() like so:
=CONCATEATE(YEAR(A1); "-"; MONTH(A1); "-"; DAY(A1))
supposing the date you entered is in the cell A1 (offcourse).
Good luck!
Jonathan