Manually-generated excel files versus computer-generated reports/files

Why is it the data in a user-generated report (e.g., manually encoded journal entries in an excel file) are destroyed once I append or merge them using Audit Command Language or windows cmd?

Like for example, I am trying to append the contents of sales reports whose data content was manually inputted by a human being– however, once I append them the data are destroyed. This is not the case for computer-generated reports (i.e., reports generated by SAP).

Excel files are binary files, not plain text. Running copy *.txt consolidated.txt is fine but running copy *.xls consolidated.xls does not work because binary files are encoded so that you have to open a specific program that understands the structure of the data in the file.

One way to consolidate a folder of Excel files is simply to convert them to a plain text format (eg a csv), and consolidate those using copy on the command line.

You can create a macro to do this for you:

1. Create a new Excel file. Save the file in the same directory as the files you want to consolidate as a macro-enabled workbook: eg CsvConverter.xlsm

2. Press alt+F11 to open the Visual Basic Editor. Select Insert then Module.

3. Paste the following code into your module (taken from here):

   Option Explicit
   
   Sub ConvertToCSV()
   
   ' Uses code from John Walkenbach's Power Programming book
   
   '
   
   Dim i As Long
   
   Dim NumFiles As Long
   
   Dim FileName As String
   
   Dim FileNames() As String
   ' Get name of first file in backlog directory
   
   FileName = Dir(ThisWorkbook.Path & "/*.xls")
   NumFiles = 1
   
   ReDim Preserve FileNames(1 To NumFiles)
   
   FileNames(NumFiles) = FileName
   ' Get other file names, if any
   
   Do While FileName <> ""
   
   FileName = Dir()
   
   If FileName <> "" Then
   
   NumFiles = NumFiles + 1
   
   ReDim Preserve FileNames(1 To NumFiles)
   
   FileNames(NumFiles) = FileName
   
   End If
   
   Loop
   ' Save each file as a .csv file, overwriting any existing .csv files
   
   Application.DisplayAlerts = False
   
   For i = 1 To UBound(FileNames)
   
   If FileNames(i) <> ThisWorkbook.Name Then
   
   Workbooks.Open FileName:=ThisWorkbook.Path & "" &
   FileNames(i)