Importing From Excel Not Using DTS

Question

Importing From Excel Not Using DTS

dec_obrien

Default port

Points: 1486
More actions
November 7, 2005 at 2:18 am

#166137

Hi,
I'm using SQL Server 2000 through Visual Studio (therefore no enterprise manager or DTS etc). I have a table, Table 1 with fields Field1 and Field2, both varchar. I also have a spreadsheet with two columns titled Field1 and Field2. How do I import the data from the spreadsheet to the table using T-SQL? I don't know whether or not it makes a difference but the database is on a different machine to mine.
I thought this would be a common enough scenario but I can't seem to find the answer. Any help greatly appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

The question is: manual or automagical? Onetime or scheduled?

For manual singleton jobs (I do this pretty often) I just save the Excel sheet as a skv file and bcp it into my table. Quick and painless.

If you instead want to read the Excelsheet straight off, you can do that by defining a linked server that points to the Excel file. While possible, if you don't have the proper tools to set it up (ie EM for managing linked servers) it's a bit involved to set up.

/Kenneth

dec_obrien Default port Points: 1486 More actions · Answer 2

Hi Kenneth,

It's a one-off so could you give me a bit more detail on how I would do Option No 1.

Thanks!

Dec.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Well, it's not very complicated at all. For each sheet, just choose 'save as' and choose a suitable format (personal pref is semicolon-separated - in office 2003 that's the .csv format)

Don't forget to make sure that the sheet must be in a tabular format in order to load cleanly.

Make a loading table that matches the columns for yor sheet, then use bcp to load the file into that table.

Done.

/Kenneth

dec_obrien Default port Points: 1486 More actions · Answer 4

Hi Kenneth,

Sorry for all of the silly questions, but how do I run bcp? (I tried from command line and "not recognised" both in VS command line and Dos) I don't have SQL Server installed on my PC. I connect to a remote server. If bcp isn't available is there a way to import using T-SQL using a stored proc?

Thanks again.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

You could try using openrowset like this

SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;HDR=YES;Database=C:\temp\book1.xls', [sheet1$])

but I have had problems using this if the spreadsheet does not reside on the sql server

Far away is close at hand in the images of elsewhere.
Anon.

dec_obrien Default port Points: 1486 More actions · Answer 6

Hi,

Yes, me too. The server is on a different machine.

Any other ideas?

Anton Pieterse Newbie Points: 9 More actions · Answer 7

If you are developer then why not try and write some code in the background of the excel file with a connection to the SQL box using ODBC?

Aren't u people suppose to be asleep in the states?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Yes, if you don't have bcp.exe (which is a commandline exe) on your machine, and you cant remote exec it from the server (where I'm sure it is to be found), you could have a look at the Transact SQL command BULK INSERT. It does what bcp does (ie loads a file) but is a T-SQL command.

BOL has more info in BULK INSERT

/Kenneth

dec_obrien Default port Points: 1486 More actions · Answer 9

Hi,

I haven't tried it yet but the Help for BULK COPY looks like it will work. The file doesn't need to be on the same server as SQL Server: you specify the path as UNC and it should work.

I'll post my results when I try it.

Thanks for the help.