This is a discussion on Mysql CSV Import Problem within the MySQL Database forums, part of the Database Forums category; Hello I`ve got some problem with mysql import data using LOAD DATA INFILE. IGNORE <count> LINES clause ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
|||
|
Hello
I`ve got some problem with mysql import data using LOAD DATA INFILE. IGNORE <count> LINES clause doesn`t work like I expect and it makes problems. I'm importing CSV file and unfortunately file generated from MSOffice is other from CSV generated from OpenOffice. OpenOffice adds quote (") chars to column header labels and it makes problem with import to mysql (for MSOffice-CSV everything is OK). If I delete quotes from header line everything works fine. It`s strange for me that with IGNORE 1 LINES flag for import statement content of this first line has effect for import result. I think it`s rational that ignored lines should be deal as they are absent in an import file Maybe there is some bug in mysql or i should change some database setting or make changes im my LOAD DATA INFILE statement. Here is example i`ve tried, i`ve checked on mysql versions: 5.0.16-nt i 4.1.22-community --file 1a.csv: "KOD_TEMA";"NAZWA" 1;"TOW;AR 1" 2;"TOWA''R 2'" 3;"TOWAR 3;;" 4;"T;OWA""R 4" 5;"TOWAR 5" 6;"TO""WA""R 6" --file 1b.csv (after delete quotes from header): KOD_TEMA;NAZWA 1;"TOW;AR 1" 2;"TOWA''R 2'" 3;"TOWAR 3;;" 4;"T;OWA""R 4" 5;"TOWAR 5" 6;"TO""WA""R 6" --mysql console mysql> create table test1 (kod_tema int, nazwa varchar(20)); Query OK, 0 rows affected (0.22 sec) mysql> desc test1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | kod_tema | int(11) | YES | | NULL | | | nazwa | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> load data infile 'c:\\1a.csv' into table test1 fields terminated by ';' enclosed by '"' escaped by '"' lines terminated by '\r\n' IGNORE 1 LINES; Query OK, 0 rows affected (0.00 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 mysql> load data infile 'c:\\1b.csv' into table test1 fields terminated by ';' enclosed by '"' escaped by '"' lines terminated by '\r\n' IGNORE 1 LINES; Query OK, 6 rows affected (0.00 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 Thanks for any answer |
![]() |
| Thread Tools | |
| Display Modes | |
|
|