4

I noticed something strange when executing the Copy command from a Windows batch file and was wondering if anyone has encountered this before and can explain the cause.

I have a line in a batch file which copies an Excel file from location A to location B and also renames the file. For example:

Copy \\server\share\folder\locationA\file_*.xlsx \\server\share\folder\locationB\file.xlsx /y

The copy looks like it completes fine, since the Excel file is present in location B, however when I go to open the file, I get an error message stating that:

"Excel cannot open the file... because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

I ran some tests with the above command line and noticed that this issue only happens if I use a wildcard in the filename in part A. For example, the file copies and I can open it just fine with this command:

Copy \\server\share\folder\locationA\file_LongName.xlsx \\server\share\folder\locationB\file.xlsx /y

I realize that there are numerous ways to fix this, but I'm not interested in a solution, I'm interested in an explanation. My question is why is this happening?

3

The reason you're seeing this issue is that the wildcard pattern switches the COPY command into concatenation mode, which is designed for plain text ASCII files. In ASCII mode, some data in binary files looks like an "End of File" character.


Excel .XLSX files are essentially Zip archives with a different extension and Zip archives are binary files, not ASCII. The COPY command is treating this binary file as an ASCII files and trying to concatenate the contents with nothing.

One might think concatenating a file with nothing would give you the same file you started with, but not in this case.

The COPY command continues processing a file only until it reaches an End of File (EOF) character. Once it reaches this character, it continues to the next file. (In this case it stops processing entirely.)

Your binary Excel files contain data that, when converted to ASCII, represent EOF characters and therefore the concatenation of the file ends earlier than expected.

To illustrate this, I used your COPY command to concatenate a 7zip file with a blank Excel file (I just renamed them to file_1.xlsx and file_2.xlsx). I opened the 7zip file and the output file in Notepad++ and compared the contents using WinMerge.

WinMerge comparison

As you can see in the image, the two files are identical up until the (1A) character.

Next, I concatenated two plain text files, which worked flawlessly. However, once I inserted this (1A) character (which shows in Notepad++ as (sub)) into one of the text files, I was able to confirm that the COPY command stopped exactly at that point and moved on to the next file.

Concatenation in Notepad++

1
  • Wow, what a thorough answer. Thank you very much for this!
    – roger21
    Oct 1 '18 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.