I have some bulk data in a text file that I need to import into a MySQL table. The table consists of two fields ..
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.
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.
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.
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 ], ... );
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.
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.
Here is the simplest method to date:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With