Using OPENROWSET to read a Text File? 2

[ctarr]

My dilemma is, I am attempting to use the OPENROWSET to read a text file. The text file is a directory listing of all folders that are contained in the folder "c:\somefolder".

Problem is the file doesn't have any column identifiers since it is just a DIR listing.

How do I correctly write this Query:
SELECT t.FileNumber, o.FileNumber
FROM test.dbo.FileListing AS t RIGHT OUTER JOIN
OpenRowset("???","Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=F:\somefolder\;&quot
AS o
ON t.FileNumber = o.???

Any ideas?? Or suggestions. I am staying away from Bulk Insert due to the permissions needed. And I can't pass in a delimited query parameter that contains the folder names because I would run over the limit of varchar(8000) (Each folder is 20 chars long).

Are there other avenues that I am missing?
Thanks for the help,

Craig

 

[ctarr]

Well, after some more investigating I have answered my questions.

First, the provider is MSDASQL, this SQL provider can not only open SQL and Access files but it can also open CSV and TXT files.

Secondly, the file that I am accessing defaults its first row to be the column headers. So, by using FSO to create the file and populate it on the fly. I can just put the column header in first then have FSO finish up.

If you have any comments or suggesions please let me know.

Craig ;-)

 

[1dba2b]

Hi,

If you are using SQL Server 2000, and have sufficient SQL admin permissions, you might want to investigate the usage of the system stored procedure "sp_readerrorlog". This stored proc is designed to read the log, but has additional parameters that will allow you to read ANY text file. You don't need the OLE-DB provider (Openrowset) to do it either.
You can also put all of this into a stored proc and using temp tables, etc., encapsulate the code for efficiencys sake.

Check this link out for a further information:

http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm

HTH.