将数据加载到 mysql 在 linux 通过 windows 客户端上承载

发布时间: 2016/10/11 18:56:04
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我运行最新版本的工作台。

我在我的数据库中创建表︰

use medicare_dataset;
CREATE TABLE `medicarepuf` (
  `npi` int(10) NOT NULL,
  `nppes_provider_last_org_name` varchar(70) DEFAULT NULL,
  `nppes_provider_first_name` varchar(20) DEFAULT NULL,
  `nppes_provider_mi` varchar(1) DEFAULT NULL,
  `nppes_credentials` varchar(20) DEFAULT NULL,
  `nppes_provider_gender` varchar(1) DEFAULT NULL,
  `nppes_entity_code` varchar(1) DEFAULT NULL,
  `nppes_provider_street1` varchar(55) DEFAULT NULL,
  `nppes_provider_street2` varchar(55) DEFAULT NULL,
  `nppes_provider_city` varchar(40) DEFAULT NULL,
  `nppes_provider_zip` int(20) DEFAULT NULL,
  `nppes_provider_state` varchar(2) DEFAULT NULL,
  `nppes_provider_country` varchar(2) DEFAULT NULL,
  `provider_type` varchar(43) DEFAULT NULL,
  `medicare_participation_indicator` varchar(1) DEFAULT NULL,
  `place_of_service` varchar(1) DEFAULT NULL,
  `hcpcs_code` varchar(5) DEFAULT NULL,
  `hcpcs_description` varchar(30) DEFAULT NULL,
  `line_srvc_cnt` varchar(10) DEFAULT NULL,
  `bene_unique_cnt` varchar(10) DEFAULT NULL,
  `bene_day_srvc_cnt` varchar(10) DEFAULT NULL,
  `average_Medicare_allowed_amt` varchar(20) DEFAULT NULL,
  `stdev_Medicare_allowed_amt` varchar(20) DEFAULT NULL,
  `average_submitted_chrg_amt` varchar(20) DEFAULT NULL,
  `stdev_submitted_chrg_amt` varchar(20) DEFAULT NULL,
  `average_Medicare_payment_amt` varchar(20) DEFAULT NULL,
  `stdev_Medicare_payment_amt` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(侧一点,但不知道为什么 mysql 需要 '相反字符单引号' ' 定义的列的名称时)

我使用下面的 mysql shell 脚本尝试 70 mb 的文件装载到表︰

LOAD DATA INFILE 'C:/Users/agordon/Downloads/Medicare_Physician_and_Other_Supplier_PUF_aa_CY2012.csv'
INTO TABLE medicarepuf
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

这里是发生了什么︰

enter image description here

这里是一个视频剪辑的发生了什么︰ http://screencast.com/t/ReFfyQxtAJO

这里是我想在那里得到的原始数据的几行︰

+------------+------------------------------+---------------------------+-------------------+-------------------+-----------------------+-------------------+------------------------+------------------------+---------------------+--------------------+----------------------+------------------------+---------------+----------------------------------+------------------+------------+------------------------------+-----------------+-------------------+---------------------+------------------------------+----------------------------+----------------------------+--------------------------+------------------------------+----------------------------+
|    npi     | nppes_provider_last_org_name | nppes_provider_first_name | nppes_provider_mi | nppes_credentials | nppes_provider_gender | nppes_entity_code | nppes_provider_street1 | nppes_provider_street2 | nppes_provider_city | nppes_provider_zip | nppes_provider_state | nppes_provider_country | provider_type | medicare_participation_indicator | place_of_service | hcpcs_code |      hcpcs_description       |  line_srvc_cnt  |  bene_unique_cnt  |  bene_day_srvc_cnt  | average_Medicare_allowed_amt | stdev_Medicare_allowed_amt | average_submitted_chrg_amt | stdev_submitted_chrg_amt | average_Medicare_payment_amt | stdev_Medicare_payment_amt |
+------------+------------------------------+---------------------------+-------------------+-------------------+-----------------------+-------------------+------------------------+------------------------+---------------------+--------------------+----------------------+------------------------+---------------+----------------------------------+------------------+------------+------------------------------+-----------------+-------------------+---------------------+------------------------------+----------------------------+----------------------------+--------------------------+------------------------------+----------------------------+
| 1003002494 | ANDERSON                     | JOSEPH                    | M                 | M.D.              | M                     | I                 | 802 B ST               |                        | SAN RAFAEL          |          949013026 | CA                   | US                     | Pathology     | Y                                | O                | 88305      | Tissue exam by pathologist   | 1,797           |               165 |                 168 | 133.43                       | 20.95                      | 263.23                     | 41.17                    | 105.53                       | 25.87                      |
| 1003002494 | ANDERSON                     | JOSEPH                    | M                 | M.D.              | M                     | I                 | 802 B ST               |                        | SAN RAFAEL          |          949013026 | CA                   | US                     | Pathology     | Y                                | O                | 88342      | Immunohistochemistry         | 568             |                72 |                  73 | 131.74                       | 16.22                      | 255.64                     | 31.27                    | 102.43                       | 39.47                      |
| 1003002494 | ANDERSON                     | JOSEPH                    | M                 | M.D.              | M                     | I                 | 802 B ST               |                        | SAN RAFAEL          |          949013026 | CA                   | US                     | Pathology     | Y                                | O                | G0416      | Sat biopsy prostate 1-20 spc | 25              |                25 |                  25 | 866.27                       | 0                          | 1733                       | 0                        | 693.02                       | 0                          |
+------------+------------------------------+---------------------------+-------------------+-------------------+-----------------------+-------------------+------------------------+------------------------+---------------------+--------------------+----------------------+------------------------+---------------+----------------------------------+------------------+------------+------------------------------+-----------------+-------------------+---------------------+------------------------------+----------------------------+----------------------------+--------------------------+------------------------------+----------------------------+

我到底做错了?如何向我表加载我的数据?

解决方法 1:

尝试

LOAD DATA LOCAL INFILE

我认为这个问题是你正在试图加载到 linux mysql 的 windows 文件。这意味着该文件是 local 到客户端。

赞助商