%~I - expands %I removing any surrounding quotes (")
%~fI - expands %I to a fully qualified path name
%~dI - expands %I to a drive letter only
%~pI - expands %I to a path only
%~nI - expands %I to a file name only
%~xI - expands %I to a file extension only
%~sI - expanded path contains short names only
%~aI - expands %I to file attributes of file
%~tI - expands %I to date/time of file
%~zI - expands %I to size of file
%~$PATH:I - searches the directories listed in the PATH
environment variable and expands %I to the
fully qualified name of the first one found.
If the environment variable name is not
defined or the file is not found by the
search, then this modifier expands to the
empty string
The modifiers can be combined to get compound results:
%~dpI - expands %I to a drive letter and path only
%~nxI - expands %I to a file name and extension only
%~fsI - expands %I to a full path name with short names only
%~dp$PATH:I - searches the directories listed in the PATH
environment variable for %I and expands to the
drive letter and path of the first one found.
%~ftzaI - expands %I to a DIR like output line
declare @tmp table (id int identity, results varchar(255),filename varchar(255),filesize int, processed datetime)
insert @tmp (results)
exec master..xp_cmdshell 'for /R "c:\ee" %i in (*.csv) do @echo %~fi""%~zi'
update @tmp set filename = substring(results,1,charindex('""',results)-1), filesize = substring(results,charindex('""',results)+2,10)
declare @filename varchar(255)
declare @tmp_cnt table (id int identity, filename varchar(255), rowcounter bigint, errors nvarchar(2048))
set @filename = (select top 1 filename from @tmp where processed is NULL and filesize > 0 order by id)
while isnull(@filename ,'') > ' .'
begin
update @tmp set processed = getdate() where filename = @filename
begin try
insert @tmp_cnt (filename , rowcounter)
exec('select '''+@filename+''' as filename,count(*) as rowcounter from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\ee; '',''select * from '+@filename+''')')
end try
begin catch
insert @tmp_cnt (filename, errors) values (@filename, error_message())
end catch
set @filename = isnull((select top 1 filename from @tmp where processed is NULL and filesize > 0 order by id),NULL)
end
select t.filename, t.filesize, t.processed, c.rowcounter, isnull(c.errors,'') as errors
from @tmp t
left outer join @tmp_cnt c on t.filename = c.filename
go