1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Bat file to copy xls and add time/date

Discussion in 'DOS/Other' started by ecellis, Nov 14, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. ecellis

    ecellis Thread Starter

    Joined:
    Jul 4, 2011
    Messages:
    6
    Hi,
    I am trying to create a simple bat file that will copy all the excel files in a folder into another folder but add the time and date to the name. I am running Windows 7 if that matters. Here is basically what I am looking for:

    example1.xls, example2.xls, example3.xls are the original files. I want to copy them into a backup directory that will already contain hundreds of backed up xls files that need to stay untouched. When they are being copied, I want the time (hhmm) and date (mmddyy) appended to the end of the filename.
    I want the files to turn out like this: [email protected], [email protected], etc.

    I started out with this: copy *.xls %date:~4,2%%date:~7,2%%date:~10,4%*.xls but it was over writing the filename so I didn't even try the time part. It has been a long time since I have worked with DOS and I forgot most of it. I still will use the occasional BAT file to automate something.

    I would prefer it to stay simple so I can modify it as needed. I don't care if the time needs a 0 for 2:00(0200) or if it is military time. I do want the date to stay MMDDYY (1st choice) or MMDDYYYY.

    Thank you for any help you provide!
     
  2. Squashman

    Squashman Trusted Advisor

    Joined:
    Apr 4, 2003
    Messages:
    19,786
    Can you show me the output of your Date and Time variables from the cmd prompt.

    Code:
    H:\>echo %time%
     8:00:01.92
    
    H:\>echo %date%
    Tue 11/15/11
    
    H:\>
     
  3. ecellis

    ecellis Thread Starter

    Joined:
    Jul 4, 2011
    Messages:
    6
    Hi Squashman,
    Thanks for replying! Here is the answers to your questions.

    Time is:
    0:07:45.69 (12:07 AM)

    Date is :
    Thu 11/17/2011

    Thanks again!
     
  4. Squashman

    Squashman Trusted Advisor

    Joined:
    Apr 4, 2003
    Messages:
    19,786
    You should be able to do something like this.
    Code:
    FOR %%A in (*.xls) do copy "%%~A" "%date:~4,2%%date:~7,2%%date:~10,4%_%%~A"
     
  5. ecellis

    ecellis Thread Starter

    Joined:
    Jul 4, 2011
    Messages:
    6
    Hi Squashman. Thanks for the reply. I've been out this weekend and just got back. I tried the code above but I get an error saying that %%A was unexpected at this time. Any ideas?

    Thanks again!
     
  6. Squashman

    Squashman Trusted Advisor

    Joined:
    Apr 4, 2003
    Messages:
    19,786
    Did you put that code into a batch file or did you run it from the command prompt?
     
  7. ecellis

    ecellis Thread Starter

    Joined:
    Jul 4, 2011
    Messages:
    6
    Hi Squashman,
    I think I figured it out. Here is what I ended up with so others can save time:

    @Echo Off
    @For /F "tokens=1,2,3,4 delims=/ " %%A in ('Date /t') do @(
    Set Day=%%A
    Set Month=%%B
    Set Year=%%C
    Set Year=%%D
    Set All=%%A%%B%%C%%D
    )
    @For /F "tokens=1,2,3 delims=: " %%A in ('Time /t') do @(
    Set Hour=%%A
    Set Min=%%B
    Set Sec=%%C
    Set Allm=%%A%%B%%C
    )

    @For %%a in ("*.xls") do copy %%a "%%~na_%All%_%Allm%.xls"

    I kind of combined a couple of pieces I saw in other posts into 1 and edited the last line to include both. It seems to work. I will probably strip out the day. I have never used the FOR /f before but it seems like it can be pretty useful! It's been a while since the good old DOS days and my memories are a bit mushy.

    One thing I don't really get is why the seconds don't show up in the file name. I don't want them, but it looks like they are being set. Is it because the time /t command does not show seconds?

    Again, thank for replying and the help! I really do appreciate it!
     
  8. ecellis

    ecellis Thread Starter

    Joined:
    Jul 4, 2011
    Messages:
    6
    Duh... I forgot about the %% vs % difference when a bat file vs straight command. :)

    Banging head on the wall

    Your code wins for it's simplicity! I modified it to:
    FOR %%A in (*.xls) do copy "%%~A" "%date:~4,2%-%date:~7,2%-%date:~10,4%@%time:~0,2%%time:~3,2%_%%~A"

    The file name ends up looking like this: [email protected]_example.xls (ack the message board made it a link but you get the idea - there is an _ betwen 1935 and example)

    That way it has the time too. It shows the time in 24 hour format but I guess that will work. Is there a way to bring it to 12h format?

    Thanks!
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - file copy date
  1. niedernsill
    Replies:
    3
    Views:
    419
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026898

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice