The important parts with regards to the input file are
Code:
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
and
Code:
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
Fields
The
fields section defines what fields look like.
'terminated by' is the field separator, e.g. a comma.
'enclosed by' which character encloses the field; if your field contains the separator, the field needs to be enclosed, else the separator in the field is treated as a separator instead of as part of the field; it's often a double quote. If the fields contains one or more double quotes, this will confuse mysql and therefore they need to be escaped (hence the
'escaped by')
Code:
1,"sturkenboom, wim"
3,"pietersen, piet"
4,"double quote ""
The above is a sample csv file containing an ID (number) and a name. The fields are seperated by a comma so the ID is '1' and the name is 'sturkenboom, wim'. Because lastname and firstname are separated by a comma as well, we need an
'enclosed by'. The last record contains a double quote, so that needs to be escaped with e.g. a backslash; this needs to be fixed in the csv file so we get
Code:
1,"sturkenboom, wim"
3,"pietersen, piet"
4,"double quote \""
And the mysql statement is
Code:
mysql> load data infile 'mytesttable.csv' into table mytesttable fields terminated by ',' enclosed by '"' escaped by '\\';
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
The result is
Code:
mysql> select * from mytesttable;
+----+------------------+
| PK | name |
+----+------------------+
| 1 | sturkenboom, wim |
| 3 | pietersen, piet |
| 4 | double quote " |
+----+------------------+
3 rows in set (0.00 sec)
Lines
The
lines section defines what lines look like. No experience with the
'start with' so I will skip that one.
'terminated by' is important when the input is generated on e.g. a dos/windows machine as the line terminator in dos is '\r\n' while in linux it is '\n'
So if your file comes from a dos/windows system, you (might) need to add
lines terminated by '\r\n' at the end of your mysql statement
Code:
mysql> load data infile 'mytesttable.csv' into table mytesttable fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';
If you don't come right, please post the first few lines of your datafile as well as some of the lines that are not imported correctly. Also post the mysql statement that you have used to import.
WimS
PS assuming you're using mysql under linux
Automating data entry into MySQL database?
