加载数据内文件 (*.csv) - 忽略空单元格

发布于 2024-10-16 17:22:57 字数 601 浏览 2 评论 0原文

我即将将一个大型 (500 MB) *.csv 文件导入到 MySQL 数据库。

我就这样：

LOAD DATA INFILE '<file>'
    REPLACE
    INTO TABLE <table-name>
    FIELDS
        TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES ( #Header
        <column-name1>,
        <column-name2>,
        ...
    );

我的其中一个列有问题（它的数据类型是 int） - 我收到一条错误消息：

错误代码：1366 不正确的整数值：行列的“ ”

我查看了 *.csv 文件中的这一行。导致错误的单元格内部只有一个空格（如下所示：...; ;...）。

如何告诉 SQL 忽略此列中的空格？

由于 *.csv 文件非常大，之后我必须导入更大的文件，因此我想避免编辑 *.csv 文件；我正在寻找 SQL 解决方案。

原文

I'm about to import a large (500 MB) *.csv file to a MySQL database.

I'm as far as that:

LOAD DATA INFILE '<file>'
    REPLACE
    INTO TABLE <table-name>
    FIELDS
        TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES ( #Header
        <column-name1>,
        <column-name2>,
        ...
    );

I have a problem with one of the coluns (it's data type is int) - I get an error Message:

Error Code: 1366 Incorrect integer value: ' ' for column at row

I looked at this line in the *.csv-file. The cell that causes the error has just a whitespace inside (like this: ...; ;...).

How can I tell SQL to ignore whitespaces in this column?

As the *.csv-file is very big and I have to import even bigger ones afterwards, I'd like to avoid editing the *.csv-file; I'm looking for a SQL-solution.

分享到QQ

分享到微博

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

发布评论

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

你如我软肋 2024-10-23 17:22:57

像这样添加一个 SET COLUMN：

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

您需要用处理“空间”的表达式替换 @var1/100 并转换为 -Infinity 或 0 或 42...不确定..

Add a SET COLUMN like so:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

You need to replace the @var1/100 with an expression that handles the 'space' and convert to -Infinity or 0 or 42... not sure..

回复收藏 0 原文

执笏见 2024-10-23 17:22:57

_{这个答案最初作为@speendo的编辑包含在问题中；我已将其转换为正确的答案。}

解决方案是：

LOAD DATA INFILE '<file>'
    REPLACE
    INTO TABLE <table-name>
    FIELDS
        TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES ( #Header
        <column-name1>,
        <column-name2>,
        @var1 #the variable that causes the problem
        ...
    )
    SET <column-name-of-problematic-column> = CASE
        WHEN @var1 = ' ' THEN NULL
        ELSE @var1
    END
;

_{This answer was originally included in the question as an edit by @speendo; I have converted it into a proper answer.}

The solution is:

LOAD DATA INFILE '<file>'
    REPLACE
    INTO TABLE <table-name>
    FIELDS
        TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES ( #Header
        <column-name1>,
        <column-name2>,
        @var1 #the variable that causes the problem
        ...
    )
    SET <column-name-of-problematic-column> = CASE
        WHEN @var1 = ' ' THEN NULL
        ELSE @var1
    END
;

回复收藏 0 原文

~没有更多了~