Excel ODBC Import from MySQL

Question

I have an excel import from ODBC setup using MSQuery and all of the data and rows I want are being returned fine there however when I send the data to my excel workbook I am missing 3 fields day, month & year Can anyone help find out why?

SELECT  contract_0.create_date AS 'Submitted Date', 
        -- abbreviating
        contract_0.install_date AS 'installed_US', 
        RIGHT(contract_0.install_date,2) AS 'day', 
        MID(contract_0.install_date,6,2) AS 'month',
        LEFT(contract_0.install_date,4) As 'year'
            -- abbreviating
FROM `web54-reiga-gms`.contact contact_0, `web54-reiga-gms`.contract contract_0, `web54-reiga-gms`.user user_0
WHERE contract_0.contact_id = contact_0.id AND contract_0.user_id = user_0.id

My MSQuery Code above

*EDIT **

I am using Excel 2010 on Windows 7 and MySQL ODBC 3.5

UPDATE **

CREATE TABLE `contract` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `installer` varchar(50) DEFAULT NULL,
  `fitter` varchar(50) DEFAULT NULL,
  `guarantee_no` varchar(50) DEFAULT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `equipment` varchar(32) DEFAULT NULL,
  `certificate_no` varchar(50) DEFAULT NULL,
  `install_date` date DEFAULT '0000-00-00',
  `create_date` date DEFAULT '0000-00-00',
  `edit_date` date DEFAULT '0000-00-00',
  `edited_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

I simplified your query for troubleshooting. You could simplify it further by removing all but the contract_0 tables since they are not important in this question. — Paul Sasik, Jul 29 '11 at 13:39

Paul Sasik · Accepted Answer · 2011-07-29T13:42:52.447

0

You are treating the install_date field as a string. It's probably a Date or DateTime field and to get the individual year, month and day values you should use the extract function.

OK. It is as I suspected and you are dealing with a Date. Replace these lines:

    RIGHT(contract_0.install_date,2) AS 'day', 
    MID(contract_0.install_date,6,2) AS 'month',
    LEFT(contract_0.install_date,4) As 'year',

with these:

EXTRACT(YEAR FROM contract_0.install_date) AS 'day', 
EXTRACT(MONTH FROM contract_0.install_date) AS 'month',
EXTRACT(DAY FROM contract_0.install_date) As 'year',

edited Jul 29 '11 at 13:42

answered Jul 29 '11 at 13:23

Paul Sasik

79,492
20
149
189

can I email you my server details so you can take a look? – Justin Erswell Jul 29 '11 at 13:30
@Justin: Please don't do that. That's not how SO is supposed to work. Can you just generate the CREATE script for the table, change anything that would expose sensitive info and add it to your post? – Paul Sasik Jul 29 '11 at 13:36
Thanks tried this in MsQuery and i get - Could not add the table 'contract_0.install_date)'. – Justin Erswell Jul 29 '11 at 13:46
That error is not related to anything I/we have done. Please check to make sure you're connected to the proper database and the overall syntax of your query. Perhaps repost that query and error in another question. – Paul Sasik Jul 29 '11 at 13:50
@JustinErswell let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/1952/discussion-between-paul-sasik-and-justin-erswell) – Paul Sasik Jul 29 '11 at 13:50

Excel ODBC Import from MySQL

1 Answers1