Import Excel Date Modified - SQL Server Forums

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 Date Modified

Author

Topic

Fredforfree
Starting Member

49 Posts

Posted - 2012-11-21 : 07:34:48

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 08:00:28

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.

Fredforfree
Starting Member

49 Posts

Posted - 2012-11-21 : 09:55:01

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 09:58:30

Try replacing T with its full url, something like \\Server1\Drive\Bedrock\ etc.

Jim

Everyday I learn something that somebody else already knew

Fredforfree
Starting Member

49 Posts

Posted - 2012-11-21 : 10:30:21

Hi Jimf,

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

Fred

Fred

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 11:38:16

Are you able to do a dir from xp_cmdshell against any network drive at all?

Fredforfree
Starting Member

49 Posts

Posted - 2012-11-21 : 12:02:13

No I get same error with any network drive, only works on c:
Fred

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 12:08:53

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources