Compare CSV files: how to get better match?

[bcoldtimer]

See the screen shot:

Obviously a much better match can be obtained if Beyond Compare realizes that lines 665, 669, 670, and 671 on the left hand side are exactly the same as lines 664, 665, 666, and 667 on the right hand side. Are there adjustable options to help BC find the matches and avoid such "false alarm" of differences? Thanks!

[Aaron]

Hello,

BC3's Data Compare sorts and aligns by the Key column by default. The key column (or columns) should be unique identifiers. For example, an Employee ID, or combination of two columns: First Name and Last Name.

From your screenshot, I cannot see your column headers to see which is the Key column, but would it be column 1? If so, column 1's date, alone, is not enough to be a key since it has duplicate entries in the column.

Is there another column, or set of columns, that could be used as the unique identifier for the row?

If the row on a whole is compared without any concept of key, then it wouldn't need to be sorted based on a key column. What if you open it in our Text Compare (using the Session menu -> Compare Files Using submenu)?

[bcoldtimer]

Thanks! Text compare worked better. But sounds like I can have more than one key column with varying priorities? If so how to set it up? By the way I don't want to sort the data according to the content in the columns. Thanks!

[Aaron]

You can disable the sorting in the Data Compare's Session Settings, columns tab, but if you have a proper key column it usually makes sense to sort it. There isn't a varying priority; multiple key columns together form the overall Key. So "First Name" and "Last Name", for example, would form a unique name for each row, assuming no two people have the same full name.

Once the Key is defined, it is often sorted to make sure the key aligns properly. If it is out of order, it may not align, even if it is present in both files.

[bcoldtimer]

I see. Where do I define the keys?

[Aaron]

You can define a Key by right clicking the column header and setting it to Key, Standard, or Unimportant. Or you can define it in the Session Settings, Columns tab, select a column and Edit it.

[jokey]

Maybe it is a bit of hijacking but I created dataprofiles for other filetypes (which are csv based) yet I did not find to set key (and ignored) columns per filetype. Is this possible?

[Chris]

The Data Compare in Beyond Compare 3 can only define key columns per-session, not by file type (file format). To set key columns in the Data Compare, right click on a column header and select "Key Column". The other option is to select "Session > Session Settings" and double click on columns in the Columns tab.

[jokey]

Well, as a workaround I generate a session file that has right settings for files to be compared but it is more a quick-and-dirty-hack than a sane solution

[Aaron]

We always appreciate collecting sample/example files.

Do you have some sample files you would be able to email in? Please email us at [email protected] and include a link back to this forum thread for our reference.