Comparing 2010 Excel Files (.xlsx)

[jack]

You can also disable the sorting in the Session menu -> Session Settings -> Columns tab, "Unsorted alignment."

THANK YOU! This is exactly what I was looking for.

[MRizzo]

Hi Chris,

It took a very long time but yes, it worked. Thank you so much!!

[Chris]

Does it help if you prevent the Data Compare from sorting?

To turn off sorting, select "Session > Session Settings".
Go to the Columns tab.
Check "Unsorted alignment".

[MRizzo]

No, all the data is at the top and it does not show them. As you can see from the screenshot, the data shown starts at the bottom of my worksheet.

[Aaron]

Hello,

Given the sample screenshot, it looks like those are blank cells, which would be sorted to the top. If you scroll down sufficiently far, is your data at the bottom?

[MRizzo]

No, none of actual data is being loaded. Yes, the Key Column is defined correctly. Please see attachment:

[Aaron]

Hello,

The Data Compare would Sort based on the key column. Is all of the data loaded, but just not in the order you expect? Do you have your Key column defined correctly? By default, we mark column 1 as the Key, but you can right click any column header to make it a Key, Standard, or Unimportant.

An example of a Key would be an ID column, or multiple columns together form the key (like a FirstName column and LastName column).

[MRizzo]

I have an excel file that has 15,712 rows and it's not loading all the data but going to the next line that is blank (15,713). What is wrong with this?

[Aaron]

Hello,

No, the presentation of the Text Compare will show the files as plain text. The Data Compare will display as rows, columns, and cells. How the information is displayed is one of the main differences between the session types.

If you try the third method of the KB article, it would use the Text Compare's conversion (multiple sheets) in the Data Compare, but it does mix all of the rows together.

[MRizzo]

Hi, I had use the Text Compare session. Is there a way to make it actually look lok excel in the windows of the session?

[Aaron]

Hello Jennifer,

There are a couple ways to tackle this:

1) Data Compare automatically aligns and sorts by the first column as if it were an ID column. You can change this to be a different column (or multiple columns: First Name and Last Name) by right clicking any column header and switching between Key, Std, or Unimportant. Key columns are the ones sorted by and aligned by.

You can also disable the sorting in the Session menu -> Session Settings -> Columns tab, "Unsorted alignment."

2) If you would just like to see your Excel files as if they were plain text, unsorted, without rows or columns, you can install our Text Compare Excel rule here:
http://www.scootersoftware.com/downl...oreformats_alt
Once installed, go to the Tools menu -> File Formats. Note that the topmost Excel format (gold icon or white icon) is the one used automatically when you open Excel files. The other must have the session opened first, and then open the Excel files, to use it.

How do these two methods work for you?

[Jennifer]

Thanks, Chris. That tip got me *almost* there. Selecting "data" enabled me to see actual data instead of garbled characters. But, the first column contains alphanumberic codes that identify the organization to which the people belong (R1, R3, TA, etc.). The 12 codes ended up in a small table at the bottom of the data - not associated with each row, as it should have been. I'd appreciate your further guidance - many thans!

[Chris]

To compare Excel files in Beyond Compare 3, open them in the Data Compare. If you try to open them in the Text Compare, you'll get garbled characters.

[Jennifer]

BC documentation seemed to indicate that comparisons with Excel were included in BC3, which I have. Is there something special that I need to do for BC3 to work with Excel 2010? All I see are special characters.