Pass file name as variable

Question

Post reply

Pass file name as variable

rohanverma

Old Hand

Points: 351
More actions
August 31, 2008 at 3:15 pm

#189907

Hi,
Does anyone know how to pass a file name as a user defined variable in sql server 2005 query?
E.g.
Folder C:\Misc has file Sample_20080825.txt
In sql server 2005 query I have a user defined variable: @filename which should be set with the file name from above folder.
Set @filename = C:\Misc\Sample*.txt
or
Set @filename = Sample_20080825.txt
Does anyone know how i can accomplish this?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

What are you doing with the file when you have passed it into the stored proc?

rohanverma Old Hand Points: 351 More actions · Answer 2

Hi,

I am trying to load text files from C:\Misc to a table in sql server 2005 using openrowset using schema.ini file. I can do this by manually changing the file name, i am trying to identify an automated way of inserting the file from the folder.

Here is my original script:

SELECT *

FROM OPENROWSET(

'MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\misc;',

'SELECT *

FROM File1.txt')

I need to change the filename from "File1.txt" to dynamically populate based on the file that's in c:\misc folder.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

OpenRowset cannot take variables as part of it's syntax. You'll need to do the whole OpenRowset thing as dynamic SQL.

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

rohanverma Old Hand Points: 351 More actions · Answer 4

Yup, I got this from this forum:

declare @filename char(40);

set @filename = 'File1.txt'

declare @dir char(30);

set @dir = 'C:\Misc';

set @openrow1 = 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=' + @dir + ';';

set @openrow2 = 'select * from ' + @filename;

set @sqlstring = N'insert into #temp select * from OpenRowset(' + N'''' + N'MSDASQL' + N'''' + N', ' + '''' + @openrow1 + '''' + ', ' + '''' + @openrow2 + '''' + ');';

Here is there a way to populate @filename using the file that's in Misc folder?

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

rohanverma (9/1/2008)
Yup, I got this from this forum:
declare @filename char(40);
set @filename = 'File1.txt'
declare @dir char(30);
set @dir = 'C:\Misc';
set @openrow1 = 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=' + @dir + ';';
set @openrow2 = 'select * from ' + @filename;
set @sqlstring = N'insert into #temp select * from OpenRowset(' + N'''' + N'MSDASQL' + N'''' + N', ' + '''' + @openrow1 + '''' + ', ' + '''' + @openrow2 + '''' + ');';
Here is there a way to populate @filename using the file that's in Misc folder?

Ummm.... wrong question there... the code above allows the filename to be IMPORTED to be variable. It'll work for your "pass a parameter as a filename" problem.

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

rohanverma Old Hand Points: 351 More actions · Answer 6

Oops sorry for the confusion. My question is lets say if the file in C:\Misc changes from File1.txt to File8.txt, how can my code dynamically capture the file change and populate @filename with "File8.txt". Hope this helps.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 7

It looks from your initial few posts that the filename will actually be something like File20050801.txt, where you're incorporating the date. If that is true, and the date will always be a set date (yesterday/today/Monday/whatever, I'll use today for simplicity) you could use the script you found and just SET @filename = 'File'+convert(char(8),getDate(),112)+'.txt'

Then you'd always be pulling from the correct date-formatted filename.

But that may not be what you're trying to do. Does that help?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

GSquared SSC Guru Points: 260824 More actions · Answer 8

If xp_cmdshell is enabled on your server, you could use:

create table #Files (

FName varchar(1000))

insert into #Files (FName)

exec master..xp_cmdshell 'dir c:\Misc\*.txt /b'

That will give you a table with a list of the file names in that directory. You can then assign values to the variable from the table, or build a cursor on it, or whatever.

Will that do what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

rohanverma Old Hand Points: 351 More actions · Answer 9

Thank you so much for your suggestions. It worked. Here is what I did:

CREATE TABLE #temp(

col1 char(50) NOT NULL,

col2 bigint NOT NULL,

col3 char(100) NULL,

col4 char(100) NULL,

col5 char(3000) NULL,

col6 datetime NULL,

col7 char(100) NULL,

col8 char(100) NULL);

create table #Files (

FName varchar(1000))

insert into #Files (FName)

exec master..xp_cmdshell 'dir C:\Misc\*.txt /b'

select *, ROW_NUMBER() OVER (ORDER BY FName ASC) AS ROWID--row_number(#files) as rank

into #files1

from #files

where fname is not null;

update #files1

set Fname = replace(FName,'.','#')

from #files1;

declare @maxfileno int;

set @maxfileno = (select (max(Rowid) + 1) from #files1);

declare @fileno int;

set @fileno = 1;

WHILE (@fileno < @maxfileno)

BEGIN

declare @filename char(40);

set @filename = (select fname from #files1 where rowid = @fileno);

declare @sqlstring nvarchar(500);

set @sqlstring = N'insert into #temp select * from TxTSvr...' + @filename + ';';

exec sp_executesql @sqlstring;

set @fileno = @fileno + 1;

end

Now I need to figure out a way to find out how many columns are in a text file. All your suggestions have really helped me a lot. Thank you so much.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Once the text server (linked server) has been established, you don't need to make a trip to the CmdShell to get a listing of files in the directory....

EXEC dbo.sp_Tables_Ex TxtSvr

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