How to import text file to table with primary key

问题:

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.

回答1:

Not correct on import with the LOADTABLE INFILE, just create the auto-increment column as the LAST column/field... As its parsing, if your table is defined with 30 columns, but the text file only has 1 (or anything less), it will import the leading columns first, in direct sequence, so ensure your delimited with... is correct between fields (for any future imports). Again, put the auto-increment AFTER the number of columns you know are being imported.

create table YourMySQLTable
(  FullName varchar(30) not null ,
   SomeOtherFlds varchar(20) not null,
   IDKey int not null AUTO_INCREMENT,
   Primary KEY (IDKey)
);

Notice the IDKey is auto-increment in the last field of the table... regardless of your INPUT stream text file which may have less columns than your final table will actually hold.

Then, import the data via...

LOAD DATA
    INFILE `C:\SomePath\WhereTextFileIs\ActualFile.txt`
    INTO TABLE YourMySQLTable
    COLUMNS TERMINATED BY `","`  
    LINES TERMINATED BY `\r\n` ;

Above example is based on comma seperated list with quotes around each field such as "myfield1","anotherField","LastField". Also, the terminated is the cr/lf that typical text files are delimited per row

In the sample of your text file having the full name as the single column, all the data would get loaded into the "YourMySQLTable" into the FullName column. Since the IDKey is at the END of the list, it will still be auto-increment assigned values from 1-? and not have any conflict with the columns from the inbound text.

回答2:

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.

回答3:

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.

回答4:

I just used a TAB as the first field in my text file, then imported it as usual. I got a warning about the ID field but the field incremented as expected...

回答5:

I just tried this:

1. In phpMyAdmin table- match the amount of fields you have in your csv.
2. Perform the import of csv data into your table
3. Go to the [Structure] tab and add a new field [At beginning of table] (I assume you want the id field there)
4. Fill in the [name] attribute as "id",
5. [length] to "5"
6. [Index] to "Primary"
7. Tick the A_I (Auto Increment)
8. Hit [Go] button
9. The table should have updated with the id field at the front of all your data with auto-incrementing.

At least this way you don't have to worry about matching fields, etc.

回答6:

I´ve solved that problem by simply add the column_names under Format-Specific Options without the Column ID. Because the Column ID ist Auto increment. In my case it works fine without changing anything in the CSV File. My CSV File has only Data inside no Column Headers.

回答7:

If the table columns do not match, I usually add "bogus" fields with empty data where the real data would've been, so, if my table needs "id", "name", "surname", "address", "email" and I have "id", "name", "surname", I change my CSV file to have "id", "name", "surname", "address", "email" but leave the fields that I do not have data for blank.

This results in a CSV file looking like this:

1,John,Doe,,
2,Jane,Doe,,

I find it simpler than the other methods.