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

  • 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!

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

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

     

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

     

  • 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!

  • 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 

  • 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!

  • Yes that works well.  Thanks. 

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

     

  • 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

     

  • 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!

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

  • 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

Viewing 15 posts - 1 through 15 (of 18 total)

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