Automating File Loading Into SQL Server With Python And SQL - Part 1

Loading files into databases are not exactly exciting work. It can be tedious and boring work. Often times it is constrained by tools like SSIS that freak out when a small change is made.
This is one of the core roles of BI and data engineering teams. They need to load files from operational databases into data warehouses. They do this by developing pipelines that process and transform data.
One problem we see is teams have multiple file types, data spec versions, and data providers. Each of which requires a different pipeline just to load into their raw database.
This forces teams to manage 10, 20 maybe even 50 smaller pipelines that are all subject to the change of the data providers. In addition, each new data provider requires an entirely new pipeline to be developed. It also requires managing infrastructure around each pipeline in order to log and track data pipelines statuses. This can quickly become very difficult to manage and tedious to build.
Github

Contact us with questions

Watch our video on Automating Bulk Inserting With SQL

[s.m.ehsanulamin]

can you share this things for importing CSV files in mysql from a directory by using python while using load infile command ?

[sachingera]

Hello Ben, can you please tell me the best way to migrate data from SharePoint to SQL server? I will be using python for making automation scripts. Waiting for your reply. Thanks.

[ryantran]

nice article, thank Ben, do you have part 2 available yet?

[christopherparry]

thanks Ben, do you have a faster way to insert into sql other than 1 record at a time using insert. I am dealing with millions of records and this approach is slow.

[lolista]

thank you Ben. looking forward to more such videos. did you write all these things anywhere, like a blog post or something?

[lorileifer]

This is great! So can this work if you're trying to move tables of data (dumped into csv's) from one database to another?

[himanshunarang]

Hi Ben can you please share the git repository link where you have uploaded it

[oleng]

hi it might sound weird but where did the meta_raw_files and meta_raw_files_extract tables come from assuming we dont have the data loaded yet? sorry im new to this studd 😅

[stanislavezhevski]

Thanks for video, but isn't easier to use Pandas for ETL processes ?

[aviralojha]

Hey Ben, tell me how can I migrate data from on prem to vantage using python script?

[OBFinancial]

Where in your python or SQL scripts are the names of the SQL tables named ?

[Siyao.Diamour]

Thanks for the sharing. Just one suggestion. When you move your cursors too much, it is really distracting.

[mohitrai]

sir i want to record video through python and directly upload it on sql server . please anyone help me

[Crazyxxx]

You’re loading into SQL server. Use SSIS.

[taiphan]

I run fine in SSIS with python simple python scripts. but when i use import pyodbc on the script file it gave me an error with

[Execute Process Task] Error: In Executing "C:\Program Files (x86)\Python39-32\python.exe" "LoadingScripts.py" at "D:\MyDir\Testprocess", The process exit code was "1" while the expected was "0".


here is my script file
import sys, os
import pyodbc

print("Testd")


can you help?