Alas SQL Server Management Studio isn’t as friendly as it could be for pasting in data. You’d think Microsoft would have this humming, but when I tried to paste from Excel, it attempted to paste the entire first row from my spreadsheet into the first column (in one row) of the database.
Using MS Access to open up the database probably would have worked, but I didn’t have it on that machine.
Trying to import using the SQL Server Import And Export Data wizard from a CSV text file worked for a small amount of data, but the 80,000 rows I was trying to import from the world ports code list didn’t. Time and time again it would report an error (unspecified) and give me the option of Abort, Retry, Ignore. No matter option I chose, it crashed.
While the 64-bit version of the wizard on my 64-bit Win7 machine didn’t allow you to import from Excel/Access, the 32-bit version did (presumably because MS Office, at least the version I have installed, is 32-bit).
The next problem was that it only supported Excel 2003 format, which can’t handle more than 64K rows. I ended up having to split the data into two and import the two spreadsheets separately. Then it worked.
Shame the wizard is so flaky, and of course it’s a big shame that Management Studio doesn’t do copy/paste like one would expect. (Maybe that too was a 32-bit/64-bit issue.)
Which version of management studio are you using? SQL Server 2008 definitely imports from excel 2007 (XLSX)
2008 R2, x64, from Excel 2010. Like I say, I couldn’t get it to work via Copy/Paste, and the Import wizard didn’t like it either.
wierd, im using essentially the same set up (x64 08 rs) and there is a option for xlsx import on the wizard.
You can get the Import wizard to import XLSX files, but it is not intuitive. It involves setting an OLE-DB setting, I think. I have the details printed out, as I have to do it on a semi-regular basis when I get given spreadsheets with 100k+ rows to import. Oh the joy….