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

Feb 9 '06 #1

Subscribe Reply

3972

Jonathan

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

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

Good luck!

Jonathan

Feb 9 '06 #2

Bill Karwin

"Jonathan" <jo******@heela l.nl> wrote in message
news:43******** **************@ text.nova.plane t.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

Piotr

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

Jonathan

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

Jonathan

Bill Karwin wrote:

"Jonathan" <jo******@heela l.nl> wrote in message
news:43******** **************@ text.nova.plane t.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

Piotr

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

Similar topics

2497

not quitting excel

by: Mike | last post by:

Please help. I am using object.quit in an attempt to quit an excel application that I started with createobject ("Excel.Application"). The code executes, but does not stop the application. When I run the exact same code for Access and Word, it works fine (app closes). Any ideas? Any help would be appreciated. Thanks, Mike Sweet

.NET Framework

20275

GetObject and Excel - working with VB 5

by: Otie | last post by:

I found the following under the GetObject help notes and in the example for GetObject: "This example uses the GetObject function to get a reference to a specific Microsoft Excel worksheet (MyXL). It uses the worksheet's Application property to make Microsoft Excel visible, to close it, and so on. Using two API calls, the DetectExcel Sub procedure looks for Microsoft Excel, and if it is running, enters it in the Running Object Table. The...

Visual Basic 4 / 5 / 6

2166

vb .net and Excel 2000

by: Steven | last post by:

Hello, I use the following code to make an Excel-file, which works fine with Excel 2003 (and the MS Excel 11.0 object library): Dim Excel As New Excel.Application Dim oBook As Excel.Workbook Dim WSheet As New Excel.Worksheet WSheet = Excel.Workbooks.Add.Worksheets.Add

Visual Basic .NET

21922

Excel Process ID

by: Powerguy | last post by:

Hi all, I am looking for a way to get the Process id (or a handle) of an EXCEL process created from within my code. For example when the following code is executed: Dim EXL As Excel.Application = New Excel.Application a new instance of EXCEL.EXE is created in the task manager.

Visual Basic .NET

2810

Excel in VB.NET - looks the same as the old days?

by: Anthony | last post by:

To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't really changed since the days of VB6. That is, I'd do something similar to this Code: Dim ExcelApp As Excel.Application Dim ExcelWB As Excel.Workbook

Visual Basic .NET

2671

CSharp VB Excel COM Differences

by: Alan Roberts | last post by:

Can someone please explain the following for me... I am trying to link to a .NET DLL from Excel. Excel needs to pass a reference to itself to the DLL and then the DLL needs to perform some work on the running instance of Excel via that reference. As an example, a VB DLL to return the path to the current active workbook contains the following (in addition to the COM GUIDS automatically added to a new COMClass)... Public Class Class1...

C# / C Sharp

3147

using Excel 2003 from C# 2003

by: Mike in Santa Rosa | last post by:

I'm trying to get a simple c# app built that can launch/manipulate an excel workbook, sheet. I've chased down several examples and can't any of them to work. So I must be doing somethnig obviouslt wrong. Most examples to get things started include something: using Microsoft.Office.Interop.Excel; or using Excel; or using Excel = Microsoft.Office.Interop.Excel;

C# / C Sharp

3020

Accessing existing Excel instance

by: Ian Dunn | last post by:

I'm simply trying to access an instance of Excel that has been opened manually by the user in order to put a few values in the existing sheet. Here's the code I've tried: Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet oXL = GetObject(, "Excel.Application") oWB = oXL.ActiveWorkbook oSheet = oWB.ActiveSheet

Visual Basic .NET

2685

Excel Instance won;t die if coping cell content to struct

by: alexia.bee | last post by:

Hi all, In some weird reason, excel instance won;t die if i remove the comment from 4 lines of setting values into struct. here is a snipcode public System.Collections.Generic.List<frmMain.sDBTest> LoadTestSet(string TestSetFile, System.Collections.Generic.List<frmMain.sDBTestDBviewList)

C# / C Sharp

4086

C# closing Excel the right way

by: fakehitswizard | last post by:

this is the correct way to close excel with C#. I've seen alot of other bogus posts ALL over the web that don't work, how frustrating. string savepath; bool foundPID; int ourPID = 0; int tmpX = 0; int indexRow = 1; int indexCol = 1; int existingPIDs;

.NET Framework

8031

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8443

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8315

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6792

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5467

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

3945

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2452

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1565

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1309

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General