Scripting data-report for multiple CSV files

[probat]

Hello,

I've been doing a lot of digging around on the forums and I have not found conclusive answers for my questions. If someone would be so kind to answer them, that would be great!

First a description of what my end goals are:

I want to create a script to automatically generate side-by-side difference reports for roughly 100 csv files. I would like to dump 50 of the files in one folder and dump the remaining 50 files in a second folder. Read the folders in the script and loop through each file in each folder creating difference reports for them, ending with a total of 50 difference reports.

Preferably I would like to create an html-color report and save it as an MS Excel file (*.xlsx) - but from what I was reading this is not doable in BC4. So I will need to save the difference reports as *.html files and then open each one in Excel and perform a Save-As *.xlsx.

Below is my command line to activate the script:

BCompare.exe @"C:\Users\probat\Desktop\Compare_Sample\Sample_Script.txt"

Below is the code for the script:

# Generating a report of differences
# This script generates a report of only differences.
#_____________________________________________________

# Set up basic comparison features.
criteria rules-based

# Filter out log files.
filter "*.txt; *.mdb"

# Load first comparison.
load "C:\Users\probat\Desktop\Compare_Sample\First_Folder" "C:\Users\probat\Desktop\Compare_Sample\Second_Folder"

# Compare files with timestamp differences.
select all.diff.files

# Generate a report of the differences.
data-report layout:side-by-side &
options:ignore-unimportant,line-numbers &
output-to:"C:\Users\probat\Desktop\Compare_Sample\Diff_Report.html" &
output-options:html-color

Question 1: How can I automatically generate a name for each difference report. Currently I have "Diff_Report.html" as the name but this will obviously overwrite every single difference report file and I will be left with one difference report at the end, rather than 50 difference reports. I would like to use a part of the name from the .csv files so they can easily be recognized if that is possible?

Question 2: Is it possible to set up rules in the script for the data-report, for example enable "unsorted alignment "?

Question 3: Is it possible save the difference report as a MS Excel file? (From material I've read I don't think it is)?

[Aaron]

Hello,

When selecting multiple files and generating a report, this will generate a single report file. To generate individual reports, you'll need to:
a) pass in 2 files as parameters and generate a single report.
b) load folders, and generate a Folder-report with the include-file-links option.

For a), you can use:
bcompare.exe "@c:\bcscript.txt" "c:\users\probat\desktop\compare_sample\first_fold er\file1.txt" "C:\Users\probat\Desktop\Compare_Sample\Second_Fol der\file1.txt" "C:\Users\probat\Desktop\Compare_Sample\Diff_Repor t_file1.html"

where bcscript.txt is then:
data-report layout:side-by-side &
options:ignore-unimportant,line-numbers &
output-to:"$3" &
output-options:html-color "$1" "$2"

2) Not as a scripted option. You can set the default settings of the Table Compare session in the graphical interface, which is then used as part of the script. You could also edit the BCSessions.xml before calling to the script, to edit the default session settings.

3) No, the output is either to printer, .txt, or .html. Excel supports opening .html files into the main interface, which can then be Saved As... .XLSX.

[probat]

Thanks for the info Aaron. A quick follow up question regarding the following you provided:

bcompare.exe "@c:\bcscript.txt" "c:\users\probat\desktop\compare_sample\first_ fold er\file1.txt" "C:\Users\probat\Desktop\Compare_Sample\Second _Fol der\file1.txt" "C:\Users\probat\Desktop\Compare_Sample\Diff_R epor t_file1.html"

I have 100 files I am going to be comparing. Is there a way that I can give some parameter instead of having to verbatim include the name of the files to be diff'd? For example:

bcompare.exe "@c:\bcscript.txt" "c:\users\probat\desktop\compare_sample\first_ fold er\<<<Some Parameter to Fetch 1st File>>>" "C:\Users\probat\Desktop\Compare_Sample\Second _Fol der\<<<Some Parameter to Fetch 2nd File>>> "C:\Users\probat\Desktop\Compare_Sample\Diff_R epor t_file1.html"

This would be more efficient.

Thanks

[Chris]

If you haven't tried it yet, using folder-report with include-file-links as Aaron described earlier might do what you want. It creates a single parent folder compare report as HTML listing names of files, when you click on links in that report it will open a separate report for each pair of files.

[probat]

Aaron stated that I can do the following:

2) Not as a scripted option. You can set the default settings of the Table Compare session in the graphical interface, which is then used as part of the script. You could also edit the BCSessions.xml before calling to the script, to edit the default session settings.

For data compare or table compare, I need to select the column keys so it sorts on the correct data.
I generated my own BCSessions.xml file like Aaron suggested above, but this does not work.
The difference reports are not sorting on the correct columns that I have selected as the key column in the XML file I generated.

I presume there is only one BCSessions.xml file located on Windows in 'AppData\Roaming\Scooter Software\Beyond Compare 4\'

For simplicity, I will include one entry from the BCSessions.xml file where I set the "Keys Value=4":

