1
$\begingroup$

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:

  1. After I get this data into python, whats the best way/package to use to send it to SQL
  2. 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

$\endgroup$
  • $\begingroup$ I think the easiest way would be to store all data in column 2 as strings, that way you can store all data in the database and if you need to get the data in a different you should be able to later on. I've personally used sqlite as a database in combination with python and it has worked really well. $\endgroup$ – Oxbowerce Nov 22 '19 at 22:07
1
$\begingroup$

One way would be to declare that column as a character type. I think this would be a poor solution as it forces all interpretation and processing of the data back onto the application. Better would be to declare Val2 as datatype sql_variant. At least then the DBMS has a fighting chance of interpreting the value correctly.

Since each of those datatypes represents a different attribute with unique semantics it would be better to separate them into their own columns. SQL Server has the TRY_PARSE function which allows you to discover the datatype of each value and assign it to its proper column.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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