我有一个文本数据xxx.txt,格式如下:
"1","xxx","yyyy","zzzzz"
"2,"aaaa","bbbb","ccccc"
"3","dddd",""eeee"","ffff"
"4","kkkk","lllll","mmmmm","nnnnn"
我使用如下语句导入数据库中:
LOAD DATA INFILE 'xxx.txt' INTO TABLE dhyhls
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
执行时,总提示
ERROR 1261 (01000):Row 3 doesn't contain data for all colums
我知道问题在于第3行""eeee""引起的问题,
可是该文本文件是自动生成的,行数可能达到数万行,我不可能去逐行查找修改,而且我也希望将"eeee"直接读入数据库中,应该怎么办?
"1","xxx","yyyy","zzzzz"
"2,"aaaa","bbbb","ccccc"
"3","dddd",""eeee"","ffff"
"4","kkkk","lllll","mmmmm","nnnnn"
我使用如下语句导入数据库中:
LOAD DATA INFILE 'xxx.txt' INTO TABLE dhyhls
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
执行时,总提示
ERROR 1261 (01000):Row 3 doesn't contain data for all colums
我知道问题在于第3行""eeee""引起的问题,
可是该文本文件是自动生成的,行数可能达到数万行,我不可能去逐行查找修改,而且我也希望将"eeee"直接读入数据库中,应该怎么办?
解决方案 »
- MySQL服务器一般能处理多大数据量的处理(标准配置的硬件设备)。
- 请教一下,mysql_connect的CLIENT_INTERACTIVE模式的区别
- mysql无法插入中文
- 真诚求教:往表随机插入一个"集合"中的某个字符串+MySQL
- 急求:最新两行记录的时间字段值之差,非常感谢!
- mysql怎样对一个库进行全文检索?
- not null default 的问题
- mysql 奇怪的问题-很急,解决不了都回不了家过年了!!
- mysql中的库的字段不能自动递增的么,就象ms sql中的identity
- 为什么这条SQL在服务器上能执行,在我本机却失败了呢?
- SQL语句报错
- mysql如何实现非自增ID
用mysql语句处理,有好办法吗?
查看 MySQL 当前连接的 sql_modemysql> show variables like 'sql_mode';+---------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------+
| sql_mode | strict_trans_tables,no_auto_create_user,no_engine_substitution |
+---------------+----------------------------------------------------------------+设置 MySQL sql_mode,使其不包含 “strict_trans_tables” mode找到办法了,呵呵。
set sql_mode='';这样,就可以利用 MySQL load data infile,继续向 MySQL 中导入数据了。否则,MySQL 会终止导入过程,并抛出下面几种错误信息:ERROR 1261 (01000): Row 1 does not contain data for all columns
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns 本文由 www.sqlstudy.com 原创,版权所有,转载请注明作者和出处!