Copy directory listing into a table

Question

Post reply

Copy directory listing into a table

ikf

SSC Enthusiast

Points: 113
More actions
February 22, 2007 at 1:04 pm

#177022

I need to store data about the files that come into my data warehouse from various interfaces. (I'm using SQL Server 2000). A directory listing looks something like this:
          1/20/2007   10:14:33 AM     5,824,739  DG31H7A_ZSDDJPV_20070219_20070219.B01
          1/20/2007   02:01:47 PM           7,103 GH005_CFD_20070219_20070219.B01
How do I capture and parse this into a table with the following columns?
          Date            (1/20/2007, 1/20/2007)
          Time            (10:14:33, 02:01:47)
          DayPart        (AM, PM)
          Size             (5,824,739, 7,103)
          SrcSys          (DG31H7A, GH005)
          SrcFile          (ZSDDJPV, CFD)
          FromDt          (20070219, 20070219)
          ToDt             (20070219, 20070219)
If you cannot help with parsing out the components of the file name, that's OK. I'll settle for a way to get the DIR listing into a table.
Thanks for any help you can give!
Irene

Viewing 9 posts - 1 through 8 (of 8 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Irene,

Try This:

Create

Table #MyTable(MyDir varchar(100))

insert

into #MyTable

execute

master.dbo.xp_cmdshell 'dir C:\'

select

* from #MyTable

Drop

Table #MyTable

After you get your directory in the database you can parse it as you say.

If you are more comfortable with the VBscript or Visual Basic you can use FileSystemObject to get details on your files and then use ADO or ADO.NET to get the details into the database.

Regards,Yelena Varsha

ikf SSC Enthusiast Points: 113 More actions · Answer 2

Thanks a lot, Yelena.

Now just one more step: it works for the C:\ drive, but I need F:\Archive_bds2. Where do I insert that?

Michael T SSC-Addicted Points: 479 More actions · Answer 3

change:

execute master.dbo.xp_cmdshell 'dir C:\'

to:

execute master.dbo.xp_cmdshell 'dir F:\Archive_bds2'

Michael T SSC-Addicted Points: 479 More actions · Answer 4

Also, I learned a lot about processing DOS directories from this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=96&messageid=7812

ikf SSC Enthusiast Points: 113 More actions · Answer 5

ikf

SSC Enthusiast

Points: 113

February 23, 2007 at 5:35 pm

#691592

Thanks, Michael. I'll check out that thread.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

Irene,

Just in case:

If your directory or folder name contains a space like in Program Files then you should take the path in double quotes like:

dir "c:\Program Files"

Regards,Yelena Varsha

ikf SSC Enthusiast Points: 113 More actions · Answer 7

Thanks again, Yelena.

What to I do when SQL Server is on serverABC, but the directory whose listing I want is on serverXYZ? I tried

execute master..xp_cmdshell 'dir \\serverXYZ\C:\'

but all I get is "Directory path not found".

-Irene

Michael T SSC-Addicted Points: 479 More actions · Answer 8

Hi Irene,

Do you have permissions from your SQL system for C on serverXYZ? You may need assign them and/or share C.

I use the CMD window to prototype my DOS commands before I use them in xp_cmdshell. It's a lot faster to it there then copy them to SQL when I have the command which gives the result I want. (I have also started with Windows Explorer to debug permissions between systems, then transferred down to DOS to get my syntax, and then to xp_cmdshell to implement it.)

Michael