貌似只能写程序刷,一个正则表达式替换的问题,也不复杂,用python写得话,9行搞定:import re
f = file('G:/20140107.012897.jyy_etdata.csv/simpletest.csv')
f2 = file('G:/20140107.012897.jyy_etdata.csv/simpletest~.csv','w')
for line in f:
f2.write(re.sub(r',,',r',\N,',line))
f2.close()
f.close()
还有一个比较笨的办法,建个临时表,只一个字段,导入到表,FIELDS TERMINATED BY ‘\r\n’
然后,update ,replace,把,,替换成,0,或者,\N,
然后导出,
然后导入。
f = file('G:/20140107.012897.jyy_etdata.csv/simpletest.csv')
f2 = file('G:/20140107.012897.jyy_etdata.csv/simpletest~.csv','w')
for line in f:
f2.write(re.sub(r',,',r',\N,',line))
f2.close()
f.close()
还有一个比较笨的办法,建个临时表,只一个字段,导入到表,FIELDS TERMINATED BY ‘\r\n’
然后,update ,replace,把,,替换成,0,或者,\N,
然后导出,
然后导入。
-> id int,
-> name CHAR(9),
-> data VARCHAR(100),
-> height float,
-> age int
-> );
Query OK, 0 rows affected (0.16 sec)mysql> LOAD DATA INFILE 'd:/aa.txt'
-> INTO TABLE Test_Book1
-> CHARACTER SET utf8
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> lines terminated by '\r\n';
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 2mysql> select * from Test_Book1;
+------+-----------+------+--------+------+
| id | name | data | height | age |
+------+-----------+------+--------+------+
| 1 | testdata1 | jim | 170.5 | 34 |
| 2 | testdata2 | lili | 0 | 34 |
| 3 | testdata3 | tip | 0 | 36 |
| 4 | testdata4 | sam | 150.4 | 23 |
+------+-----------+------+--------+------+
4 rows in set (0.00 sec)mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.58-community |
+------------------+
1 row in set (0.00 sec)mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)mysql>
版主你好,我又试了下,还是不行。
我的SQL版本是'5.6.20-log'@@sql_mode不是空的,如下
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'请麻烦看看是不是这些地方的问题?如果是的话该怎么设置?谢谢!
谢谢回答的各位。