An oil company which had an MS access DB and a form configured for it with no checks for formatting that would insert the fields of the form directly into the database, and then if they wanted to make a change, they would export the entire database as csv, open it in excel, make changes and use that to overwrite the entire database.
This had been going on since some time in the 1990’s. They finally wanted to move to a modernized databasing/operations solution which is what my company does.
I successfully cleaned 75% of that data, however it took 37 regular expressions and a script that was about 800 lines to account for every possible mis-entry, incorrect format, and merging fields if they were empty from newest records to oldest records until the fields were no longer empty where possible (essentially collapsing the records together to get as much data on each unique object which may have had N records over time through the database).
It is UN-BELIEVABLE what actual businesses get away with.
An oil company which had an MS access DB and a form configured for it with no checks for formatting that would insert the fields of the form directly into the database, and then if they wanted to make a change, they would export the entire database as csv, open it in excel, make changes and use that to overwrite the entire database.
This had been going on since some time in the 1990’s. They finally wanted to move to a modernized databasing/operations solution which is what my company does.
I successfully cleaned 75% of that data, however it took 37 regular expressions and a script that was about 800 lines to account for every possible mis-entry, incorrect format, and merging fields if they were empty from newest records to oldest records until the fields were no longer empty where possible (essentially collapsing the records together to get as much data on each unique object which may have had N records over time through the database).
It is UN-BELIEVABLE what actual businesses get away with.