从 MySQL 导入 Excel ODBC

发布于 2024-11-27 00:05:47 字数 1416 浏览 1 评论 0原文

我使用 MSQuery 从 ODBC 设置进行了 excel 导入，并且我想要的所有数据和行都在那里正常返回，但是当我将数据发送到我的 excel 工作簿时，我缺少 3 个字段日、月和日期。年谁能帮忙找出原因吗？

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

我上面的 MSQuery 代码

*编辑 **

我正在 Windows 7 上使用 Excel 2010 和 MySQL ODBC 3.5

更新**

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 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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吖咩 2024-12-04 00:05:47

您将 install_date 字段视为字符串。它可能是一个 Date 或 DateTime 字段，要获取单独的年、月和日值，您应该使用提取函数。

好的。正如我所怀疑的那样，你正在处理一个约会对象。将这些行：替换

    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',

为：

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',

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',

回复收藏 0 原文

~没有更多了~