No announcement yet.

Cross referencing CSV files

  • Filter
  • Time
  • Show
Clear All
new posts

  • Cross referencing CSV files

    I've been playing around with BC4 and like what I've been able to do so far but would like to know if the following is possible before I purchase:

    I have two CSV files with one key column. File 1 has a couple hundred thousand entries and file 2 has around ten thousand entries. I'd like to delete all rows in file 1 except for the ones that match data in the key column in file 2. I would then like to move some columns from file 2 into file 1 but in a way where the data corresponds to the key column.

    I've been able to do the matching part just from playing around but I don't really know what I'm doing and it's sort of messy. I'd appreciate some clear steps.


  • #2

    BC4's Table Compare can open your two csv files, set the Key column by right clicking it and setting to Key (or Standard or Unimportant). Once the Key is configured, this will align rows by this data. This assumes all Keys are unique. Is "match data in the key column in file 2" mean rows that are aligned because their key matches? Or where the key matches and all other column data (standard) matches? You can use the Display Filters to Show All or Show Same to show all aligned key rows with all or equal data.

    Once limited to only the rows you want to delete with the Display Filter, you can Select All and delete them. Then Verify with Show All to find the remaining rows.

    What controls how "data corresponds to the key column"? Would setting a different key, marking some columns as Unimportant, or using the Display filters limit the view to only the rows you need for this move?

    The main controls we have for limiting view are setting the Key, setting other columns as Unimportant and if they should not matter using the Ignore Unimportant Differences toggle (squiggly = sign in the toolbar), and using the Display Filters (toolbar or View menu) to limit the view to only rows that are All, Same, or Different.
    Aaron P Scooter Software