When Python Meets SQL–part 5: read/write multiple sheets from one excel file

After I finished re-testing this code, I suddenly realized that SQL connection and SQL commands did not appear. I am happy that this post in only name as part 5. The previous four posts all have SQL inside. This one is just to show how flexible pandas could be. When I had an excel file with several tabs and the data structure on every tab are same, this code was used to save time and to avoid mistakes:

Continue reading “When Python Meets SQL–part 5: read/write multiple sheets from one excel file”

When Python Meets SQL-part 3: load excel file data into SQL Server without creating table

At the beginning of my Python ETL journey, I created tables in SQL server and insert to those tables. Back then, I thought this is the only way. During my work using pySpark, I used pySpark to write SQL tables from pySpark dataframe. The best thing is I don’t need to create tables pySpark does all for me. (I will wrote pySpark codes later). That good experience makes me think: is there a better way in python? Is there a way to write data into SQL server without creating table?

My best friend google told me: yes. And here is how:

Continue reading “When Python Meets SQL-part 3: load excel file data into SQL Server without creating table”

When Python Meets SQL –part 2: loop to ETL multiple tables

In part 1 of this series, I mentioned loop after I failed to fast load data to sql server in my test code with my personal computer. In this post, I will show the advantage of using loop in python to move multiple tables/queries. The beauty of learning and using python is we can skip many mouse clicks in SQL server Import and Export Wizard. Let’s begin.

Continue reading “When Python Meets SQL –part 2: loop to ETL multiple tables”

When Python Meets SQL–part 1: move data between two SQL servers with fast load

I will organize several code that I have used in my daily work to do simple ETLs. This post will be first one: how to move table (or query result from one SQL server) to another. Even though the task could be handled pretty easily in SSIS and SS Import and Export Wizard, the python code provides the perfect starting point for SQL developers to use python.

Continue reading “When Python Meets SQL–part 1: move data between two SQL servers with fast load”