Python use case – Export SQL table data to excel and CSV files – SQL Server 2017

In this post, we are going to discuss how we can export SQL Server table data to an Excel file or to a CSV file using Python’s pandas library. Prior to SQL Server 2017, we could use one of the below methods to export data from SQL Server to Excel or CSV file:

1. Create an SSIS package to export the data from SQL Server – This option can be a good choice if we want to reuse the export process again and again. Also, if we want to put moderate/complex transformations during data export, this option can be a better choice.
2. Use SQL Server Import/export wizard – SQL Server provides in-built data export/import wizard which can be used in case we want to export data with no/minimal transformations.
3. OS-based copy-paste functionality – We can simply copy the query output from SQL Server using Ctrl + C option and then open a blank excel worksheet and paste in it. However, this is a bit cumbersome especially if the data size is big.

With the integration of Python in SQL Server 2017, it has become very easy to export and import data from SQL Server using Python’s pandas library. Pandas is a very powerful Python library used for data analysis and data wrangling. It has many methods which are very handy in data manipulation. Let’s use pandas library to export data from SQL Server to an Excel and CSV file.

Export data from SQL Server to Excel file

Suppose we have a table named EmployeeMaster and want to export the table data into an Excel file. Below is the sample table and its data:

SELECT * FROM TestDB.dbo.EmployeeMaster

To create the above table with its sample data, we can use below code:

--Create demo employee master table
CREATE TABLE TestDB.dbo.EmployeeMaster
(
	BusinessID int NOT NULL,
	NationalIDNumber nvarchar(15) NOT NULL,
	LoginID nvarchar(256) NOT NULL,
	JobTitle nvarchar(50) NOT NULL,
	BirthDate date NOT NULL,
	MaritalStatus nchar(1) NOT NULL,
	HireDate date NOT NULL,
	Gender nchar(1) NOT NULL
)
 
--Add sample rows in the table
INSERT INTO dbo.EmployeeMaster(BusinessID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, Gender)
VALUES (1, '295847284', 'adventure-works\ken0', 'Chief Executive Officer', CAST('1969-01-29' AS Date), 'S', CAST('2009-01-14' AS Date), 'M'),
(2, '245797967', 'adventure-works\terri0', 'Vice President of Engineering', CAST('1971-08-01' AS Date), 'S', CAST('2008-01-31' AS Date), 'F'),
(3, '509647174', 'adventure-works\roberto0', 'Engineering Manager', CAST('1974-11-12' AS Date), 'M', CAST('2007-11-11' AS Date), 'M'),
(4, '605667284', 'adventure-works\john0', 'HR Head', CAST('1960-04-28' AS Date), 'S', CAST('2012-01-12' AS Date), 'M'),
(5, '745797967', 'adventure-works\adam0', 'VP Business', CAST('1975-08-01' AS Date), 'S', CAST('2008-01-22' AS Date), 'F'),
(6, '809646574', 'adventure-works\michal0', 'CTO', CAST('1978-11-12' AS Date), 'M', CAST('2007-11-26' AS Date), 'M')

Now, to export the data into an Excel file, we can use below python code in SQL Server 2017:

--Export data to an excel file
EXEC sp_execute_external_script
@language = N'Python',
@script =
N'
#Line 1
import pandas as pd
 
#Line 2
OutputDataSet = InputDataSet

#Line 3
OutputDataSet.to_excel("D:\\Test\\EmployeeMaster.xlsx", sheet_name = "EmployeeMaster", header = True)
',
@input_data_1 = N'SELECT BusinessID, NationalIDNumber, LoginID, JobTitle, 
					CONVERT(VARCHAR(20), BirthDate, 106) AS BirthDate, MaritalStatus, 
					CONVERT(VARCHAR(20), HireDate, 106) AS HireDate, Gender FROM dbo.EmployeeMaster'
GO

Handling error – Unsupported input data type in column

In case, you are getting an error like this:

Msg 39004, Level 16, State 20, Line 27
A ‘Python’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 27
An external script error occurred:

Unsupported input data type in column ‘ColumnName’. Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary.
SqlSatelliteCall error: Unsupported input data type in column ‘ColumnName’. Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary.

We need to convert the unsupported datatypes into the supported data types. In our case, we have “date” data type which is currently not supported and that is why we have used the CONVERT clause in our input SQL query to change these columns into varchar data type.

Handling error – Permission denied

In case you are getting permission denied error during the execution of the above script:

Msg 39019, Level 16, State 2, Line 47
An external script error occurred:
PermissionError: [Errno 13] Permission denied: ‘D:\\Test\\EmployeeMaster.xlsx’

SqlSatelliteCall error: Error in execution. Check the output for more information.

To fix this error, we need to go to the destination folder, right-click on that folder and click on the Security tab. Then, we can allow Full control access to Everyone or to the MSSQLServer Service account on that folder to fix this issue.

Output

Once we execute the above script, it will generate an excel file at the given location which looks like this:

Export data from SQL Server to CSV file

To export the SQL Server table data into a CSV file, we can use the below python code:

--Export data to an excel file
EXEC sp_execute_external_script
@language = N'Python',
@script =
N'
#Line 1
import pandas as pd
 
#Line 2
OutputDataSet = InputDataSet

#Line 3
OutputDataSet.to_csv("D:\\Test\\EmployeeMaster.csv", header = True)
',
@input_data_1 = N'SELECT BusinessID, NationalIDNumber, LoginID, JobTitle, 
					CONVERT(VARCHAR(20), BirthDate, 106) AS BirthDate, MaritalStatus, 
					CONVERT(VARCHAR(20), HireDate, 106) AS HireDate, Gender FROM dbo.EmployeeMaster'
GO

Below is the CSV file which is generated when we execute the above code. We can verify the data by opening the CSV file in a text editor.

Thanks for the reading. Please share your inputs in the comment section.