April 5, 2019 at 2:18 pm
Following is potential code to get a list of files from the filesystem in order to process some CSV files as a part of another stored procedure. To be clear, the code DOES WORK within the scope of a stored procedure, but obviously the INSERT INTO... EXEC... @INSERTCmd is not allowed inside a function. Right now a slight variation of this code is in several stored procedures but in the interest of object-oriented coding I'd like to create a tabular function so this task that I need multiple times within our operation exists only once.
To save the question, "Why would you want to do this?"; I am in a factory environment that has multiple pieces of equipment that collect data about their operation and automatically export it to CSV files on a shared drive. We can't change how the data is exported from the equipment so a series of stored procedures go get a list of filenames from a given directory every 15 minutes then uses BULK INSERT on each file (via a WHILE loop) to insert data into a table.
So, again, I'm looking for suggestions on how to do that from within a function as tradtional Dynamic SQL EXEC statements don't work. Now, noted, I CAN execute 'EXEC master.dbo.xp_cmdshell @GETCmd' but that doesn't redirect the output into a table.
Code ------------
ALTER FUNCTION [dbo].[tf_GetFileList]
(
@FilePath varchar(500)
)
RETURNS @ParsedFileList TABLE
( DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT NULL,
ProcessStatus decimal(1,0)
)
AS
BEGIN
DECLARE @GETCmd varchar(500)
DECLARE @INSERTCmd varchar(max)
SET @GETCmd = 'dir "' + @FilePath + '\*.*"' + char(39)
SET @INSERTCmd = 'INSERT INTO @OriginalFileList(RawData)' + char(13) + 'EXEC ' + @GETCmd
----------Create Temporary Tables----------
DECLARE @OriginalFileList TABLE (RawData varchar(1000) NULL)
----------Get Filenames & Insert Into Build Table----------
INSERT INTO @OriginalFileList(RawData) EXEC master.dbo.xp_cmdshell @INSERTCmd
----------Delete NON-Filename Data Rows----------
DELETE FROM @OriginalFileList WHERE RawData IS NULL
DELETE FROM @OriginalFileList WHERE RawData LIKE '%Volume%'
DELETE FROM @OriginalFileList WHERE RawData LIKE '%Directory%'
DELETE FROM @OriginalFileList WHERE RawData LIKE '%<DIR>%'
DELETE FROM @OriginalFileList WHERE RawData LIKE '%bytes%'
----------Parse RawData Into Filename List----------
INSERT INTO @ParsedFileList (DateTimeStamp, FileSize, FileName1, ProcessStatus )
SELECT TOP 500 LTRIM(SUBSTRING (RawData, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (RawData, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (RawData, 40, 1000)) AS 'FileName1',
0
FROM @OriginalFileList
RETURN
END
April 11, 2019 at 2:09 pm
A piece of code I use to get the file names from a folder.
This can be used in a stored procedure, not in a function. Maybe this does help.
Ben
DECLARE @Folder varchar(300) = 'C:\CSV_IMP'
DECLARE @cmd varchar(5000)
CREATE TABLE Meta_Files_Tables(
WHICHPATH VARCHAR(300)
,WHICHFILE varchar(300)
,name varchar(300)
,extension varchar(300)
,table_name varchar(300)
,CSV_Table_name varchar(300)
,Prefix_field varchar(30)
,Table_alias varchar(300)
,action varchar(300)
,status varchar(300)
,delimitor char(1)
,textqualifier char(1)
,EOL_delimitor char(2)
,Headerline int
,Datatypeline int
,Datalines int
)
SET @cmd = 'dir ' + @folder +'\*.* /b'
INSERT INTO Meta_Files_Tables(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE Meta_Files_Tables SET WHICHPATH = @folder where WHICHPATH is null
SELECT * FROM Meta_Files_Tables
April 11, 2019 at 5:12 pm
You're going to need to use a stored procedure. I would argue that's modular and worth doing just as much as a function.
April 12, 2019 at 5:06 pm
As Ben had shown, you can use Insert...EXEC in the procedure to capture xp_cmdshell output into table. Then parse it in the function.
You can do file manipulation in the external scripts like PowerShell or cmd. The script would copy CSV files to predefined location with predefined names. Then load them in SQL sp, which can be called from script.
If you have $500.00 you can buy SQL# library. It has lots of file manipulation procedures and functions. Your task will become a one line code:
SELECT * FROM SQL#.File_GetDirectoryListing('C:\SQL#Test\', 0, '', '\.csv')--Vadim R.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply