Batch file to copy files and rename them to folder name

[Vassago]

Hello,

I have a file structure like this:

C:\Test\01.2020\ABC\FileA.csv
C:\Test\01.2020\ABC\FileB.csv
C:\Test\01.2020\ABC\FileC.csv
C:\Test\01.2020\BBC\FileA.csv
C:\Test\01.2020\BBC\FileC.csv
C:\Test\02.2020\DBC\FileA.csv
C:\Test\02.2020\DBC\FileB.csv

I need to create a script that will loop through the folders in C:\Test and capture all csv files, copying and renaming them to the following:

C:\Dest\012020_ABC_FileA.csv
C:\Dest\012020_ABC_FileB.csv
C:\Dest\012020_ABC_FileC.csv
C:\Dest\012020_BBC_FileA.csv
C:\Dest\012020_BBC_FileC.csv
C:\Dest\022020_DBC_FileA.csv
C:\Dest\022020_DBC_FileB.csv

I'm not great in batch scripts. Anyone have a good solution for this?

Thanks!

 

[Deleted member 147267]

At first I thought you meant via .bat or .cmd type of script. Maybe you mean vbScript or just any sort of vba code will do?

 

[theDBguy]

Hi. I first thought of using the FOR command. For example,

FOR /R C:\Test\ %%G IN (*.CSV) DO REN "%%G" "C:\Dest\%%~nG"

But your requirement is more complex than that because you want to also parse the folder and filename to create a new filename. So, as Micron said, you might go with VBScript or PowerShell instead.

PS. Stuff like this could be dangerous, so make sure to have a backup and test a few times. The above sample code is untested.

Cheers!

 

[Vassago]

I am certainly working with copied files.

I'm open to a VBA operation. It's been a long time since I've had to do anything like this with VBA.

 

[theDBguy]

I am certainly working with copied files.

I'm open to a VBA operation. It's been a long time since I've had to do anything like this with VBA.

Ah, if we can use VBA, I would recommend using FSO. Just my 2 cents...

CopyFile method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

PS.
I'm thinking along the lines of:

fso.CopyFile strFilename, Replace(strFilename,"Test","Dest")

But that still doesn't include the part for parsing the folder and filename to create a new filename.

 

[theDBguy]

Also, if I may, in case you are interested (and hopefully, it helps), I have a small demo on my website that may be related to this topic.

List Files in a Folder and Subfolders

 

[Deleted member 147267]

If dbGuy is on the right track, then using FSO you can loop through all *csv files in a directory but you don't need the replace function. FSO has a Name command which will not only rename the file, but the path as well, and I think it will create a path that doesn't exist, which might be your case. I don't think I have anything for this but will look, or you could google ms access vba rename files in the meantime. I'm sure you'll find lots of examples. Are you going to Kill the old file too? Do so only if the Name function doesn't err.

 

[theDBguy]

Hi Micron. I used Replace() because I quote:

I need to create a script that will loop through the folders in C:\Test and capture all csv files, copying and renaming them to the following:

So, I took that as the original files need to be preserved. However, I agree, the above sample code is still incomplete.

PS. Also, I just checked this list and didn't see a Rename or Name method.

FileSystemObject object

Office VBA reference topic

docs.microsoft.com

 

[Deleted member 147267]

copying and renaming
Maybe it's not a function or a method. I'm just going by memory. The syntax is
Name "C:\OldDir\OldFileName" AS "C:\NewDir\NewFileName"

If you try and can't prove that right, let me know and I'll try to find something. As for now, I gotta get off of here. Have done nothing else all day!

 

[theDBguy]

copying and renaming
Maybe it's not a function or a method. I'm just going by memory. The syntax is
Name "C:\OldDir\OldFileName" AS "C:\NewDir\NewFileName"

If you try and can't prove that right, let me know and I'll try to find something. As for now, I gotta get off of here. Have done nothing else all day!

Hi. I see what you mean. That's a VBA statement, and yes, that's available. It's supposed to "rename" a file, like Name OldName As NewName, but acts as "copy and delete (i.e., move)" when the AS part uses a different folder name, as in: Name OldDir\Filename As NewDir\Filename.

 

[Vassago]

Thanks everyone so much for helping me with this. As frustrating as this has been today for me, and after hours of work myself and you guys spent time trying to help me with this, they decided they didn't need me to do it after all.

Don't you love wasting a day of work?

I'm sorry, but I did learn some things I'll hopefully be able to use in the future.

 

[theDBguy]

Hi. Learning is never a waste of time. Cheers!

 

[Deleted member 147267]

I agree that it's not a total loss if you learned something. Even better is that you probably got paid during that learning spree. I bet DbGuy can't say that any more than I can! Better yet (from my perspective) I learned or was reminded that the Name statement actually moves the file at the same time if the rest of the path is not the same.

 

[Vassago]

True enough, though I could have been spending the time on other projects.