Database Reference
In-Depth Information
For
Lines terminated by
, recent versions of phpMyAdmin offer the
auto
choice,
which should be tried first as it detects the end-of-line character automatically.
We can also specify manually which characters terminate the lines. The usual
choice is
\n
for UNIX-based systems,
\r\n
for DOS or Windows systems, and
\r
for
Mac-based system (up to Mac OS 9). If in doubt, we can use a hexadecimal file editor
on our client computer (not part of phpMyAdmin) to examine the exact codes.
By default, phpMyAdmin expects a CSV file with the same number of fields and
the same field order as the target table. But this can be changed by entering a
comma-separated list of column names in
Column names
, respecting the source
file format. For example, let's say our source file contains only the author ID and the
author name information:
"1","John Smith"
"2","Maria Sunshine"
We'd have to put
id
,
name
in
Column names
to match the source file.
When we click
Go
, the import is executed and we get a confirmation. We might also
see the actual
INSERT
queries generated if the total size of the file is not too big.
Import has been successfully finished, 2 queries executed.
INSERT INTO `author` VALUES (
'1', 'John Smith', '+01 445 789-1234'
)# 1 row(s) affected.
INSERT INTO `author` VALUES ('2', 'Maria Sunshine', '333-3333'
)# 1 row(s) affected.
CSV using LOAD DATA
With this method, phpMyAdmin relies on the server's
LOAD
DATA
INFILE
or
LOAD
DATA
LOCAL
INFILE
mechanisms to do the actual import, instead of processing the
data internally. These statements are the fastest way for importing text in MySQL.
They cause MySQL to start a read operation either from a file located on the MySQL
server (
LOAD
DATA
INFILE
) or from another place (
LOAD
DATA
LOCAL
INFILE
),
which in this context, is always the web server's file system. If the MySQL server
is located on a computer other than the web server, we won't be able to use the
LOAD
DATA
INFILE
mechanism.
Requirements
Relying on the MySQL server has some consequences. Using
LOAD
DATA
INFILE
requires that the logged-in user possess a global
FILE
privilege. Also, the file itself
must be readable by the MySQL server's process.
Search WWH ::
Custom Search