I'm running webscrapes via python which are retrieving data from csv's hosted on the web. I'd like to pass the data into a MSSQL database. An issue I have is the mixed elements/data types in the csv. Here is an example of the data
Item Val1 Val2 A 100 200 B 101 201 C Null -2/2(%) D Null 2019-Nov-18
I would like to import all of this data into the db, but the critical data is in the "Val2" column. It contains numbers (formatted as numbers and general), dates, and strings with numbers as above.
I would appreciate your thoughts on two things:
- After I get this data into python, whats the best way/package to use to send it to SQL
- Do I need to set up the SQL table in a certain way to handle the mixed elements? Should I filter those different values out with python and put them somewhere else?
For 1, I've looked at things like
pd.to_sql and iterating over rows and sending via pyodbc. Data won't be particularly large to begin with. I'll be setting up a lot of scrapes so just want some ideas on the "best" way to do this transfer so I don't have to re-design them all in the future
Thanks I know this is open ended but would love to hear any thoughts on the "right" way to do this