469,269 Members | 1,006 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,269 developers. It's quick & easy.

Excel -> MySQL ODBC data format problem

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

Feb 9 '06 #1
6 3734
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
Feb 9 '06 #2
"Jonathan" <jo******@heelal.nl> wrote in message
news:43**********************@text.nova.planet.nl. ..
Normally floating numbers are represented by 45.45 in MySQL and not as
45,45.


Normally this is true but I infer there is a way to configure the decimal
format, by specifying a locale. But I can't find a definitive documentation
page that shows how one can specify a locale.

Here's a related issue from the MySQL bug database:
http://bugs.mysql.com/bug.php?id=1388
"MyODBC ignores decimals of a float for client using comma as decimal
separator"

The bug log seems to state that using OPTION=256 in the MyODBC connection
string is related to locales, but the doc page
http://dev.mysql.com/doc/refman/5.0/...arameters.html shows a
different meaning for the 256 option in MyODBC. Very confusing!

Regards,
Bill K.
Feb 9 '06 #3
I will try and I will let you know,
1. Im sure about field definition as I have admin rights to mysql I
have tried Float na Double withiut success anyway.
2. Is any way to change decimals from , to . on the fly ? I dont want
to change regional settings on every computer.

regards
Peter

Feb 10 '06 #4
Piotr wrote:
I will try and I will let you know,
1. Im sure about field definition as I have admin rights to mysql I
have tried Float na Double withiut success anyway.
2. Is any way to change decimals from , to . on the fly ? I dont want
to change regional settings on every computer.

regards
Peter


(If you have a domain) is to enforce all the PC's in the domain to a
different locale setting using network domain policies.

Jonathan
Feb 10 '06 #5
Bill Karwin wrote:
"Jonathan" <jo******@heelal.nl> wrote in message
news:43**********************@text.nova.planet.nl. ..
Normally floating numbers are represented by 45.45 in MySQL and not as
45,45.


Normally this is true but I infer there is a way to configure the decimal
format, by specifying a locale. But I can't find a definitive documentation
page that shows how one can specify a locale.

Here's a related issue from the MySQL bug database:
http://bugs.mysql.com/bug.php?id=1388
"MyODBC ignores decimals of a float for client using comma as decimal
separator"

The bug log seems to state that using OPTION=256 in the MyODBC connection
string is related to locales, but the doc page
http://dev.mysql.com/doc/refman/5.0/...arameters.html shows a
different meaning for the 256 option in MyODBC. Very confusing!

Regards,
Bill K.

I haven't found anything either except for this...
http://dev.mysql.com/doc/refman/5.0/...er-syntax.html

Which states:

Integers are represented as a sequence of digits. Floats use . as a
decimal separator.

Not very hopefull :-(

Jonathan
Feb 10 '06 #6
Thank, but nothing helped it seems that excel is not idea for data
entry for mysql, only strings and data fields worked for me.

Feb 16 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Mike | last post: by
1 post views Thread by Steven | last post: by
2 posts views Thread by Powerguy | last post: by
7 posts views Thread by Alan Roberts | last post: by
5 posts views Thread by Mike in Santa Rosa | last post: by
3 posts views Thread by Ian Dunn | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.