Import CSV file into MySQL using phpMyAdmin

Question

I have searched and read many posts/articles regarding importing a CSV file into a MySQL database using phpMyAdmin 2.8.0.1 and they make it sound so simple, in actually it is not. Nothing I do works correctly.

I have a table with 2 columns, both defined as NOT NULL. The primary index is configured to use both columns. I have many CSV files to import but I'm starting with the small ones first. Here is a sample of my CSV data file:

type    description
T   Antarctic Territory
T   Dependency
T   Independent State
T   Proto Dependency
T   Proto Independent State

There are only 17 rows to import but usually I get 0 rows inserted and sometimes I get 1 row inserted but it is in the wrong format. What I mean is that column 1 is blank and column 2 contains the data of both columns, in the wrong order. This is the SQL generated by my import attempt:

LOAD DATA LOCAL INFILE '/var/php_sessions/uploads/phpiptDPV' REPLACE INTO TABLE `country_types`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1
LINES (
`type` ,
`description`
)# MySQL returned an empty result set (i.e. zero rows).

Can anyone see where I'm going wrong? I've spent a few days researching and trying different things but I'm ready to throw out phpMyAdmin.

Answer 1

The problem is with LOCAL. There are two concepts of "local" in this case. You probably mean that the CSV file is on the workstation where you are running your browser accessing phpMyAdmin.

But the LOAD DATA LOCAL INFILE statement is running on the web server where phpMyAdmin is running. So it's looking for the file on the web server. When I tried this I got this error output by phpMyAdmin:

#7890 - Can't find file '/Users/billkarwin/t.csv'. 

You can try using phpMyAdmin's Import feature.

1. Select your table.
2. Click the Import tab.
3. Click the Choose file button to browse to your local csv file.
4. Select the 'CSV using LOAD DATA' for Format.
5. Choose other Format-Specific Options.
6. Click Go.