Mysql CSV Import Problem

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 ...


Go Back   Usenet Forums > Database Forums > MySQL Database

FAQ Members List Calendar Search Today's Posts Mark Forums Read
  #1 (permalink)  
Old 05-14-2008
gRabbi
 
Posts: n/a
Default Mysql CSV Import Problem

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


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:27 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.0.0