9

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.

Improve this question
4
  • 1
    Seems the code blocks did not appear correctly. My CSV is TAB delimited so I use \t during the import. Commented May 22, 2014 at 19:40
  • Your data seems to be tab delimited, not comma delimited.. can you confirm? Commented May 22, 2014 at 19:40
  • Yes, the CSV file is TAB delimited. Commented May 22, 2014 at 21:07
  • 1
    I found my problem. The CSV file generated by Excel 2003 had some weird characters at the beginning of the 1st line. I could not see them but they showed up in an SQL import error message. I created a new CSV file and copied the data over and the import then worked. Commented May 23, 2014 at 8:07

3 Answers 3

Reset to default
15

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.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I have tried un-checking the LOCAL keyword option but I then get this error:code#13 - Can't get stat of '/var/php_sessions/uploads/phpKnuoWl' (Errcode: 2)code.
I only have 3 import options: CSV, CSV using LOAD DATA and SQL. I have tried the first 2 only, with all possible settings I can think of but nothing works. Seems your screen shot is of a newer version than mine. I should say that I'm logged in to my web host and using phpMyAdmin from the control panel. I do let the file upload happen by the import statement. I do not upload the file myself.
You're using phpMyAdmin version 2.8.0.1, which was released 2006-03-08. That's over 8 years old!! There have been dozens of security holes fixed since then. I would urge you to put some pressure on your web host to upgrade, or else move to a different web host.
I found my problem. The CSV file generated by Excel 2003 had some weird characters at the beginning of the 1st line. I could not see them but they showed up in an SQL import error message. I created a new CSV file and copied the data over and the import then worked.
6

The following steps worked for me.

  • Import the file to a dummy database (i.e. don't select any database).

  • Hit import and select the appropriate CSV file that you want to load the data with.

  • phpMyadmin will create a database and an appropriate table to hold the data.

  • Hit export and select the SQL format

  • You will get to download an SQL file.

  • The file will have the appropriate query to insert the data into your table.

Make sure you change the default table name and column name to match your table name and column names.

Improve this answer

Comments

1

I have finally found an easy and reliable way to import from Excel to phpMyAdmin. Save in Excel in OpenDocument Spreadsheet format. Import into phpMyAdmin in that same format. Poof, it's all there. It sets up column names as A, B, C,... etcetera, but other than that it's flawless.

I have just saved myself another day of fiddling with quote marks, escapes, trailing tabs, extra carriage returns, delimiting delimiters, and professional database wrestling.

Improve this answer

1 Comment

No it's not all there. Importing a ODS into phpMyAdmin lose the hyperlinks (and e-mails saved as such), replacing them with None instead

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.