I am doing some data scraping from websites, its quite a fairly simple task, getting data from some columns, however, those columns can be empty, strings, or numbers inter-mixed in one single column. Now my question is, what is the best strategy to bring that "dirty" data in a consistent way into a table or database schema for persisting? The main problem here is the different data types that I could encounter (lets leave out relationships, foreign keys etc. as those dont play a role yet). Setting every column as a string is bad because I would also do data analysis on numbers later.
Values can also be empty or have string values if they are not existent.. So, what is the best approach to clean those data if you want to persist them in a table, or is there a "general" best practice guide out there or a book that someone can recommend me?