Importing Excel to SQL table

• iitt2007

  SSC Veteran

  Points: 286

  More actions

  April 23, 2008 at 1:44 pm

  #185766

  Scenario :

  SQL table has 2 columns SQ1, SQ2

  Excel sheet has three columns : EX1, EX2, EX3

  SQ1 matches with EX2

  SQ2 matches with EX3

  I would like to insert the rows where EX1='X'. EX1 is the left most column in th Excel sheet.

  Here's what I am trying. Could you suggest any changes/suggestions?

  -- Link server logic

  IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Config_spreadsheet_load')EXEC master.dbo.sp_dropserver @server=N'Config_spreadsheet_load', @droplogins='droplogins'

  GO

  --This is the link logic to connect the spreadsheet to the database

  sys.sp_addlinkedserver 'Config_spreadsheet_load',

  Excel',

  'Microsoft.Jet.OLEDB.4.0',

  In : @ExcelfileName,

  'excel 8.0;

  IMEX=1;

  GO

  -- Insert Plan Codes, for the column (PLC_NEW_COL) marked as ‘X’

  insert into Plan_code (SQ1,

  SQ2)

  -- **** How can I have the logic for checking EX1='X'

  where PLC_NEW_COL like ‘X’

  -- Once the data has been created, clean-up

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  April 24, 2008 at 9:30 am

  #806678

  Hi,

  I think this should help....

  DECLARE@xlsFile VARCHAR(255)

  DECLARE@xlsSheet VARCHAR(255)

  DECLARE@importServer VARCHAR(10)

  DECLARE@importDatabase VARCHAR(50)

  DECLARE@importTable VARCHAR(50)

  DECLARE @rc INT

  DECLARE @adodbObject INT

  DECLARE @connString VARCHAR(1000)

  DECLARE @sqlString NVARCHAR(2000)

  DECLARE @dummyOut INT

  -- Connection Settings

  SELECT @xlsFile = 'c:\myfile'

  ,@xlsSheet = 'mySheetName$'

  ,@importServer = 'myServer'

  ,@importDatabase = 'myDB'

  ,@importTable = 'myTable'

  -- Create ADODB connection

  EXEC @rc = master.dbo.sp_OACreate 'ADODB.Connection', @adodbObject OUTPUT

  -- Set connection string to point to file

  SET @connString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @xlsFile + ';Extended Properties="Excel 8.0;HDR=NO;IMEX=1"'

  PRINT @connString

  EXEC @rc = master.dbo.sp_OAMethod @adodbObject, 'Open', NULL, @connString

  -- Query To Import Data

  SET @sqlString = 'INSERT INTO ' + @importTable + ' SELECT EX2, EX3 FROM [odbc;Driver={SQL Server};Server=' + @importServer + ';Database=' + @importDatabase + ';Trusted_Connection=true].[' + @importTable + '] FROM [' + @xlsSheet + '] WHERE ex1=''X'''

  -- Log and Execute Dynamic SQL

  PRINT @sqlString

  EXEC @rc = master.dbo.sp_OAMethod @adodbObject, 'Execute', @dummyOut OUT, @sqlString

  HTH,

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]