Excel -> MySQL ODBC data format problem

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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
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 ?


Re: Excel -> MySQL ODBC data format problem

Piotr wrote:
Quoted text here. Click to load it

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

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!


Re: Excel -> MySQL ODBC data format problem

Quoted text here. Click to load it

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:
"MyODBC ignores decimals of a float for client using comma as decimal

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/en/connection-parameters.html shows a
different meaning for the 256 option in MyODBC.  Very confusing!

Bill K.

Re: Excel -> MySQL ODBC data format problem

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.


Re: Excel -> MySQL ODBC data format problem

Piotr wrote:
Quoted text here. Click to load it

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


Re: Excel -> MySQL ODBC data format problem

Bill Karwin wrote:
Quoted text here. Click to load it
  I haven't found anything either except for this...

Which states:

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

Not very hopefull :-(


Re: Excel -> MySQL ODBC data format problem

Thank, but nothing helped it seems that excel is not idea for data
entry for mysql, only strings and data fields worked for me.

Site Timeline