我有一个文本数据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"直接读入数据库中,应该怎么办?
解决方案 »
- 错误1045 (28000): Access denied for user 'root'@'localhost'?
- mysql数据库文件导入PowerDesigner问题
- mysql编码为latin1的数据库中的中文数据乱码问题
- 从命令行启动 mysql 服务,为什么命令行窗口总会失去响应呢?
- mysql的疑难杂症请教
- myodbc3.5支持mysql5.0.24a最新版本么?
- 根据数据库创建文件如,sql.sql,可能有很多张表一起,如何通过命令建立表?
- select permission denied on ..(怎么办)
- 请教:连接人数不多!去显示Too many connections 错误!
- 无聊!散分!
- 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 原创,版权所有,转载请注明作者和出处!