尝试将csv文件导入mysql表时总是出错

vkc1a9a2 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(478)

我正在尝试将一个csv文件导入数据库的表中。csv文件的前两行是：

Nr$Name$Telefon$Flaeche$Einwohner$Pendler    
1$Innenstadt$069 755 10100$2.11$10100$

我试图导入到的表（“polizeireviere”）如下所示：

+-----------+---------------+------+-----+---------+-------+     
| Field     | Type          | Null | Key | Default | Extra |     
+-----------+---------------+------+-----+---------+-------+      
| Nr        | int(5)        | YES  |     | NULL    |       |      
| Name      | varchar(20)   | YES  |     | NULL    |       |     
| Telefon   | varchar(34)   | YES  |     | NULL    |       |    
| Flaeche   | decimal(10,0) | YES  |     | NULL    |       |     
| Einwohner | int(10)       | YES  |     | NULL    |       |     
| Pendler   | int(10)       | YES  |     | NULL    |       |     
+-----------+---------------+------+-----+---------+-------+

我要导入的命令如下：

load data infile 'polizeireviere.csv'    
into table polizeireviere     
fields terminated by '$'    
lines terminated by '\n'    
ignore 1 lines;

但是我得到了一个错误：

' for column 'Pendler' at row 1nteger value: '

但我不知道这是什么意思，因为“pendler”列中的所有条目都是空的或整数。

mysql Import

来源：https://stackoverflow.com/questions/50817022/always-getting-errors-trying-to-import-csv-file-into-mysql-table

1条答案

按热度按时间

vuktfyat1#

您可以使用set子句解决此问题，如下所示：
加载命令应如下所示：

load data infile '/var/lib/mysql-files/vivek/test.dat' 
into table polizeireviere
fields terminated by '$'
lines terminated by '\n'
ignore 1 rows
(Nr,Name,Telefon,Flaeche,Einwohner,@Pendler)
SET Pendler = IF(@Pendler='',null,@Pendler);

示例：

mysql> CREATE TABLE polizeireviere (
    ->   Nr int(5) DEFAULT NULL,
    ->   Name varchar(20) DEFAULT NULL,
    ->   Telefon varchar(34) DEFAULT NULL,
    ->   Flaeche decimal(10,0) DEFAULT NULL,
    ->   Einwohner int(10) DEFAULT NULL,
    ->   Pendler int(10) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.46 sec)

mysql> 
mysql> load data infile '/var/lib/mysql-files/vivek/test.dat' 
    -> into table polizeireviere
    -> fields terminated by '$'
    -> lines terminated by '\n'
    -> ignore 1 rows
    -> (Nr,Name,Telefon,Flaeche,Einwohner,@Pendler)
    -> SET Pendler = IF(@Pendler='',null,@Pendler);
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from polizeireviere;
+------+------------+---------------+---------+-----------+---------+
| Nr   | Name       | Telefon       | Flaeche | Einwohner | Pendler |
+------+------------+---------------+---------+-----------+---------+
|    1 | Innenstadt | 069 755 10100 |       2 |     10100 |    NULL |
+------+------------+---------------+---------+-----------+---------+
1 row in set (0.00 sec)

赞(0）回复(0）举报 2021-06-21

我来回答

尝试将csv文件导入mysql表时总是出错

1条答案

示例：

相关问题

热门标签

最新问答