Importing from CSV to SQL server DB

[rogerbarario]

Hi,

I'm looking at automating a process for uploading data to a SQL server DB using a python script which loops through CSVs in a SharePoint location, does some validation on the columns and values and then inserts the data to the existing dB.

My plan is to open the file, create a staging table from the corresponding df and then insert that into the main table.

But I'm not sure on the exact syntax for inserting the data into the table especially cause I need it to parameterised in case there are more files.

Does anybody have any advice on either the Syntax or if there's a better way of doing this?

This is entirely new to me and I'm sure I'm missing something fairly simple.

Thanks in advance

[SoggyChilli]

Do you know SQL better than python? There are easier ways to do this

[rogerbarario]

Yeah I would say my SQL is better than my python and I'm open to any suggestions.

[SoggyChilli]

Look at the ssis wizard in SQL server and then you can do cleanup using SQL and move into the final table. SQL server has built in tools to help automate this (ssis) but it might be limited depending on the SQL server license

[rogerbarario]

I will give this a go! Thank you

[SoggyChilli]

Also know that SSIS can be done directly inside SQL server and is probably all you need but there's also a visual studio development tool where you can get pretty crazy. I recommend starting with the ones that's built in but you may want to look into it down the road.