Export to sequential folders

[jeffwest21]

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...'

 

[simian336]

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

 

[jeffwest21]

This what I have that creates a folder called 20140918

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...'

 

[simian336]

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

 

[jeffwest21]

Simi

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

'Clever boy...'

 

[gmmastros]

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

Try this:

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

 

[simian336]

Nice function George... I like it.

Here is a pretty nice example.

http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html

Thanks

Simi

 

[jeffwest21]

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.

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.

		--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...'