code to find the latest file in a folder and load data to a table
-
Hi Experts,
I have to find the latest file in a folder and export data to a table in sql server.
The code should be something that has to be incorporated into a t-sql stored procedure.
The file name would for example abc_defYYYYMMDD.xls.
would i be able to find the latest file in the folder using the the datestamp (YYYYMMDD) in the filename.
Please note i would have files in other format and names with datestamp attached to it, so the code has to pick specific file for which the file name starts with 'abc_def'
and export data to a table.
Any help would be highly appreciated
Alicia Rose
-
Hi Alicia...
Since you're trying to "export" the file from an .xls file to a table, you'll need to follow a couple of "rules" about the format of the .xls file. Take a look at the following URL to see a couple of methods for doing this to of "Import" to an SQL Server table...
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
Finding the latest file is actually a cake walk, especially the way you've named the files... if course, you'd replace the 'C:\Temp' with the proper UNC or path.
CREATE TABLE #MyFiles (Name SYSNAME, Depth TINYINT, IsFile TINYINT)INSERT INTO #MyFilesEXEC Master.dbo.xp_DirTree 'C:\Temp',1,1SELECT TOP 1 NameFROM #MyFilesWHERE Name LIKE 'abc_def[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].xls'AND ISDATE(SUBSTRING(Name,8,8))=1ORDER BY Name DescThere's a couple of other ways to do this same thing including a trip to a Cmd window, using a LinkedServer in the text mode, or writing a custom CLR (one of the only things I recommend writing a CLR for).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Excel files are like any other files in that they have datestamps. You can red these directly into SQL Server so as to be sure to pick up the latest Excel file. See some example code that uses the FileSystem Object (FSO),
http://www.simple-talk.com/content/article.aspx?article=374#forum
Best wishes,
Phil Factor -
Although using FSO through sp_OA is a wonderful idea... it still requires "SA" privs. Not complaining or arguing... just reminding
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
I seem to be plugging Robyn Page Articles today (I helped with these). This tells you how to do the process with sa, without letting it anywhere normal users, or causing any security concerns.
http://www.simple-talk.com/sql/t-sql-programming/process-delegation-workbench/
You'd only need to use this sort of technique where using sa became a problem. It works fine, but you will get up to a 20 second delay. Still, 20 secs for Alicia's excel file aint bad.
I must admit, once we've got this sort of mechanism set up for a database system, we put all the 'back-office' stuff in it. It is dead handy. Works with 2000 as well! We've used it with several websites.
Best wishes,
Phil Factor -
Good stuff, Phil... we had some critical stuff we needed to do as a batch job and, for sure, it required "SA" privs and xp_cmdShell...
I love my DBA... she said, "Jeff, if you can write it, I can run it". So, I built this wonderful import system that would dutifully import files and move them to a "completed" directory after successful import and provide all sorts of good information... of course, it used xp_cmdShell and MOVE/DELETE commands and all sorts of high security risk things.
She bottled it up in a job, set the user to run it as as "SA", and gave the night operators privs to execute the job. They can't see it run so it sends them an email to say "Running" and another to say "Job completed... here's the results".
I asked her how she did it and she said "Proxy User". How very simple...
Like you said, it's not instant, but it works just fine for batch jobs.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
LOL!
I think I love your DBA as well. Don't get to thinking they're all like that. Try and persuade her to write for SSC or ST!
Process-delegation solves all sorts of problems. Inherently Slow processes, processes that require a history, Processes that need to happen on an event, rather than user input. Processes that require god-like privileges.
Best wishes,
Phil Factor -
While we're on the topic of processes involving SA and xp_cmdshell, a quick, down and dirty way to load the contents of a directory (date ordered) is to use DIR /B /D. For example, to load your excel files...
exec master..xp_cmdshell 'DIR *.xls /B /D'
Just my .02
DAB
-
Try this... no "SA" privs required...
EXEC Master.dbo.xp_Dirtree 'C:\',1,1
... can be loaded into a table and filtered for use with something like BULK INSERT. Only advantage over what Doug posted is it does not require any special privs.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
We have an another way if you can use DTS.
Through ActiveXScript we can use FSO and find out the latest file from the folder and assign the valid excel file name into the Microsoft Excel Connection object.
It is easy if you are using FSO in Active Script and assigning the file to your Microsoft Connection Object.
-
I tried through linkedserver. Plz check it out .... :w00t:
SELECT
*
FROM
sys.sysservers
ORDER BY srvid DESC
EXEC master.dbo.sp_addlinkedserver
@server = N'ExcelSte12'
,@srvproduct=N'Excel'
,@provider=N'Microsoft.Jet.OLEDB.4.0'
,@datasrc=N'C:\Book1.xls'
,@provstr=N'Excel 8.0'
SELECT
*
FROM
ExcelSte12...[Sheet1$]
In this "Sheet1$" is the name of the ExcelSheet.
---
-
Drifting slightly off-topic here, but what about a TSQL routine that uses the xp_cmdShell 'DIR' trick to get an XML list of the files, with their full paths, including the subdirectories. I find this one very useful myself. I haven't added the bit that picks up the date of the file because this is specific to your international settings in Windows, and I've never thought of a foolproof way of doing it.
[font="Courier New"]SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetFilePaths]
@BaseDirectory VARCHAR(255),-- the initial directory e.g. 'c:\mypath'
@filespec VARCHAR(10) = '*.*',--the files you want
@wanted VARCHAR(10) = 'files',--files or directories
@subdirectories INT = 1,--do we want the subdirectories too
@xmlFileList XML OUTPUT-- we are essentially passing back a table
/*
e.g.
DECLARE @FileList XML
EXECUTE spGetFilePaths 'S:\work\programs\SQL\Templates\ssc', '*.sql',
@subdirectories = 1, @XMLFileList = @FileList OUTPUT
SELECT @filelist
*/
AS
SET NOCOUNT ON
DECLARE @DirectoryName VARCHAR(255)
DECLARE @Directory_ID INT
DECLARE @command VARCHAR(255)
DECLARE @directory TABLE
(
Directory_ID INT IDENTITY(1, 1),
directory VARCHAR(255),
done INT
)
DECLARE @line TABLE ( line VARCHAR(255) )
DECLARE @files TABLE
(
files_ID INT IDENTITY(1, 1),
thepath VARCHAR(255),
[Filename] VARCHAR(100),
[Path] VARCHAR(255)
)
INSERT INTO @directory ( directory, done )
SELECT @baseDirectory,
0
WHILE 1 = 1 --forever until a break!
BEGIN
DELETE FROM @line --clear out the work table
SELECT TOP 1
@Directory_ID = directory_ID,
@DirectoryName = directory
FROM @directory
WHERE done = 0
IF @@rOWCOUNT = 0 --if nothing there left to do
BREAK ;
SELECT @command = 'dir "' + @directoryname + '\' + @Filespec
+ '"'--build the DIR command
INSERT INTO @line--pop it into our work table
EXECUTE xp_cmdshell @command
IF NOT EXISTS ( SELECT line
FROM @line
WHERE line LIKE 'File Not Found' )
INSERT INTO @files--and into our file table
(
thePath,
[filename],
[path]
)
SELECT @directoryname + '\' + SUBSTRING(line, 40, 255),
SUBSTRING(line, 40, 255),
@DirectoryName
FROM @line
WHERE SUBSTRING(line, 25, 8) = ''
AND LEFT(line, 1) <> ' '
IF @Filespec NOT IN ( '*.*', '*.' )
BEGIN--get the directories
DELETE FROM @line
SELECT @command = 'dir "' + @directoryname + '"'
INSERT INTO @line
EXECUTE xp_cmdshell @command
END
INSERT INTO @directory ( directory, done )--and store them
--so we can process them in a later loop.
SELECT @directoryname + '\' + SUBSTRING(line, 40, 255),
0
FROM @line
WHERE SUBSTRING(line, 25, 8) = '<dir>'
AND SUBSTRING(line, 40, 255) NOT IN ( '..', '.' )
UPDATE @directory
SET done = -1
WHERE directory_ID = @directory_ID
IF @subdirectories = 0
BREAK
END
IF @wanted = 'files' --have we asked for the files
SET @xmlFileList = ( SELECT thePath,
[filename],
[path]
FROM @files
FOR
XML PATH('thefile'),
ROOT('thefiles'),
TYPE
)
ELSE --or just the directory structure
SET @xmlFileList = ( SELECT thepath = directory
FROM @directory
FOR
XML PATH('thefile'),
ROOT('thefiles'),
TYPE
)
[/font]
(prettified by the prettifier on http://extras.sqlservercentral.com/prettifier/prettifier.aspx)
Best wishes,
Phil Factor
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply