CODEWITHSUNDEEP
mysqlsqlexcelphpmyadmin
user1020069
user1020069

Reputation: 1570

Importing from an excel sheet into a database table

I am attempting to insert data into my db table from a MS Excel 2007 sheet using the Import function. The challenge here is that my db table has it's primary key auto_incremented. This basically means that in the excel sheet, there for practical reasons a column cannot exist that would act as the primary key data since that would make it tedious for the editors to manually keep inputting data [the excel sheet for the table can be added multiple times hence keeping a tab of the last number in the primary key column isn't exactly a great idea].

This is wherein the challenge lies.The import function refuses to insert the information into the specific table, but rather creates a new table in the database and enters the information there. To counter this, a second strategy would be to have a trigger or stored procedure that carries out a periodic [I am not concerned about the semantics of when/how this would be executed] : INSERT INTO requiredDbColumn SELECT *
FROM excelSheetTable

However, this won't really work, since excelSheetTable and requiredDbColumn have a values/column mismatch as excelSheetTable only has the (n-1) number of columns that were created from the excel sheet that was uploaded vs requiredDbColumn that has n number of columns, the additional column being the primary key column.

I have set the primary key to be on auto increment, so I am not sure why INSERT INTO, SELECT states a column count/value count mismatch.

This is the general challenge, anybody have a solution for this?

Thanks!

Upvotes: 2

Views: 1145

Answers (1)

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

When I do an INSERT, I typically give "values" to all the fields for the newly inserted row. My Insert also auto-increments a primary key field automatically, too.

Here's an excerpt from one of my INSERT commands:

INSERT INTO Security.Memberships VALUES (185790, '1461459','asdf',NULL,NULL,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

Perhaps you need a more fully constructed INSERT command?

Upvotes: 0

Related Questions