dbo.xp_cmdshell fill table according to filename

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

SQL Server 2005 Forums

Transact-SQL (2005)

dbo.xp_cmdshell fill table according to filename

Author

Topic

ph1long
Starting Member

16 Posts

Posted - 2012-10-17 : 13:45:07

Hi,

I have a table called reports with three columns: Warrants, Statment, and Property Tax.

I have files in a folder in this format:

AAA_2012-07_Stmt.pdf
AAA_2012-07_Tax.pdf
AAA_2012-07_Warr.xls

Depending on the filesnames', I would like to fill my sql table with the file names under the correct columns. For example,

Statements: AAA_2012-07_Stmt.pdf
Property Tax: AAA_2012-07_Tax.pdf
Warrants: AAA_2012-07_Warr.xls

Please help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-17 : 19:19:50

create a temp table, insert the filenames into that table using xp_cmdshell (do you need to use xp_cmdshell necessarily?) and then process the table to populate your actual table. Here is some sample code for doing it.

CREATE TABLE #tmp(filenames VARCHAR(255));

INSERT INTO #tmp
EXEC xp_cmdshell 'dir C:\temp /b'

;WITH cte AS
(
	SELECT
		CASE 
			WHEN files LIKE '%stmt.pdf' THEN 'stmt'
			WHEN files LIKE '%tax.pdf' THEN 'tax'
			WHEN files LIKE '%warr.xls' THEN 'warr'
		END
		AS FileType,
		REPLACE(REPLACE(REPLACE(files,'stmt.pdf',''),'tax.pdf',''),'warr.xls','') AS FILENAME,
		files
	FROM
		#tmp
)
INSERT INTO YourTable
([Statement],[Property Tax],[Warrants])
SELECT
	stmt, tax, warr
FROM
	cte
PIVOT(MAX(files) FOR filetype IN ([stmt],[tax],[warr]))p

ph1long
Starting Member

16 Posts

Posted - 2012-10-18 : 16:30:37

Hi,

I get an error running the code.

"Incorrect syntax near '('., whih refer to the syntax after "Pivot".

drop table #temp
CREATE TABLE #temp(

[Warrants] [nvarchar](50) NULL,
[Statement] [nvarchar](50) NULL,
[Property Tax] [nvarchar](50) NULL
) ON [PRIMARY]
go

INSERT INTO #temp
EXEC xp_cmdshell 'dir C:\Data /b'

;WITH cte AS
(
SELECT
CASE
WHEN files LIKE '%stmt.pdf' THEN 'stmt'
WHEN files LIKE '%tax.pdf' THEN 'tax'
WHEN files LIKE '%warr.xls' THEN 'warr'
END
AS FileType,
REPLACE(REPLACE(REPLACE(files,'stmt.pdf',''),'tax.pdf',''),'warr.xls','') AS FILENAME,
files
FROM
#temp
)
INSERT INTO Reports
([Statement],[Property Tax],[Warrants])
SELECT
stmt, tax, warr
FROM
cte
PIVOT(MAX(files) FOR filetype IN ([stmt],[tax],[warr]))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 16:38:38

are you using SQL 2005 or above? whats the compatibility level?

run below and post the result


SELECT @@VERSION
GO

EXEC sp_dbcmptlevel 'your database name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ph1long
Starting Member

16 Posts

Posted - 2012-10-18 : 16:42:04

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 16:43:48

quote:
Originally posted by ph1long

Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

you didnt run full statement i guess. where's result for compatiubility level?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ph1long
Starting Member

16 Posts

Posted - 2012-10-18 : 16:46:33

The current compatibility level is 90.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 21:43:41

then it shouldnt be a problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources