How to import text file to table with primary key as auto-increment

auto-incrementimportphpmyadmin

I have some bulk data in a text file that I need to import into a MySQL table. The table consists of two fields ..

  1. ID (integer with auto-increment)
  2. Name (varchar)

The text file is a large collection of names with one name per line …

(example)

John Doe
Alex Smith
Bob Denver

I know how to import a text file via phpMyAdmin however, as far as I understand, I need to import data that has the same number of fields as the target table. Is there a way to import the data from my text file into one field and have the ID field auto-increment automatically?

Thank you in advance for any help.

Best Answer

Another method I use that does not require reordering a table's fields (assuming the auto-increment field is the first column) is as follows:

1) Open/import the text file in Excel (or a similar program).

2) Insert a column before the first column. 

3) Set the first cell in this new column with a zero or some other placeholder.

4) Close the file (keeping it in its original text/tab/csv/etc. format).

5) Open the file in a text editor.

6) Delete the placeholder value you entered into the first cell.

7) Close and save the file.

Now you will have a file containing each row of your original file preceded by an empty column, which will be converted into the next relevant auto-increment value upon import via phpMyAdmin.

Related Topic