<?xml version="1.0" encoding="UTF-8"?>
<!-- Produced by Beyond Compare 4 from Scooter Software -->
<BCSessions Version="1" MinVersion="1"><TSessionFolder>
<Items>
<TSessionFolder>
<Items>
<TDataCompareSession Value="EXAMPLE_1.csv">
<LastModified Value="2018-08-14 10:39:35"/>
<Rules>
<Cols>
<Keys Value="4"/>
</Cols>
</Rules>
<Specs>
<Left Value="C:\Users\probat\Desktop\CSV_Revision1\EXAMP LE_1.csv"/>
<Right Value="C:\Users\probat\Desktop\CSV_Revision2\EXAMP LE_1.csv"/>
</Specs>
</TDataCompareSession>
</Items>
</TSessionFolder>
</Items>
</TSessionFolder>
</BCSessions>

[Aaron]

Hello,

Once you are editing .xml on the fly (before launching Bcompare.exe), I would recommend using a Portable Install. This way, you can edit and verify the changes still work in the graphical interface by loading a new Table Compare and then dragging your files into it, and then also in scripting. A Portable Install shares the same settings with any User account, avoiding any difference between graphical or scripted solutions or any AppData access issues. You can create a Portable Install using the setup.exe, and place it in a location where any user would have read/write access (such as C:\Tools\Beyond Compare 4\).

For the above example, you need to edit the default Table Compare settings for script to then use those defaults when generating reports. To set the default, use the Home screen's Saved Sessions list, expand the New folder and edit the Table Compare : Edit Defaults. Or if you are already in a Table Compare, editing the Session Settings, update the bottom dropdown from "Use only for this view" to "Also update session defaults"

[probat]

Aaron,

I do not have access to the setup.exe at my workplace to create a portable install - so I cannot do this.

I am also trying to automate the generation for hundreds of difference reports of tables, so editing the default settings in the graphical interface is not feasible since that would be time consuming.

Why doesn't the BCSessions.xml file I created work? I can not see anything that is wrong with the contents of it.

Thanks for your help.

[probat]

A follow up, after opening Beyond Compare in the home screen, I can clearly see under Auto-Saved --> Today, that all of the default sessions from the XML file exist. When I double click one of the saved sessions, it does open up with the proper column keys set. When scripting though, it does not work. Is there something I need to say in the BC script to use the default saved sessions?

[Aaron]

Hello,

The Auto-saved sessions are not the Default session. The default settings can be seen in the "New" folder, and select the default "Table Compare". Or, if you launch a new, blank Table Compare with the big Home screen button, this loads with the default settings. The auto-saved sessions are specific sessions, and aren't used unless they specifically are loaded (which script cannot do). The workaround for the scripted solution is to update the default settings in .xml, so that the automation then loads with these default settings, which can include the column setting.

If the defaults are properly configured, loading any new, blank Table Compare will have your key column set, and then loading any new files into it (Drag/drop) will load them and use the configured key column.

If you create a Copy of your install directory and manually place a BCState.xml within the BCompare.exe, this would also create a Portable Install. This can be verified if run, it will show factory defaults (no saved sessions or anything). If it is loading any previously saved sessions, then it was not successfully set up.

[probat]

If you edit the default settings for Table Compare - you can only set this setting once for no particular files being compared. Is this a correct statement?

Meaning if I set the default column key to 2 and 4, then every Table Compare I do will be set to 2 and 4. This is not desirable. I need the column keys to dynamically change depending on the Table that is being compared.

Is there a way to do that. According to your previous response there is apparently. Will you please be more descriptive on how to achieve this in your response. I need to set the column key differently depending on the Tables that are being compared.

Thanks.

[Aaron]

Hello,

Not within automation. The graphical interface allows you to configure and save sessions based on specific files and setting combinations, then load and generate reports as needed. The scripting, however, does not support loading a saved Table Compare. The workaround is that script will use the default table compare settings when generating a report, and because these settings are plain text, it is possible to override them (changing the global defaults on the fly) before calling any specific compare. This scripted solution can update the default depending on which files you are loading, and then load the files/generate the report. It's not an ideal workflow, but it's working within the limitations of the script support.

Since Beyond Compare has dozens of option combinations, the easiest way to determine the syntax for overriding the BCSessions.xml is to generate these changes in the interface, and then compare against an older version of the BCSessions.xml. This way you can see how any setting change is saved in the file, and then create an automated solution (external to BC4) which can update the .xml the same way, before you call to Bcompare.exe to automate the report creation.

Your above .xml example successfully updates the Key columns, but is within a specific saved session "EXAMPLE_1.csv", and isn't the default node location. To find this node, save off a copy of your current BCSessions.xml. Then go to the Home screen, expand the New folder, select Table Compare, and click the "Edit session defaults" button. Update the default column settings, and Save. Load a new, blank Table Compare and load your files and note that the columns are correctly 2,4 by default. Then close BC4 and save off a copy of this updated BCSessions.xml. You can then compare BCSessions.xml to the older BCSessions.xml and see how the settings have updated in the default node section, and how (and, if you changed any other settings, how they also updated).

[probat]

Thank you, I know what I need to do now... which is what I was hoping to avoid. I will have to create the xml with the default, then generate a difference report, regenerate the xml, and so on....

I think this should be added as a scripting feature... I would say the reason why people want to script is for doing huge amounts of difference files. Without this type of flexibility, it makes extremely difficult to script difference reports and almost makes it not worth the effort.

Thanks for you time.

[Aaron]

No problem. Enhancing our script is something on our wishlist, and I'll add your notes to our entry on the subject.