Excel -> MySQL ODBC data format problem

[Piotr]

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

[Jonathan]

Re: Excel -> MySQL ODBC data format problem

Piotr wrote:[color=blue]
> 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
>[/color]

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(YEA R(A1); "-"; MONTH(A1); "-"; DAY(A1))

supposing the date you entered is in the cell A1 (offcourse).

Good luck!

Jonathan

[Bill Karwin]

Re: Excel -> MySQL ODBC data format problem

"Jonathan" <jonathan@heela l.nl> wrote in message
news:43eb939d$0 $2016$ba620dc5@ text.nova.plane t.nl...[color=blue]
> Normally floating numbers are represented by 45.45 in MySQL and not as
> 45,45.[/color]

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.

[Piotr]

Re: Excel -&gt; 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.

regards
Peter

[Jonathan]

Re: Excel -&gt; MySQL ODBC data format problem

Piotr wrote:[color=blue]
> 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
>[/color]

(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

[Jonathan]

Re: Excel -&gt; MySQL ODBC data format problem

Bill Karwin wrote:[color=blue]
> "Jonathan" <jonathan@heela l.nl> wrote in message
> news:43eb939d$0 $2016$ba620dc5@ text.nova.plane t.nl...[color=green]
>> Normally floating numbers are represented by 45.45 in MySQL and not as
>> 45,45.[/color]
>
> 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.
>
>[/color]
I haven't found anything either except for this...

http://dev.mysql.com/doc/refman/5.0/en/number-syntax.html

Which states:

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

Not very hopefull :-(

Jonathan

[Piotr]

Re: Excel -&gt; 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.