how to import data from Excel to table

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  January 11, 2010 at 1:44 am

  #96144

  This is my Ftp Route \\190.0.00.001 Daily one Excel will be save at this place with abc+11012010.xls the next day it will be abc+12012010.xls goes on.. i need this datas to move to my table on daily basis data fileds are same formate no change in formate but the name will be changing daily

  how to do this in SQL 2005

  Thanks

  Parthi

  Thanks
  Parthi

• Azzu

  Ten Centuries

  Points: 1363

  More actions

  January 11, 2010 at 2:21 am

  #1101657

  Hi

  You can create a procedure and then create a job on that procedure. You can use the OPENROWSET for this purpose in the procedure.

  Assuming that the excel file will always be the form of abc_11012010.xls.

  Procedure:

  CREATE PROCEDURE PR_ImportXLSData

  AS

  BEGIN

  Declare @sql nvarchar(1000)

  Select @sql =

  ' INSERT INTO dbo.TblA SELECT ColumnA, ColumnB FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',

  ''Excel 8.0;Database=\\PATH\DOC_'

  + CAST(DATEPART(DD,Getdate()) AS VARCHAR(2)) + CASE WHEN LEN(DATEPART(MM,Getdate())) > 1 THEN CAST(DATEPART(MM,Getdate()) AS VARCHAR(2)) ELSE '0'+CAST(DATEPART(MM,Getdate()) AS VARCHAR(2)) END + CAST(DATEPART(YYYY,Getdate()) AS VARCHAR(4)) + '.xls'', [Sheet1$])'

  exec(@SQL)

  END

  Hope this helps.

  Thanks.

  Azeem

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  January 11, 2010 at 11:10 pm

  #1102191

  I am getting this error when i run with my path details

  Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

  Thanks
  Parthi

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  January 22, 2010 at 3:24 am

  #1107204

  Consider using SSIS for this. With the Foreach Loop Container you can process all files (specified) in a source folder, extract data from them, and then move them to an archive folder (to prevent them from being re-processed).

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com