Convert Microsoft SQL Server Database Tables to CSV

We can convert and save Microsoft SQL Server database tables to CSV using python. I've recently written a python script to automate a process to convert Microsoft SQL Server database tables to CSV format and save it to drive so that I can use it later for other purposes. I've used pyodbc and pandas library to automate this process.

Firstly we have to establish a connection to Microsoft SQL Server database using pyodbc library.

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=ISRAJ\SQLEXPRESS;'
                      'Database=TEST_DB;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

Here we have provided server name, database name and if it has any username and password we have to add the credentials too.

After that we will query table names so that we can see the tables we have and can select the table name we want to convert to CSV.

sql = "SELECT TABLE_NAME FROM TEST_DB.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"

cursor.execute(sql)

We have written a query to select all the tables from database and by using cursor.execute we are executing the query.

Now we can loop through the table names:

for (table_name,) in cursor:
    print(table_name)

Output will be something like this:

Table_One
Table_Eight
Table_Two
Table_Three
Table_Four
Table_Five
Table_Six
Table_Seven
Table_Nine

Now we will use pandas to get the specific table data to dataframe.

import pandas.io.sql as psql

sql = "SELECT * FROM TEST_DB.dbo.Table_One"
df = psql.read_sql(sql, conn)

Here we have written another query to select all from Table_One and psql.read_sql will execute the query and convert the data into dataframe.

Now we will use pandas to_csv method to save the dataframe as csv.

df.to_csv("table_one_in_csv.csv", index=False)

Thanks For Reading!

If you want to test or get the code here I’ve added a simple command line tool to convert Microsoft SQL Server database tables to CSV.

https://github.com/IsrajurRahman/mssql2csv

Comments

[Muhammad Zohaib Khan]

I have good experience in this regard and many projects done if you consider i can do this your project