Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export to sequential folders

Status
Not open for further replies.

jeffwest21

IS-IT--Management
Apr 4, 2013
60
GB
I have to create an export everyday for a client, I then need to upload that data onto a sftp.

The issue I have is the client want the folder to be named Run001, Run002 etc

I know how to create a dated folder, but how do I create a folder with a sequential number in the format above??

Any help with this would be greatfully appreciated.

'Clever boy...'
 
it could be done in several ways... Show us your current code for the dates and prehaps we can incorperate the sequential numbers in the process.

Simi
 
This what I have that creates a folder called 20140918

Code:
Declare @sql varchar(8000)
Declare @Createsql varchar(8000)
Declare @file varchar(250)
Declare @Dir varchar (100)
Declare @MkDir varchar (100)
Declare @fileunique varchar(100)

set @Dir = '\\bridata\Master Drive\CCP\Rostrvm\Output\Park_Test\UK\Completed\'
set @fileunique =Convert(varchar(100), Year(GETDATE()))
set @fileunique = @fileunique + case WHEN datepart(month,getdate())  < 10  THEN '0' +cast(datepart(month,getdate()) as varchar(2)) else cast(datepart(month,getdate()) as varchar(2))END
set @fileunique = @fileunique + case WHEN datepart(day,getdate())  < 10  THEN '0' +cast(datepart(day,getdate()) as varchar(2)) else cast(datepart(day,getdate()) as varchar(2))END 

set @MkDir = ''+@Dir+@fileunique+''
set  @Createsql = 'mkdir '+'"'+@MkDir+'"'
exec xp_cmdshell @Createsql

As I say, what I really need is Run001, Run002 etc

'Clever boy...'
 
The simplest way would be to create a table with a date and an int field. Check the date and if the date is not equel to today update it with the current date and update the number to 1.

If it is the same date use that number for your folder name then update the int in the table to be number +1.

Simi

 
Simi

Thanks, had thought about that as well, so may just give that a go.

'Clever boy...'
 
There is an undocumented procedure called xp_dirtree that you may find useful.

Try this:

Code:
Declare @Dir varchar (100)
set @Dir = '\\bridata\Master Drive\CCP\Rostrvm\Output\Park_Test\UK\Completed\'
exec xp_dirtree @Dir, 0, 0

This should list all of the subfolders in the directory. If this works for you, you can insert/exec this data in to a temp table and then find the last folder. Increment by 1, and use it to create the new folder.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Thanks for the help, managed to do it this way in the end which works fine

This is the first piece of code in the first SP that runs.
It creates the folder if it doesn't already exits.

Code:
DECLARE @RunNumberTest int
	--set @Dir = '\\jaguar\share$\Database Services\BrighthouseExports\'
	SET @Dir = '\\bridata\Master Drive\CCP\Rostrvm\Output\Park_Test\UK\Completed\'

		--Create a new Run number folder

    SELECT  @RunNumberTest = (SELECT Run_Number FROM Run_UK)
    IF @RunNumberTest <= '9'

	SET @fileunique = (Select RIGHT('Run00' + CONVERT(VARCHAR(6),Run_Number), 6) as [Run_Number] from Run_UK)

SET @MkDir = ''+@Dir+@fileunique+''
SET  @Createsql = 'mkdir '+'"'+@MkDir+'"'
EXEC xp_cmdshell @Createsql

    IF @RunNumberTest >= '10' and @RunNumberTest <= '99' 

	SET @fileunique = (Select RIGHT('Run0' + CONVERT(VARCHAR(6),Run_Number), 6) as [Run_Number] from Run_UK)

SET @MkDir = ''+@Dir+@fileunique+''
SET  @Createsql = 'mkdir '+'"'+@MkDir+'"'
EXEC xp_cmdshell @Createsql

    IF @RunNumberTest > '99'

	SET @fileunique = (Select RIGHT('Run' + CONVERT(VARCHAR(6),Run_Number), 6) as [Run_Number] from Run_UK)

SET @MkDir = ''+@Dir+@fileunique+''
SET  @Createsql = 'mkdir '+'"'+@MkDir+'"'
EXEC xp_cmdshell @Createsql

This is in the second SP
This populates the folder.

Code:
		--Use Run Number Folder

    SELECT  @RunNumberTest = (SELECT Run_Number FROM Run_UK)
    IF @RunNumberTest <= '9'

	SET @fileunique = (Select RIGHT('Run00' + CONVERT(VARCHAR(6),Run_Number), 6) as [Run_Number] from Run_UK)

	SET @MkDir = ''+@Dir+@fileunique+''

	UPDATE Run_UK
	SET Run_number = @RunNumberTest+1

    IF @RunNumberTest >= '10' and @RunNumberTest <= '99'

	SET @fileunique = (Select RIGHT('Run0' + CONVERT(VARCHAR(6),Run_Number), 6) as [Run_Number] from Run_UK)

	SET @MkDir = ''+@Dir+@fileunique+''

	UPDATE Run_UK
	SET Run_number = @RunNumberTest+1,Date = Getdate()

	IF @RunNumberTest > '99'

	SET @fileunique = (Select RIGHT('Run' + CONVERT(VARCHAR(6),Run_Number), 6) as [Run_Number] from Run_UK)

	SET @MkDir = ''+@Dir+@fileunique+''

	UPDATE Run_UK
	SET Run_number = @RunNumberTest+1,Date = Getdate()

	SET @file = '"'+@MkDir+'"' + '\parkbad.txt'
	SELECT @sql = 'bcp "select * from Park.dbo.Park_Bad_Export_out " queryout '+ @file +' -c -t"," -T -S' + @@servername

'Clever boy...'
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top