Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.19-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> create table tst( -> tchar char(5), -> tvarchar varchar(6), -> tbin binary(3), -> tvarbin varbinary(4)); Query OK, 0 rows affected (0.17 sec) -----tst.txt------ 5___56____63_34__4 555556666663334444 AAAAABBBBBBCCCDDDD ------------------ mysql> LOAD DATA INFILE 'f:/tst.txt' -> INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; Query OK, 3 rows affected (0.05 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from tst; +-------+----------+------+---------+ | tchar | tvarchar | tbin | tvarbin | +-------+----------+------+---------+ | 5___5 | 6____6 | 3_3 | 4__4 | | 55555 | 666666 | 333 | 4444 | | AAAAA | BBBBBB | CCC | DDDD | +-------+----------+------+---------+ 3 rows in set (0.00 sec) /***** COOL!!! Let's see with numeric (FLOAT, DOUBLE) *****/ mysql> drop table tst; Query OK, 0 rows affected (0.05 sec) mysql> create table tst( -> tint int(3), -> tfloat float(5,2), -> tdouble double(7,4)); Query OK, 0 rows affected (0.09 sec) -----tst.txt------ 333555.55777.7777 123123.12123.1234 111333.22333.4444 ------------------ mysql> LOAD DATA INFILE 'f:/tst.txt' -> INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; ERROR 1264 (22003): Out of range value adjusted for column 'tdouble' at row 1 mysql> LOAD DATA INFILE 'f:/tst.txt' -> ignore INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; Query OK, 3 rows affected, 5 warnings (0.03 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 5 mysql> show warnings; +---------+------+---------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'tdouble' at row 1 | | Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns | | Warning | 1264 | Out of range value adjusted for column 'tdouble' at row 2 | | Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns | | Warning | 1264 | Out of range value adjusted for column 'tdouble' at row 3 | +---------+------+---------------------------------------------------------------------------+ 5 rows in set (0.02 sec) mysql> select * from tst; +------+--------+----------+ | tint | tfloat | tdouble | +------+--------+----------+ | 333 | 555.50 | 999.9999 | | 123 | 123.10 | 999.9999 | | 111 | 333.20 | 999.9999 | +------+--------+----------+ 3 rows in set (0.00 sec) /* I don't know What happenig with Float and Double, but my shot is the ROUND made by Float... With DECIMAL will work fine. */ mysql> delete from tst; Query OK, 3 rows affected (0.03 sec) mysql> alter table tst modify column tfloat decimal(5,2); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> LOAD DATA INFILE 'f:/tst.txt' -> INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; Query OK, 3 rows affected, 3 warnings (0.03 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 3 mysql> show warnings; +-------+------+---------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------+ | Note | 1265 | Data truncated for column 'tfloat' at row 1 | | Note | 1265 | Data truncated for column 'tfloat' at row 2 | | Note | 1265 | Data truncated for column 'tfloat' at row 3 | +-------+------+---------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from tst; +------+--------+---------+ | tint | tfloat | tdouble | +------+--------+---------+ | 333 | 555.56 | 77.7777 | | 123 | 123.12 | 23.1234 | | 111 | 333.22 | 33.4444 | +------+--------+---------+ 3 rows in set (0.00 sec) /* Here i didn't understand what's going on! Let's change tfloat to VARCHAR */ mysql> delete from tst; Query OK, 3 rows affected (0.03 sec) mysql> alter table tst modify column tfloat varchar(5); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 ---tst.txt--- 333aaaaa777.7777 123bbbbb123.1234 111ccccc333.4444 ------------- mysql> LOAD DATA INFILE 'f:/tst.txt' -> INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns mysql> /* hmmmm DOUBLE trouble? Is Steve Ray Vougham present here? :P Let's try: */ mysql> delete from tst; Query OK, 1 row affected (0.03 sec) mysql> alter table tst -> modify column tfloat decimal(5,2), -> modify column tdouble varchar(7); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> LOAD DATA INFILE 'f:/tst.txt' -> INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; ERROR 1366 (HY000): Incorrect decimal value: '555.55a' for column 'tfloat' at row 1 /* hmmmmm reading 7 chars (5,2) I'm using floating point wrong? 5 positons WITH 2 floating point or 5 positions PLUS 2 floating point? Let's try...*/ -----tst.txt------ 33377777.77a12345a 12312345.77abbbbbb 11112345.12ccccccc ------------------ mysql> LOAD DATA INFILE 'f:/tst.txt' -> INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; ERROR 1264 (22003): Out of range value adjusted for column 'tfloat' at row 1 /* and without a dot? */ ----tst.txt------ 3337777777a12345a 1231234577abbbbbb 1111234512ccccccc ----------------- mysql> LOAD DATA INFILE 'f:/tst.txt' -> INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; ERROR 1264 (22003): Out of range value adjusted for column 'tfloat' at row 1 /* Same... Just for fun, let's ignore the error... */ mysql> LOAD DATA INFILE 'f:/tst.txt' -> ignore INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; Query OK, 3 rows affected, 3 warnings (0.03 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 3 mysql> show warnings; +---------+------+----------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'tfloat' at row 1 | | Warning | 1264 | Out of range value adjusted for column 'tfloat' at row 2 | | Warning | 1264 | Out of range value adjusted for column 'tfloat' at row 3 | +---------+------+----------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from tst; +------+--------+---------+ | tint | tfloat | tdouble | +------+--------+---------+ | 333 | 999.99 | a12345a | | 123 | 999.99 | abbbbbb | | 111 | 999.99 | ccccccc | +------+--------+---------+ 3 rows in set (0.00 sec) /* I think the server is reading 7 chars (decimal(5,2)) but with the DOT it's 8 chars, and without a dot the number 7777777 is more that the field support (999.99). ok, let's try this: */ ----tst.txt------ 3337777.77a12345a 1231234.77abbbbbb 1111234.12ccccccc ----------------- /* hmmmmm i think will not work! the server reads 7 chars (5+2) but expect a DOT and convert to a 5 lenght WITH 2 floating point.*/ mysql> LOAD DATA INFILE 'f:/tst.txt' -> ignore INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; Query OK, 3 rows affected, 3 warnings (0.03 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 3 mysql> show warnings; +---------+------+----------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'tfloat' at row 1 | | Warning | 1264 | Out of range value adjusted for column 'tfloat' at row 2 | | Warning | 1264 | Out of range value adjusted for column 'tfloat' at row 3 | +---------+------+----------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from tst; +------+--------+---------+ | tint | tfloat | tdouble | +------+--------+---------+ | 333 | 999.99 | a12345a | | 123 | 999.99 | abbbbbb | | 111 | 999.99 | ccccccc | +------+--------+---------+ 3 rows in set (0.00 sec) /* Last chance: */ ----tst.txt------ 3330777.77a12345a 1230234.77abbbbbb 1110234.12ccccccc ----------------- mysql> delete from tst; Query OK, 3 rows affected (0.05 sec) mysql> LOAD DATA INFILE 'f:/tst.txt' -> ignore INTO TABLE tst -> FIELDS TERMINATED BY '' -> ENCLOSED BY '' -> LINES TERMINATED BY '\r\n'; Query OK, 3 rows affected (0.05 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from tst; +------+--------+---------+ | tint | tfloat | tdouble | +------+--------+---------+ | 333 | 777.77 | a12345a | | 123 | 234.77 | abbbbbb | | 111 | 234.12 | ccccccc | +------+--------+---------+ 3 rows in set (0.00 sec) /* It will never be! Server reads 7 position from file (5+2), but expects 6 (5 + 1 DOT) only puting a 0 before the number, in some cases impossible. Import to a INTEGER then do the divisions is the easyest way. */