Mysqlimport (and 'load data ...') are incorrectly importing data from my flat-file input.
The commands I've used to load are:
load data local infile '/srv/django/tmp/icdo_prev/orders_icdodiagnosis.csv'
into table orders_icdodiagnosis
character set latin1
fields terminated by ','
lines terminated by '\n';
or
mysqlimport -p -uroot --lines-terminated-by='\n' --fields-optionally-enclosed-by='"' --fields-terminated-by=',' --local -f -i <dbname> orders_icdodiagnosis.csv
Here are examples of what I'm seeing after import:
mysql> select code from orders_icdodiagnosis where id = 2311;
+---------+
| code |
+---------+
|9965/3
+---------+
1 row in set (0.00 sec)
mysql> select trim(code) from orders_icdodiagnosis where id = 2311;
+------------+
| trim(code) |
+------------+
|5/3
+------------+
1 row in set (0.00 sec)
mysql> select ltrim(code) from orders_icdodiagnosis where id = 2311;
+-------------+
| ltrim(code) |
+-------------+
|/3
+-------------+
1 row in set (0.08 sec)
The input data is '9965/3', and is unquoted in the flat-file. (If I use quotes in the flat-file, the quotes actually get entered as part of the data, ie '"9965/3"'.)
This looks like a line ending problem, but the data are truncated on the front side. In the first example the display end lines are absent, so something is amiss, but I haven't figured it out yet.
Can anyone suggest a means of correcting the issue?
Thanks for any help you can supply. Joe White
Have seen the same behavior with windows mysql and linux mysql.