List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 7 1999 1:13pm
Subject:Import problem
View as plain text  
>>>>> "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
Thread
Import problemPara-dox6 Jul
  • Re: Import problemT├Ánu Samuel7 Jul
  • Import problemMichael Widenius7 Jul