Need to import data into Sql Server Table from an excel File (using SQL commands)

• sandeep1188

  Old Hand

  Points: 389

  More actions

  May 3, 2011 at 9:22 am

  #249702

  I need to import data into Sql Server Table from an excel File without using SSIS OR Import and export wizard.Please suugest if there is a way to do this with a SQL Script.

  Thanks,

  Sandeep

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 3, 2011 at 9:27 am

  #1319165

  yes you can do it, but there are a few rules: the sheet has to have column names in the first row, i think.

  if your SQL2008 instance is 64 bit, you have to use ACE drivers that are 64 bit; the code below won't work, as the JET driver is 32 bit.

  try doing it with a linked server, like this:

  --#################################################################################################

  --Linked server Syntax for Excel

  --#################################################################################################

  DECLARE @server sysname,

  @srvproduct nvarchar(256),

  @provider nvarchar(256),

  @datasrc nvarchar(100),

  @location nvarchar(100),

  @provstr nvarchar(100),

  @catalog sysname,

  @sql varchar(1000)

  --add an excel spreadsheet as a linked server.

  SET @server = 'MyExcel'

  SET @srvproduct = 'Jet 4.0'

  SET @provider = 'Microsoft.Jet.OLEDB.4.0'

  set @provstr = 'Excel 5.0;'

  SET @datasrc ='C:\Clients\MyExcelDocument_APPROVAL.xls'

  EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

  EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL

  --you MUST know the name of the spreadsheet;

  --spreadsheet name has a dollar sign at the end of it!

  --I've personally never gor a spreadsheet that has a space in it

  --for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work

  --to work, so I end up editing the spreadsheet to remove spaces if that happens.

  --list all the spreadsheets and their names

  EXEC sp_tables_ex 'MyExcel'

  select * from MyExcel...Sheet1$;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 3, 2011 at 9:30 am

  #1319170

  openrowset

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  May 3, 2011 at 10:22 am

  #1319213

  Using OPENROWSET

  The example below has this capablity

  it reads data from Sheet1 starting at row 4 and only imports data form colums a thru c

  If the Excel sheet does not have a header row then HDR should be set as HDR=No

  SELECT *

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

  'Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=yes',

  'SELECT * FROM [Sheet1$a4:c]')

  If you are using EXCEL 2007 then

  SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

  'Excel 12.0;Database=d:\dasdgigs.xls', 'SELECT * FROM [dasdgigs$]')

  The above code can be modified as the first bit of code to read an Excel sheet with or without headers, and starting at a row other than 1 and reading from different columns.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• sandeep1188

  Old Hand

  Points: 389

  More actions

  May 3, 2011 at 1:02 pm

  #1319338

  Thanks Everyone for the quick Response , Is there a way to import data into Sql Server Table from an excel File use BCP Utility, Bulk Copy or Bulk Insert. Please let me know on this.

  Thanks,

  Sandeep

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 12, 2011 at 10:58 am

  #1323830

  sandeep1188 (5/3/2011)

  ---

  Thanks Everyone for the quick Response , Is there a way to import data into Sql Server Table from an excel File use BCP Utility, Bulk Copy or Bulk Insert. Please let me know on this.

  Thanks,

  Sandeep

  No...not if it's truly an Excel file. The utilities you mentioned are for importing text files (or SQL Serevr native files if you're nasty).

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato