Sunday, October 05, 2008

importing Excel to SQL2005

Such an annopyingly easy fix, just not too well documented.

I had 200 rows in an excel 2007 (this will work for most excel versions) worksheet I needed to insert into a sql table I was building.
You used to be able to use DTS in SQl 2000, but there doesn't seem to be any easy way to bring in this type of data.

First stop: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1381738&SiteID=1 with no luck, just odd SQL errors and I had no time to find the solution unitl...


This is where I found an answer that I could work with: http://www.mssqltips.com/tip.asp?tip=1430

Now this wont suit everybody as it wont work 100% of the time with tables that already have a Primary Key, I had to delete all other table columns except the title column I was after. Once the data is pasted in, I re-added the columns back in again.

I was lucky I had only just started using this table....its always annoying to have a linked tabel used in and around the database that you then have to go and wipe/re-fill with new rows.

cheers,
craig

No comments: