0

I asked this question last week but was looking for how to do it with a batch script. I think it might be possible to do with R, but I'm not very experienced using it. However, after doing some research, I'm pretty sure its impossible to do it with a batch script alone so I think I'll need to use R or VBA script. I don't want someone to just throw the solution at me, if it requires using R or VBA then I'm only interested if you would link some good resources. I've been scouring the interwebs but have found nothing so far.

I need some sort of script that will:

  1. Take the two folders as inputs
  2. Generate a list of all the files in one of them.
  3. For each file:
    • Read in the data from columns D-G
    • Find the matching file in the other folder and read in the same data
    • Compare each cell and verify that the two files match exactly
    • If they don’t match, report what data doesn’t match

This is what I was asked to do verbatim.

This is what I've done so far.

@echo off
setlocal disableDelayedExpansion

cls

rmdir c:\LocalDirectory/s /q
mkdir c:\LocalDirectory
xcopy "\\SERVER\Path\to\the\files" c:\LocalDirectory

cd c:\LocalDirectory

dir /b /a-d

as you can see, its not much. I can make a list of the files but I need something that can compare them. I know that I could manually do this comparison in Excel but I need to be able to do this for several files. So I'm trying to write some kind of script that will take a specific column of data in a specific excel file in a directory and compare all the rows of that column to a specific column in another xlsx file and then once its done that, generate a message that says either pass or fail. Once thats done, it should move on to the next excel file. The files that have the data thats being compared are named the exact same thing.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
JohnN
  • 968
  • 4
  • 13
  • 35
  • 2
    In R there are different options for reading Excel files. http://www.r-bloggers.com/read-excel-files-from-r/ Additionally, you will probably need to use the commands `file.path()` and `list.files()` `file.path()` will take arguments and create a filepath, so `file.path(base,folder1)` will create something like "base/folder1". Then this can be inputted into `list.files` which will then list all the files in a given file.path – kristang Mar 30 '15 at 14:18
  • There's no reason why this can't be done with a batch script, and in fact, I frequently do this type of batch processing at work. However, at the moment your question is quite broad. I think you should start by trying to work on an R script that handles the actual data comparison logic, and add this to your question. Having done this, it will be much easier to help you with the remaining steps in your process. – nrussell Mar 30 '15 at 14:31
  • Because there doesn't seem to be anyway to read in data from an excel file in a batch script. Thats the answer I've found through research and here – JohnN Mar 30 '15 at 15:17
  • You just need to write an R script that reads the data in (and does any other processing) - I typically use [XLConnect](http://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf) - and then create a batch file (`.bat` on Windows, `.sh`, etc.. on Unix / Linux) that calls [Rscript](https://stat.ethz.ch/R-manual/R-devel/library/utils/html/Rscript.html) to run your R file - e.g. `Rscript --no-save --no-restore my_rscript.r`. I assure you this is possible because I've done it many times. – nrussell Mar 30 '15 at 15:58
  • But this is pretty much my point, I can't use a batch file to read and process the data. – JohnN Mar 30 '15 at 17:19
  • 1
    It is *very* difficult if not impossible to to do this with just batch processing. If you are worried about having to install something, then use VB and batch. Execute a VB script with `cscript.exe` to print your columns. When you call it, redirect it to a text file. Call it twice for each matching file and compare the text files that you generate using the batch `for` loop. – Okkenator Mar 31 '15 at 10:37
  • 1
    See this to simply save the `xls` file to something that batch can parse. http://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line – Okkenator Mar 31 '15 at 10:53

0 Answers0