importing data into an auto incremented table

[jondubya]

I'm guessing this is a fairly common request and it's so obvious that I can't find an answer on the web after half hour of searching.

My problem is this: I've already a table constructed with usernames and details, and the UserID field for this table autoincrements. I now have a list of users in excel format I wish to add. I've tried leaving the UserID column in my excel list blank in the hope it will autoincrement but it just fails.

Any pointers? TIA jondubya

 

[cyoung]

You don not need to include a UserID column in your Excel sheet in order for the field to auto-increment.

 

[jondubya]

That doesn't seem to work, I've removed the UserID column from the excel file and imported that file with one column short.

This only results in the next column being entered one column earier, this doesn't work as the next column is the name field.

I'm importing using Navicat but that shouldn't make any difference, it's the autoincrement value that's causing the problem. One way could be to remove the UserID field, enter the data and create a new UserID field, but I would then lose all previous references. Any ideas?

 

[guelphdad]

export the excel file as csv then use LOAD DATA INFILE to import it into mysql. You can specify the exact columns you are importing and properly ignore your first column, which would then update accordingly.

sorry I have no experience with navicat so can't tell you exactly what you would need to do the same thing.