Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 939
webworm Avatar asked Mar 17 '10 15:03

webworm


People also ask

Is auto increment a primary key?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

How do I add auto increment to a column?

Here's the SQL statement to add AUTO INCREMENT constraint to id column. ALTER TABLE sales MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY; Next we will add a couple of rows in sales table. As you can see, the MySQL has automatically increased and populated id column with values 7 and 8.

How do you set a field as auto increment in MySQL?

You can set the MySQL Auto Increment Primary Key field via the following syntax: CREATE TABLE table_name ( column1 datatype NOT NULL AUTO_INCREMENT, column2 datatype [ NULL | NOT NULL ], ... );

Can a primary key not be auto increment?

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key.


2 Answers

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.

like image 186
Queue Avatar answered Oct 09 '22 22:10

Queue


Here is the simplest method to date:

  1. Make sure your file does NOT have a header line with the column names. If it does, remove it.
  2. In phpMyAdmin, as usual: go in the Import tab for your table and select your file. Select CSV as the format. Then -- and this is the important part -- in the

Format-Specific Options:

...in the Column names: fill in the name of the column the data is for, in your case "Name".

This will import the names and auto-increment the id column. You're done!

Tested fine with phpMyAdmin 4.2.7.1.

like image 26
Fabien Snauwaert Avatar answered Oct 09 '22 22:10

Fabien Snauwaert