Rename a text file, move it, using a stored procedure

• texpic

  SSCertifiable

  Points: 5887

  More actions

  May 1, 2005 at 9:11 am

  #88261

  I have a stored procedue that BCP's data into a table from a file called ImportData.txt in a directory C:\ImportData.

  So prior to the BCP in, the file looks like this:

  C:\ImportData\ImportData.txt

  What I'd like to do is change the name of the file to include the date and time, and move it to a subdirectory.  This way I know which file was BCP'ed, and don't have to think about whether I have the right file next time I import.

  So after, it should look like this:

  C:\ImportData\Finished\ImportData_20050430_1351.txt

  C:\ImportData\Finished\ImportData_yyyymmdd_hhmm.txt

  I think there is a way to do this using a stored procedure.  Any help would be appreciated.  Thanks!

• texpic

  SSCertifiable

  Points: 5887

  More actions

  May 1, 2005 at 12:17 pm

  #556187

  This will work for naming the file.  Now all I need to do is figure out how to move and rename the file.

  DECLARE

  @TodayDate as varchar(40),

  @TodayHour as varchar(40),

  @TodayMinu  as varchar(40)

  SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)

  SELECT @TodayHour = DATEPART(hh,GETDATE())

  SELECT @TodayMinu = DATEPART(mi,GETDATE())

  SELECT 'ImportData' + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.txt'

• texpic

  SSCertifiable

  Points: 5887

  More actions

  May 1, 2005 at 12:36 pm

  #556188

  I'm getting closer:

   

  EXEC master..xp_cmdshell 'REN C:\ImportData\ImportData.txt ImportData1.txt'

  EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData1.txt C:\ImportData\Finished\ImportData1.txt'

   

• texpic

  SSCertifiable

  Points: 5887

  More actions

  May 1, 2005 at 12:47 pm

  #556189

  The last line of this does not work  ...  any ideas?

  DECLARE

  @TodayDate as varchar(40),

  @TodayHour as varchar(40),

  @TodayMinu  as varchar(40),

  @NewFileName as varchar(100)

  SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)

  SELECT @TodayHour = DATEPART(hh,GETDATE())

  SELECT @TodayMinu = DATEPART(mi,GETDATE())

  SELECT @NewFileName = 'ImportData' + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.txt'

  EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\ImportData.txt'

  EXEC master..xp_cmdshell 'REN C:\ImportData\Finished\ImportData.txt ' + @NewFileName + '.txt'

   

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 2, 2005 at 6:00 am

  #556250

  the move command really just copied the file with the given name, and then deletes the previous file. there is no need to move it with the same name, and then rename it.

  just use :

  EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\' + @NewFileName

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• texpic

  SSCertifiable

  Points: 5887

  More actions

  May 2, 2005 at 9:06 am

  #556321

  The single command works like this:

  EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\ImportDataChanged.txt'

  When I add the + @NewFileName it does not work

  EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\'  + @NewFileName 

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 2, 2005 at 9:51 am

  #556329

  does this work for you? i just put the commadn into a string and caled the shell with the variable.

  DECLARE

  @TodayDate as varchar(40),

  @TodayHour as varchar(40),

  @TodayMinu  as varchar(40),

  @NewFileName as varchar(100),

  @cmdstr as varchar(128)

  SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)

  SELECT @TodayHour = DATEPART(hh,GETDATE())

  SELECT @TodayMinu = DATEPART(mi,GETDATE())

  SELECT @NewFileName = 'ImportData' + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.txt'

  print @NewFileName

  set @cmdstr='MOVE /Y C:\ImportData\ImportData.txt C:\ImportData\Finished\'  + @NewFileName 

  print @cmdstr

  EXEC master..xp_cmdshell @cmdstr

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• texpic

  SSCertifiable

  Points: 5887

  More actions

  May 2, 2005 at 9:02 pm

  #556437

  Yes that works well.  Thanks. 

• Vijay-383566

  SSC Journeyman

  Points: 75

  More actions

  November 17, 2006 at 9:09 am

  #672652

  Hi, Has anyone tried renaming a file (through xp_cmdshell) which is placed in a network share? My sql server agent (to run the job) is running under a account which is having full permission on the share.. but still does not solve the problem.

  Any help would be greatly appreciated.

   

• texpic

  SSCertifiable

  Points: 5887

  More actions

  November 17, 2006 at 9:55 am

  #672675

  You probably need to use the UNC and share name to make it work.

  \\192.168.0.xxx\C\YourDirectory\YourFile\

  or

  \\192.168.0.xxx\C$\YourDirectory\YourFile\

  etc

  Only guessing

   

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 17, 2006 at 10:11 am

  #672684

  this works for me, assuming the files and the folder structure exists: I'm moving a local file to a server named DAISY. The server has a share named C_DRIVE, and i have access to it.

  DECLARE

  @TodayDate as varchar(40),

  @TodayHour as varchar(40),

  @TodayMinu  as varchar(40),

  @NewFileName as varchar(100),

  @cmdstr as varchar(128)

  SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)

  SELECT @TodayHour = DATEPART(hh,GETDATE())

  SELECT @TodayMinu = DATEPART(mi,GETDATE())

  SELECT @NewFileName = 'ImportData' + '_' + @TodayDate + '-' + Right('0' + @TodayHour,2) +  Right('0' + @TodayMinu,2) + CASE WHEN  DATEPART(hh,GETDATE()) >11 Then 'PM' ELSE 'AM' END + '.txt'

  print @NewFileName

  set @cmdstr='MOVE /Y C:\ImportData\ImportData.txt C:\ImportData\Finished\'  + @NewFileName 

  set @cmdstr='MOVE /Y C:\ImportData\ImportData.txt \\DAISY\C_DRIVE\ImportData\Finished\'  + @NewFileName 

  print @cmdstr

  EXEC master..xp_cmdshell @cmdstr

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Collin Keen

  SSC Veteran

  Points: 242

  More actions

  December 20, 2006 at 8:20 am

  #679133

  Good morning,

  I am new to the forum so thanks for the help so far. I am using a script from the first page of this thread, just to rename and move the file, but I am getting an access denied error. What settings do I have to check to ensure this will work? I do have access to both folders (they are local), not sure if its a problem running xp_cmdshell or not. Thanks for any advice.

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  December 22, 2006 at 11:17 am

  #679804

  Doesn't matter what YOU can see... what can the SERVER see?  The server must be logged in as a "power user" that can see shares and machines.  In Enterprise Manager, drill down to the server, right click on the server, select [Properties], and take a look at the [Security] tab... the [Startup Service Account] must be an account that can see the things you are after.

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

• MrBaseball34

  SSCrazy

  Points: 2515

  More actions

  August 26, 2008 at 8:28 am

  #863174

  Kind of wondering how you would catch any errors when moving the file.

  I make a copy to a tempfilename passed into the procedure and then if there is an error when

  trying to move the file to it's permanent place, I'd like to copy the file to another place.

• guillermo.delacruz

  Grasshopper

  Points: 13

  More actions

  August 26, 2008 at 9:52 am

  #863240

  Hi

  I have a text file with this layout

  HDR**

  SB74449D01

  SB74449D01

  SB74449D01

  SB74449D01

  TRL** 4

  HDR**

  SB74462D01

  SB74462D01

  SB74462D01

  SB74462D01

  SB74462D01

  TRL** 7

  I want to import to table in sql server, and insert the date and time, when it made the import, but I want when start HDR** and end in TRL** insert the time and date

  Could you help me please?

  Thanks