Resolve truncated column data issue

[LeannePyle]

• [x] Send historic spreadsheets for processing
• [x] Identify business key that can be used to link between all spreadsheets
• [x] Identify rows/columns of data that have truncated data
• [ ] Clean truncated data in latest spreadsheet with data from earlier non-truncated spreadsheets
• [x] Provide old/new mapping to validate (@bferguso)

[bferguso]

OK, so I've had a look at the data and there is no decent unique column combination we can use to connect the separate spreadsheets. So far I'm thinking the best approach is to look for long values (> 249 characters) in the latest spreadsheet and to work our way backwards to try to fill in the blanks. I think it's going to be a potentially manual process, but I'll try to match the values up the best I can.

So far I'm seeing potential truncation as follows:

• [ ] 130 detailed_location has rows > 249 (7 distinct) found 7
• [ ] 94 location_description has rows > 249 (35 distinct) found 35
• [ ] 165 significant_fossil_list has rows > 249 (122 distinct) found 87 distinct truncated values
• [ ] 330 non_significant_fossil_list has rows > 249 (206 distinct) found 205
• [ ] 115 reference_author has rows > 249 (1 distinct) (found 1)
• [ ] 925 reference_title has rows > 249 (106 distinct) (found 106)
• [ ] 1834 additional_notes has rows > 249 (125 distinct) found 125

Based on this, most columns seem like the can be mapped except significant_fossil_list. We may need to start with the long (133) list and do some manual corrections.

[bferguso]

Spreadsheet created and sent to @LeannePyle & @elisabethdeom via SOFT. Will be available for 7 days.

[bferguso]

Issue moved to bcgov/BCHeritage #136 via ZenHub