>>>>> "Para-dox" == Para-dox <paradox@stripped> writes:
Para-dox> (Please email directly to me, I am not on the list at the moment)
Para-dox> I am trying to import a file in this format...
Para-dox> 123456,34643,843.33,""
Para-dox> 334343,13566,234.64,"P"
Para-dox> (about 300,000 of these)
Para-dox> into a table with these fields:
Para-dox> INVOICE INT,
Para-dox> DATE INT,
Para-dox> AMOUNT DECIMAL(7,2),
Para-dox> TYPE CHAR(1)
Para-dox> with this commands
Para-dox> LOAD DATA LOCAL INFILE '/home/mydir/pm.txt' INTO TABLE MYTBL FIELDS
Para-dox> TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
Para-dox> It aint working.
Para-dox> The result is a table with one record,
Para-dox> 123456,34643,843.33,"
Para-dox> I'm guessing that MySQL doesn't like the end empty string denoted by "". How
Para-dox> can I fix this? I tried making TYPE a CHAR(10), and then I get
Para-dox> 123456,34643,843.33,""\n334343
Para-dox> the \n is a newline character.
Hi!
I just tested your example:
mysql> create table test (INVOICE INT, DATE INT,AMOUNT DECIMAL(7,2),TYPE CHAR(1));
Query OK, 0 rows affected (0.01 sec)
mysql> load data LOCAL INFILE '/tmp/skr2' INTO TABLE test FIELDS TERMINATED BY ','
> OPTIONALLY ENCLOSED BY '"';
Query OK, 2 rows affected (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+---------+-------+--------+------+
| INVOICE | DATE | AMOUNT | TYPE |
+---------+-------+--------+------+
| 123456 | 34643 | 843.33 | |
| 334343 | 13566 | 234.64 | P |
+---------+-------+--------+------+
2 rows in set (0.00 sec)
Please check that the file is really new line terminated !
Regards,
Monty
