Looping through a directory for sub-directories' names

Question

Looping through a directory for sub-directories' names

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

irfanshaukat999 (3/22/2011)
Hi
Many thakns for your reply:)
yes I can use xp_CmdShell or sp_OACreate.
No I am not restricted to T-SQL solution. Actually I dnt know much about SSIS. I did serach about it and explored; it is not installed on the mechine which I am using. If I insall SSIS through the SQL Server; will it require the system reboot?
Please guide me and suggest me any possible solution.
Thank You.

I actually don't know much about SSIS either because I've not yet needed to use it. I'll see what I can come up with when I get home from work.

--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 Moden SSC Guru Points: 1004363 More actions · Answer 2

I usually keep my ETL machines separate and isolated. They are not public facing and they're normally setup to only "pull" data from various sites/providers. Even then, I'll still have the System DBA setup a proxy for xp_CmdShell just to be safe about it.

Now that I've uttered that basic precaution, here's one very easy method to get a list of all text files from the current directory down. There is no level control here. It's either just the current directory or the current directory and all subdirectories. Here's the code...

--===== Conditionally drop the temp table to make reruns and troubleshooting easier

IF OBJECT_ID('tempdb..#PathFileList','U') IS NOT NULL

DROP TABLE #PathFileList

;

--===== Create a temp table to hold the Path/FileName combinations

CREATE TABLE #PathFileList

(

RowNum INT IDENTITY(1,1),

PathFileName VARCHAR(512)

)

;

--===== Get the Path/FileNames only for .txt file extensions in all directories

-- in an under the currently specified path of the DIR command.

-- /B is the "bare bones" format with no header or file info. Just names.

-- /S says read down for all sub-directories and add full path info to the

-- bare bones format.

-- /O is the sort order where "N" stands for name and "E" stands for extension.

-- Of course, we all know what "*.txt" means.

-- I used "C:\WINDOWS" as the path just for demonstration purposes because

-- everyone has that place and there's always more than one directory with

-- .txt files in it but doesn't take too long to run.

INSERT INTO #PathFileList (PathFileName)

EXEC Master.dbo.xp_CmdShell 'DIR C:\WINDOWS\*.txt /B/S/ONE'

;

--===== Lets see what we landed. Notice the last file name will be NULL.

SELECT RowNum, PathFileName

FROM #PathFileList

ORDER BY RowNum

;

A simple loop or bit of dynamic SQL will do the rest for you especially since the rows are conveniently numbered. Add an index if it makes you feel better.

If you're clever, you can split this list into multiple semi-permanent "real" tables and do multiple parallel loads.

--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)

irfanshaukat999 SSC Enthusiast Points: 123 More actions · Answer 3

Dear Jeff

I have sueccessfully loaded xml files in all sub folders of the main directory.

Next, I am using OPENXML to view the contents of the each XML file(by using sp_xml_preparedocument)

Now it is required to insert the contents also in tables, not to just view.

I have inserted the data in a table but there is the problem in the inserting the complete data. After the main/root tag there is a tag named 'Header' and below that there are other tags which are being inserted in tables without any problem; but when I try to insert the header data in tables that does problem and shows NULL in columns.

Moreover I am able to insert the just header data without any other tag data. As far as I think it is problem with the Insert statement used or the path used in the OPENXML.

Kindly Advice in this regard. please let me know if any further information is required.

Best Wishes