使用过滤器上传csv文件 [英] Uploading csv file using filters

问题描述

正在尝试使用以下查询将CSV数据上传到MYSQL表。它正在成功运行。有2000万个数据的CSV文件。但是现在我仍然有一个上传数据的问题。

我的CSV文件结构如下：

<$ p $ 姓名phone_no DND
xxx 99934034343 A
xxx 99934034345 D
xxx 99934034346 A
xxx 99934034347 D


我只想使用下面的命令插入主动的A。

  LOAD DATA LOCAL INFILE'/root/782012_23.csv'
 INTO TABLE tbl_dndno 
 FIELDS TERMINATED BY '，'ENCLOSED BY''
线路终止'\\\
'; 
  

这样的预期记录

 姓名phone_no DND 
 xxx 99934034343 A 
 xxx 99934034346 A 
  

解决方案

我猜你的命令工作正常，但用4行代替预计2。

LOAD DATA IN FILE文档似乎不支持你所要求的，它只能排除行号，所以如果你有办法在创建这个csv文件时知道行号，你可以使用这个特性，否则有两个解决方法：

你可以在使用（在Linux上）加载它之前（在mysql之外）过滤文件

  grepD $/root/782012_23.csv> filteredfile; LOAD DATA LOCAL INFILE'filteredfile'... 
  

或者在通过SQL加载后过滤：

  your_command; mysql -u user -ppasswordDELETE FROM yourtable WHERE DND ='A'; 
  

Am trying to upload CSV data to MYSQL table using the below query.It's running successfully. The CSV file having 20 million data. But still now I have a problem to upload the data.

My CSV file structure like this :

Name phone_no     DND
xxx  99934034343  A
xxx  99934034345  D
xxx  99934034346  A
xxx  99934034347  D

I want to insert the active no "A" only using the below command.

LOAD DATA LOCAL INFILE '/root/782012_23.csv'
INTO TABLE tbl_dndno
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Expected record like this

Name phone_no     DND
 xxx  99934034343  A
 xxx  99934034346  A

解决方案

I guess your command is working fine but filling your table with 4 rows instead of the expected 2.

LOAD DATA IN FILE documentation seem not to support what you are asking for, it can only exclude line by number, so if you have a way to know the line numbers while creating this csv file you could use this feature. Otherwise there are 2 workarounds:

You can filter your file before loading it (outside of mysql) with (on Linux)

grep "D$" /root/782012_23.csv > filteredfile; LOAD DATA LOCAL INFILE 'filteredfile' ...

or filter after the loading via SQL:

your_command;mysql -u user -ppassword "DELETE FROM yourtable WHERE DND = 'A'";

这篇关于使用过滤器上传csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！