Need some help with data comparison!

[helpmeplz]

Here is what I am looking to do:

I have an master excel data sheet that contains information on properties going to auction which is pulled from a website. Every day I download new data from the site and I need to compare the new list to the old list. The master sheet is fed into a model so I want to preserve the location of the data in the master sheet and add new properties to the list at the bottom. I also want to know if 1 specific column has changed and then update the master sheet to reflect the changed data.

I have been able to compare the two files and see what is different but I have not found a good method to copy/export the changed/additional data back into my master excel sheet.

I feel like this should be easily doable with this program but I am under some time constraints and I would appreciate any help.

[Aaron]

Hello,

The Data Compare will sort and align on the current Key column. If you do not need to sort, you can disable it to preserve the original order in the Session menu -> Session Settings, Columns tab, check "unsorted alignment".

You could also use the alternate download to open the files in the Text Compare.

Excel files are not editable in BC3, since we do not have the ability to Save As the original .XLS/X file type. Are you manually bringing edits back into Excel?

[helpmeplz]

I was able to play around with the program and get it to do roughly what I wanted. I messed around with the rules and adjusting which columns are determined important and I was able to accomplish what I needed. Granted, I needed to make it a multi-step process to isolate each "difference" being analyzed and then manually copy/paste back into excel but fortunately copying/pasting is effective and didn't cause any problems in excel.

There is another issue I discovered though. Our main model is in .xlsb form and when I try to load it into the program I see only garbled odd characters as the output when I load it in as the left orphan. Is there a way to make .xlsb files load correctly or will I need to keep a copy of the data sets in a .xls file for the purposes of using BC3 for comparisons?

[Chris]

Beyond Compare 3 has file formats that define how to open files based on file extension. Beyond Compare doesn't ship with a definition for .xlsb, so that is why it isn't opening correctly.

You can try configuring Beyond Compare to open xlsb files the same way it opens xls files, that might allow you to view the files in the Data Compare. In Beyond Compare 3, select "Tools > File Formats". Select the "MS Excel Workbooks" file format. In the General tab, add *.xlsb to the end of the list, with each list item separated by a semicolon, then save the changes.

If the above method doesn't show your file's contents, then you'll need to save the files as plain .xls or .xlsx files before opening them.

[Critcho]

I was trying to use Beyond Compare 3 to check the differences between two versions of personal.xlsb (after a system crash) to make sure I had everything before deleting the duplicate. I found that if you compare the two files, it's just binary garbage (not sure how XLSB stores it's code, perhaps compressed text?).
Instead, I just printed each workbook (from within the VBA editor) to PDF, then compared the two PDFs with Beyond Compare, worked a treat! Still have to go back into the VBA editor to manually push the changes around, but that's not a problem.

[Aaron]

Hello,

If you follow Chris' steps above to download the MS Excel Workbooks format, you can then edit it. By default, it works with *.xlsx, but add ;*.xlsb to append that extension, too. Then it'll open directly in BC3 without needing to manually convert ahead of time.

It would be read-only in BC3, either way, since we cannot save back in the xlsb format, so edits would still need to be external.

[Critcho]

Have you tried this? XLSB is macro workbooks, the macro source code is not stored as plain text, it's just binary garble. Is there an extra step I've missed?

[Aaron]

Hello,

You have a small typo in the extension list. You have a space character and are missing the *. needed for the mask. It should look like:
*.xls;*.xlsm;*.xlsx;*.xlsb
with no spaces.

With my simple test case, this seemed to open as expected when tested. However, my constructed xlsb file is very simple; but it should at least open in the correct viewer and not the Hex Compare.

All Excel files are binary. The conversion process helps show the plain text within, and without a defined file format conversion they would all open as binary gibberish in the Hex Compare. This is also why we aren't able to edit/save Excel files, since we don't have the process to convert back to the Excel (binary) format.

[Critcho]

Aaron,

I've changed the file types to *.xlsb, the result is the same.

Do your test workbooks have any macros in them?
Sorry if this is stuff you already know, but in case it's not: To record a macro quickly, open the workbook, from the Developer menu select Record Macro. just pick a few random cells then press stop. This will ensure your book actually has macros, but this stores them as macro-enabled workbooks. I've tested this and it shows just the contents of the workbook, not the macros.

If I save these as Excel Binary Workbooks (*.xlsb), they show as binary garbage.

[Aaron]

Hello,

Could you email us a pair of sample files and your BCSupport.zip (Help menu -> Support; Export) to [email protected]? Please include a link to this forum thread for our reference.

My test files don't have macros, but with the defined File Format and extension, it should at least open in the Data Compare viewer (and then fail to convert or display) rather than the Hex Compare. I tried Recording, selecting, and Stopping the Macro, re-saving, and that seems to work and still opens well in the Data Compare (showing just the cell data, no Macro information).