Import Excel sheet that is constantly updated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import Excel sheet that is constantly updated

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-10-18 : 16:44:52

I am trying to import a list of documents from an Excel spreadsheet that gets updated often. I have written so that it imports to a temp table and then inserts into the real table if it doesn't currently exist.

Problem 1:What would be the best way to keep the SQL table updated all the time? Should I schedule a job?

Problem 2: If you look at my code below, it says to import from [field1]. In the spreadsheet, I need to import everything from B5 to whenever. How would I references that in SQL?




CREATE TABLE #XlsImport
(
RefDoc_Title varchar (100),
Rev varchar (5)
)

INSERT INTO #XlsImport  ( [RefDoc_Title ], [Rev] )
	SELECT DISTINCT[RefDoc_Title ],[Rev]
	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
	'Excel 8.0;Database=//folder/folder2/folder3/mstrlist/mstrlist.xls',
	'SELECT [field1] FROM [Prodcedures]')
	GO
	
INSERT INTO tbl_RefDoc ([RefDoc_Title], [Rev])
	SELECT DISTINCT [RefDoc_Title], [Rev]
	FROM #XlsImport
	WHERE (NOT EXISTS (SELECT RefDoc_Title FROM tbl_RefDoc as RefDoc_Check WHERE RefDoc_Title = #XlsImport.RefDoc_Title))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 04:20:00

1. for that schedule a job to be executed as per frequency. you can call script inside job
2.are columns in excel static or will change often?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-10-19 : 11:12:28

quote:
Originally posted by visakh16

1. for that schedule a job to be executed as per frequency. you can call script inside job
2.are columns in excel static or will change often?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

1. Is there a script template to create a schedule job? I don't have SQL Server Agent, but the IT department does. They prefer if I send them scripts rather then have them manually create jobs.
2. For the 5 months I've worked here, I haven't seen the column change. It's always been from B5 and below.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:29:25

1. do you've a development server you can play with. if yes you can use wizard to create job and script it out.
if not you need to use sp_add_job
http://msdn.microsoft.com/en-us/library/ms182079.aspx
and sp_add_jobstep
http://msdn.microsoft.com/en-us/library/ms187358.aspx to add the jobs

2.ok. then why not you specify column information also inside OPENROWSET

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources