导入随机行中具有空值的特定CSV文件时出现MySQL问题 [英] MySQL issue when importing specific CSV files with blank values in random rows

问题描述

我之前提到过这个，但在做了一些研究后，我意识到我在错误的地方看。这是情况。我创建这个表：

I had brought this up before earlier, but after doing some research, I realized I was looking in the wrong place. Here is the situation. I create this table:

CREATE TABLE PC_Contacts
(
POC VARCHAR(255) PRIMARY KEY NOT NULL,
Phone_1 VARCHAR(255),
Phone_2 VARCHAR(255)
);

我将一个CSV文件导入MySQL，它的值为我的表PC_Contacts：

I import a CSV file into MySQL which has the values for my table PC_Contacts:

USE Network
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\PC_Contacts.csv'
INTO Table PC_Contacts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

导入后的输出如下所示：

My output after importing looks like this:

+------------------+--------------+---------------+
| POC              | Phone_1      | Phone_2       |
+------------------+--------------+---------------+
 |April Wilson    | 123-456-5000 | 123-456-5006
             |     | 123-456-2222 |
             |     | 123-456-5331 |
             |     | 123-456-7772 |
 |Anton Watson  | 123-456-1258 | 123-456-6005
 |Elisa Kerring   | 123-456-1075 | 123-456-4475

现在你可能还记得，根据我的代码输入，POC是PK。我在原始的CSV文件中，每一行都有一个值。但是，正如你所看到的，右边没有值的任何东西都会影响左列的值。然而，如果我在GUI中查找并拉起表，它显示单元格填充的值，所以数据就在那里。如果我把xxx-xxx-xxxx，它会解决这个问题：

Now as you may recall, based on my code input, that POC is the PK. I had, in the original CSV file, a value for every line. However, as you see, anything that has no value on the right affects the left column's values. However, if I looked in the GUI and pulled up the table there, it showed the cell as populated with the value, so the data is there. If I were to put in xxx-xxx-xxxx, it would fix the issue:

    +------------------+--------------+---------------+
    | POC              | Phone_1      | Phone_2       |
    +------------------+--------------+---------------+
     |April Wilson    | 123-456-5000 | 123-456-5006
     |Nicky Nite        | 123-456-2222 | xxx-xxx-xxxx
     |Nicole           | 123-456-5331 | xxx-xxx-xxxx
     |Becky            | 123-456-7772 | xxx-xxx-xxxx
     |Anton Watson  | 123-456-1258 | 123-456-6005
     |Elisa Kerring   | 123-456-1075 | 123-456-4475

显然，我的意图是，我可以看到的价值，格式化。是否有一个特殊的SELECT命令可能？

Obviously my intentions are so that I can see the value without having to apply special formatting in the command line. Is there a special SELECT command for that maybe?

这是指向.CSV的一部分的链接，请求：

Here is a link to a portion of the .CSV, as requested:

https://drive.google.com/file/ d / 0B0MMqHN75RpGdkZhcGp0SWtmams / view？usp = sharing

推荐答案

您的CSV文件包含回车，的行，其中断格式化。使用：

Your CSV file contains a carriage return with newline at the end of row, which breaks formatting. Use:

SELECT POC, Phone_1, REPLACE(Phone_2, '\r', '') AS Phone_2 FROM PC_Contacts;

或如下更改导入查询：

USE Network
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\PC_Contacts.csv'
INTO Table PC_Contacts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

并使用简单 SELECT ：

SELECT * FROM PC_Contacts;

这篇关于导入随机行中具有空值的特定CSV文件时出现MySQL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！