Creating a View from a Windows file Directory

Question

Post reply

Creating a View from a Windows file Directory

kfschaefer

Right there with Babe

Points: 788
More actions
November 11, 2004 at 12:57 pm

#361611

Is is is possible to create a view of a specific File Directory.

I have a file directory containing numerous txt files that I will need to access and update a table in SQL - comparing file name and last date modified.

Any suggestions, will be greatly appreciated.

Karen

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

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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Hi Karen

Not sure if this can be achieved directly through a view.

Here's an example of how you can get file info into SQL Server though:

declare @SQLText char(21), @app char(1)

set @app = ''''

set @SQLText = 'xp_cmdshell '+@app+'dir c:\'+@app

create table #output (OutputData varchar (256))

insert into #output exec (@SQLText)

select * from #output

drop table #output

This returns a record for every line returned by the dir command - in a single field. It is therefore not in a good format for automated analysis - but refining the actual DOS command used (or writing something (eg .exe, .bat file) specifically to help you achieve what you're trying to do) may help here.

Obviously, to ensure that the data remains up to date, you could schedule a SQL Server job to run periodically and update the results.

Regards

Phil

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 2

Karen, we take Phil's solution one step further: we've created a vbs script, below, that we then exeute using xp_cmdshell.

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

Dim oConn

set oConn = CreateObject("ADODB.Connection")

Dim orst

set orst = CreateObject("ADODB.Recordset")

Dim appFiles , curFile, appFolder , strfilename , varfilesize

dim sql

oConn.Provider="sqloledb"

oConn.ConnectionString="Server=<SERVER NAME>;Database=<DB NAME>;Trusted_Connection=yes"

oConn.CommandTimeout=0

oConn.Open

Set appFolder = fso.GetFolder("<FOLDER>")

Set appFiles = appFolder.Files

For Each curFile In appFiles

strfilename = curFile.Name

varfilesize = curFile.size

filedate = curFile.date

msgbox("file info: " & strfilename & " , size: " & varfilesize)

sql = "INSERT OR UPDATE STATEMENT"

orst.Open sql, oConn

dab

kfschaefer Right there with Babe Points: 788 More actions · Answer 3

Thanks for the assistances, however, I have a problem with my connection string - could you please take a look and make a suggestions

I am getting a Login Password error - When I modified the syntax to include password - it didn't like that either. I also get invalid string error when I try to include the login/pwd.

Your assistances is greatly appreciated.

Karen

Public Function CADFileLinks()

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

Dim oConn

Set oConn = CreateObject("ADODB.Connection")

Dim orst

Set orst = CreateObject("ADODB.Recordset")

Dim appFiles, curFile, appFolder, strfilename, varfilesize

Dim sql

oConn.Provider = "sqloledb"

oConn.ConnectionString = "Server=KSCHAEFE507437;Database=CADFileDir;Trusted_Connection=yes"

oConn.CommandTimeout = 0

Debug.Print oConn

oConn.Open

Set appFolder = fso.GetFolder("C:\Databases\Store Dev Planning\Development\SD_Forecast_OrderVis\Txtfiles")

Set appFiles = appFolder.Files

For Each curFile In appFiles

strfilename = curFile.Name

varfilesize = curFile.Size

filedate = curFile.Date

MsgBox ("file info: " & strfilename & " , size: " & varfilesize)

sql = "INSERT INTO tblCADFileDir ( FileName, DateModified )" & _

"SELECT " & strfilename & ", " & filedate & " " & _

"FROM " & appFiles & ""

orst.Open sql, oConn

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 4

Scalability Doug

SSCertifiable

Points: 5351

November 12, 2004 at 1:37 pm

#530007

Karen, is your SQL Server configured to use SQL Authentication or Mixed Mode? If it is Mixed Mode, then what account are you executing xp_cmdshell from? If it is not Mixed Mode, Trusted_Connection=yes will fail so try this string:

oConn.ConnectionString="Server=<SERVERNAME>;Database=<DB NAME>;uid=<USER ID>;pwd=<PASSWORD>"

kfschaefer Right there with Babe Points: 788 More actions · Answer 5

I am still having problems with my connection string- Please note the the server I am attempting to connect with is not a SQL Server - it is a connection to the server storing the txt files I am trying to access.(Jet 4.0 Microsoft.Jet.OLEDB.4.0&nbsp

The following is a snapshot of the syssrver file from mys sql Master database.

2 1184 CADFileDir Jet 4.0 Microsoft.Jet.OLEDB.4.0 C:\Databases\Store Dev Planning\Development\SD_Forecast_OrderVis\Txtfiles Text 11/12/2004 10:15:36 AM 0 0 1 0 0 0 0 0 0 1 0 0 1 0

could you please rebuild my connection string accordingly - I keep getting the not trusted error.

Note that I have the security set to us the WindowsNt login and password.

My overall goal is to create a table that contains the directory data for all my txt files and then compare the file names to the list in my CADFile - if new add, else if the date modified is newer then open the txt file compare item numbers is unmatched then update the CADFile data.

If you have any other suggestions on how to accomplish this task, please feel free to make your suggestions.

Someone made a suggestions to use a DOS command line to create the txtdirectory file. Unfortunately, I am unsure on how to implemented it using a SQL Server - nightly scheduled file - Is it possible to use a DOS command while running Sql Server?

thanks again,

Karen

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

Karen,

In the Job properties in Enterprise Manager, Step Tab, there is a list of Job Steps. Double-click on the particular step. This is a window called Edit Job Step. Go to General Tab. Click on the dropdown Type. There is a choice of job types: T-SQL, Operating System (MS-DOS) or Activate Script. Since T-SQL starts with T and there are a lot of Replication types and they are arranged alphabetically you have to scroll up to see ActiveX and DOS types. You can use ActiveX Script type and just enter VBscript code without any xp_cmdshell, it does the same thing. Or you may enter MS-DOS command when the type is Operating System Command

When you edit your job you can also schedule it on a Schedules tab

Yelena

Regards,Yelena Varsha

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

In addition to my pervious post: the easy way without connection strings

Create a file with the extension .vbs like MyDir.vbs Right-click it and click Edit. Paste the following script. Modify the name of the folder from C:\ to what you need in the line Set objFolder = fso.getFolder("C:\"). Save. Run by double-clicking or by scheduling it. It will create TXTfilelist.txt with the list of your txt files in the folder where the script runs.

Then use Export/Import wizard to import data from this file to a some new table (MYTable) fro example) on your server. You may schedule this job right from the wizard. It took me less the 5 min to set everything up. Then just run Select * from MyTable

Yelena

Here is the script:

' Creating FileSystemObject

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim objFolder

Dim objFileLoop

Dim TXTfilelist

On Error Resume Next

' Creating the output file and getting TXT files names and writing to the file

Set objFolder = fso.getFolder("C:\")

Set TXTfilelist = fso.CreateTextFile("TXTfilelist.txt", True)

For each objFileLoop In objFolder.Files

If UCase(Right(objFileLoop.name,4)) = ".TXT" then

TXTfilelist.WriteLine(objFileLoop.name)

End If

SET TXTfilelist = Nothing

SET FSO = Nothing

Regards,Yelena Varsha