我正在第一列中从CSV文件中将所有数据导入MySQL.所有其他列获取NULL值 [英] I am getting all data imported in MySQL from CSV file in the first column. All other columns gets NULL values

问题描述

我在Mac本地使用MySQL. 我有一个CSV文件，所有字段都用双引号引起来. 这是我的CSV文件的第一行的摘录:

I am a using MySQL locally to my Mac. I have a CSV file all fields enclosed in double quotes. Here is an extract of the first rows of my CSV file:

$ head -5 companies.csv
"COMPANY_ADDRESS1","COMPANY_ADDRESS2","COMPANY_CITY","COMPANY_COUNTRY","COMPANY_FAX","COMPANY_GERMANY","COMPANY_ID","COMPANY_INDIANAPOLIS","COMPANY_NAME","COMPANY_STATE","COMPANY_TELEPHONE","COMPANY_VAT","COMPANY_ZIP"
"980 Madison Avenue","6th Floor","New York","USA","","","1393","","Lucky Fives LLC","NY","212-74-2313","","10075"
"1209 Orange Street","","Wilmington","USA","","","1394","","Global Five Holdings","DE","-","","19801"
"2020 Ponce de Leon Boulevard","PH2","Coral Gables","USA","","1015110","1395","","Lion Aero Corporation","FL","305-755-5120","","33134"
"100 NE Adams Street","","Peoria","USA","","","1396","","Caterpillar Inc.","IL","[phone]","","61630"

这是我的导入命令:

$ mysqlimport --local --ignore-lines=1 my_db companies.csv -u root --fields-enclosed-by='\"' --lines-terminated-by='\r\n' --fields-terminated-by=','

我正在导入所有记录，没有跳过任何内容:

I am getting all records imported, nothing is skipped:

companies: Records: 3063  Deleted: 0  Skipped: 0  Warnings: 104

但是，为什么我要在第一列中导入所有数据，而所有其他列都填充为NULL?

However, why I am getting all data imported in the first column and all other columns are populated with NULL?

该表以前是使用支持要加载的数据的所有列创建的.

The table was previously created with all columns necessary to support the data to be loaded.

推荐答案

测试架构:

-- drop table if exists aT;
create table aT
(   -- address thing
    COMPANY_ADDRESS1 varchar(100) not null,
    COMPANY_ADDRESS2 varchar(100) not null,
    COMPANY_CITY varchar(100) not null,
    COMPANY_COUNTRY varchar(100) not null,
    COMPANY_FAX varchar(100) not null,
    COMPANY_GERMANY varchar(100) not null,
    COMPANY_ID varchar(100) not null,
    COMPANY_INDIANAPOLIS varchar(100) not null,
    COMPANY_NAME varchar(100) not null,
    COMPANY_STATE varchar(100) not null,
    COMPANY_TELEPHONE varchar(100) not null,
    COMPANY_VAT varchar(100) not null,
    COMPANY_ZIP varchar(100) not null
);

命令以交互方式或通过程序运行:

LOAD DATA INFILE 'c:\\nate\\aT.csv' INTO TABLE aT
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;
select * from aT;

or Linux:

LOAD DATA INFILE '/home/nate/aT.csv' INTO TABLE aT
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;

将数据导入没问题

从aT中选择*； -查看导入

select * from aT; -- view the import

现在用于命令行(创建以上其中一项的.sql):

truncate aT; -- run this first

mysql -u root -p so_gibberish2 < c:\nate\run_this1.sql

so_gibberish2是数据库名称.数据进入.因为mysql客户端不受bash或其他命令行转义序列的约束.

so_gibberish2 is the database name. Data comes in. Because the mysql client is not subject to bash or other command line escape sequences.

这篇关于我正在第一列中从CSV文件中将所有数据导入MySQL.所有其他列获取NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！