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?

closed as too broad by gnat, Christophe, Jörg W Mittag, Robert Harvey, amon Nov 25 '17 at 14:11

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • The best strategy is to find code in a well-established and well-maintained library that already does this for you. For example, the .NET framework has methods for parsing such strings into numeric values, and it already has the necessary internationalization support to make it work in different countries. You really don't want to write this code yourself, unless your specific scenario is dead simple. – Robert Harvey Nov 24 '17 at 21:42
  • 1
    Is your question about how to design a database schema for the storage of this data, or is it about how to do the actual parsing and clean-up to get the data into an existing, hopefully well designed schema? Please clarify. – Doc Brown Nov 24 '17 at 21:58
  • it is mor about the parsing and clean-up to insert the data in some consistent way into existing database-schema (however, due to the "dirty" data there exists still room in the interpretation of how to create the table data types, but I want to leave this out here). I am doing webscraping in my case and its rather quite simple data fields, however, if I encounter different values like numbers and strings together in a column or missing values... how should I proceed in that case? that was my initial question... – user2774480 Nov 24 '17 at 22:52
  • 1
    You will have to parse as strings first, and only convert to numbers later. Since you don't know how to handle those non-parseable strings initially, you'll have to have a tool that generates a report that lists all these non-parseable strings, and then you as a human programmer will implement code that handle them. Finally, integrate your enhanced parsers into the system to convert the remaining strings into numbers. – rwong Nov 25 '17 at 0:01

Values can also be empty or have string values if they are not existent.

Codd and Date were pretty adamant that if you don't know some numeric value (it is not existent) then you should store NULL in your relation.

If a column can have both numeric and string values, then it sounds like you're trying to cram too many concepts into too few columns. Consider adding the occasional column.

When storing scraped values into a VARCHAR, do try to do some validation, and raise an exception if it fails. Then your downstream analysis code can take advantage of the fact that, e.g., the DOW column contains 7 values 'Sun' .. 'Sat', so that code needn't worry about odd variants.

  • ok I agree, then I guess its unavoidable that some columns will have lots of "NULL" values.. but still better than inconsistency – user2774480 Nov 25 '17 at 14:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.