SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import Excel Date Modified
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Fredforfree
Starting Member

Canada
49 Posts

Posted - 11/21/2012 :  07:34:48  Show Profile  Reply with Quote
Hi,

This is what I am using to import my excel file daily, is it possible to modify this so it will import the Person Catalog from this directory with the greatest date? Object is to not have to change the date each day before importing.

select * INTO XLImport FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=T:\Bedrock\AAS Calatogues\Person Catalog 11-14-2012.xlsx;HDR=YES', 'SELECT * FROM [adm_view_document_content$]')


Thanks

Fred

Fred

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  08:00:28  Show Profile  Reply with Quote
If the filename is predictable based on current day's date, construct the filename using date arithmetic. For example, if the filename has yesterday's date, it would be like this:
DECLARE @filename VARCHAR(255) = 'Person Catalog '+CONVERT(CHAR(10),DATEADD(dd,-1,GETDATE()),110);
SELECT @filename;


If the filename cannot be derived from current day's date, and instead, is just the latest file available in the folder, then construct the filename like this. My command shell skills are rather limited - I constructed this using something Lamprey posted a few days ago here; if this does not do exactly what you are looking for, refer to his post to see the logic I am using http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180630
DECLARE @Foo TABLE (id int identity(1,1), Val NVARCHAR(4000))

INSERT @Foo
EXEC xp_cmdshell N'dir T:\Bedrock\AAS Calatogues\*.xlsx /O-D /B'

DECLARE @filename VARCHAR(255);
SELECT TOP 1 @filename =  val FROM @foo order by id;
In either case, you may need to make your select query into dynamic SQL because openrowset may not let you use variables to construct the argument.

Edited by - sunitabeck on 11/21/2012 08:01:08
Go to Top of Page

Fredforfree
Starting Member

Canada
49 Posts

Posted - 11/21/2012 :  09:55:01  Show Profile  Reply with Quote
Hi Sunitabeck

I get the below error when the files are on a shared drive.

Error (2 row(s) affected)
The system cannot find the path specified.

Successful if files are on local drive.

(4 row(s) affected)
Person Catalogue 11-21-2012(2).xlsx

Any idea because it is a second party publishing the files to the 'dir T:\Bedrock\AAS Calatogues

Fred

Fred
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/21/2012 :  09:58:30  Show Profile  Reply with Quote
Try replacing T with its full url, something like \\Server1\Drive\Bedrock\ etc.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Fredforfree
Starting Member

Canada
49 Posts

Posted - 11/21/2012 :  10:30:21  Show Profile  Reply with Quote
Hi Jimf,

Same error, perhaps it is because I am logged in as local machine?

Fred

Fred
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  11:38:16  Show Profile  Reply with Quote
Are you able to do a dir from xp_cmdshell against any network drive at all?
Go to Top of Page

Fredforfree
Starting Member

Canada
49 Posts

Posted - 11/21/2012 :  12:02:13  Show Profile  Reply with Quote
No I get same error with any network drive, only works on c:
Fred
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  12:08:53  Show Profile  Reply with Quote
The error message you are seeing does not correlate with my theory here, but I would have suspected that it is a permissions issue - see if this article helps you at all. xp_cmdshell is not something that I have much experience with; so hopefully some of the experts on the forum will chime in:

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000