CODEWITHSUNDEEP
importphpmyadmin
EmmyS
EmmyS

Reputation: 12138

how can I ignore id column when importing into mySQL via phpMyAdmin?

I need to export data from a table in database A, then import it into an identically-structured table in database B. This needs to be done via phpMyAdmin. Here's the problem: no matter what format I choose for the export (CSV or SQL) ALL columns (including the auto-incremented ID field) get exported. Because there's already data in the table in database B, I can't import the ID field with the new records - I need it to import the records and assign new auto-incremented values to the records. What settings do I need to use in either the export (to be able to choose which columns to export) or the import (to tell it to ignore the ID column in the file)?

Or should I just export as CSV, then open in Excel and delete the ID column? Is there a way to tell phpMyAdmin that it should generate new auto-incremented IDs for the records being imported, without it telling me that there's an incorrect column count in the import file?

EDIT: to clarify, I'm exporting only data, not structure.

Upvotes: 5

Views: 11634

Answers (3)

Gus
Gus

Reputation: 623

Another way is

  1. Go to the import menu for that table
  2. Add the CSV file (without an ID column)
  3. Pick CSV in the Format section
  4. In the section where you pick the format of the file (separated by which char, which char encloses fields, etc) there's a field called Column names Type the names of the columns you ARE including, separated by commas.

Upvotes: 3

R Jones
R Jones

Reputation: 71

It is several years since the original question, but this still came out top in a google search so I'll comment on what worked for me:

If I delete the Id column in my CSV and then try to import I get the 'Invalid column count in CSV input on line 1.' error.

But if I keep the Id column but change all of the Id values to NULL in excel (just typing NULL into the cell), then when I import this the id auto-increment fills in the new records with consecutive numbers (presumably starting with the highest existing record Id +1 ).

I'm using PHPMyAdmin 4.7.0

Upvotes: 6

Jason McCreary
Jason McCreary

Reputation: 73011

Excel is an option to remove the column and probably the fastest at this point.

But if these databases are on the same server and you have access you can just to an INSERT INTO databaseB.table (column_list) SELECT column_list FROM databaseA.table.

You can also run the SELECT statement to just get the desired columns and then export the results. This link should be available in the recent versions of PHPMyAdmin.

Upvotes: 8

Related Questions