0

Soo, i'm a IT Intern in a Company, and we need to transfer some data from a server to another by CMD commands like: "C:/Source" "D:/Target" /commands .... And we are doing that to move from a old server to a new one and make some space on the Disk to use for something else.

The problem is there like 2000 lines of directory on Excel to do that, and doing that manually gonna take ALOT of time and me and the other team have more stuff to do, i was wondering if i can create a automatic script to read the line on excel and do that line by line to save ALOT of time. Like changing the "Source" and "Target" inputs but keeping the other commands after.

Thank alot!!!

FLCordis
  • 21
  • 5
  • There's https://stackoverflow.com/questions/17956651/execute-a-command-in-command-prompt-using-excel-vba but generally Excel is somewhere amongst the last environments to consider for working with files and shell commands. Depending on what's inside, it may be better to export the table as CSV/TSV and then handle that with practically anything else what's not Excel. – tevemadar Jul 25 '22 at 13:33
  • 2
    This sounds more like an Excel formula question, and not a Python or Java question. Can you not just create a formula in your Excel spreadsheet which [concatenates strings](https://support.microsoft.com/en-us/office/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d) together, to build all the commands you need? Then copy/paste the text of all those commands to a Windows [batch file](https://www.windowscentral.com/how-create-and-run-batch-file-windows-10) and run it. – andrewJames Jul 25 '22 at 13:34
  • @andrewJames can you help me with that? (im new on that stuff) For every line i need to write --> robocopy "E:\File\Example" "G:\File\Target" /s /e /copyall /xo" – FLCordis Jul 25 '22 at 15:34
  • And i copy the dir from Excel and paste on Command Prompt – FLCordis Jul 25 '22 at 15:38

1 Answers1

0

Assuming you have a list of the source directories in column A of your spreadsheet, then you could use an Excel formula such as the following in column B (or wherever you want to place it):

=CONCAT("robocopy ", """", A1, """", " ", """", "G", RIGHT(A1, LEN(A1) -1), """", " /s /e /copyall /xo")

Four double-quotes in a row result in one double-quote being generated as output.

The formula RIGHT(A1, LEN(A1) -1) simply trims the leading E (directory) from the path, so it can be replaced with the target G.

If A1 contains E:\File\Example then my formula generates the output:

robocopy "E:\File\Example" "G:\File\Example" /s /e /copyall /xo

Copy that formula down for as many rows as you need, then copy/paste the results to a text file (you may need to use paste-special to paste the resulting text, not the formula.

Rename the text file so it has a .bat file extension, so it is runnable from a CMD prompt. I would test the batch file with a very small number of commands first of all.

This approach is fine, I would say, for a one-time (or few-times) exercise. If you were doing this many times, then yes an automated (Java, Python, etc.) approach may make more sense.

Update:

For your updated comment below, the relevant formula would be very similar to the one I provided above:

=CONCAT("robocopy ", """E:\apl_cad\", A1, """", " ", """\\ara1app2\ENG3\PROTEÍNAS\OldVersions\", A1, """", " /s /e /copyall /xo")

If A1 contains X then the formula generates the following:

robocopy "E:\apl_cad\x" "\\ara1app2\ENG3\PROTEÍNAS\OldVersions\x" /s /e /copyall /xo

You can place that formula wherever you like - but make sure you adjust the cell references to A1 so they match wherever the actual X data is located.

andrewJames
  • 19,570
  • 8
  • 19
  • 51
  • 1
    BRO, IT WORKED! Now i need to find a way to execute every line on the CMD, but i think now i gonna do manually a Copy and Paste the command, the result of the formula is the working command. – FLCordis Jul 25 '22 at 16:35
  • "_execute every line on the CMD_" - That is covered in my answer: "_...copy/paste the results to a text file... Rename the text file so it has a `.bat` file extension, so it is runnable from a CMD prompt..._" You can ask a new question if you get stuck with that part - but there are many questions and answers already on SO for that. – andrewJames Jul 25 '22 at 16:41