SQL Server Excel Export from Table or Query data

ZappyShell for DB can export your SQL Server data to Excel, PDF, Excel, CSV in one line command. This is just single exe less than 5 MB. Excel export allows you to export data in existing or new excel file. You can specify sheetname and Cell Location where you want to start writing data. Append mode can automatically add new data in the existing workbook. Download here. Now Lets check how to do this.

Export Tables to CSV Using – SSIS Export CSV File Task

If you have ETL workflow defined in Microsoft SSIS then you can use SSIS Export CSV Task rather than Command Line approach defined above.

Dynamic SQL Table Export Using SSIS Export CSV File task

Download and Install ZappyShell Command Line Tools

Now next step is we need to Download and Install ZappyShell Command Line tools from hereYou can Download MSI Installer or XCopy Zip file which you can simply extract and copy (see below).

ZappyShell Command Line Tools – Export Excel, Excel, CSV, PDF

SQL Server Excel Export – Single table

c:\tools>db.exe export customers --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Multiple tables

In the following example we are exporting 3 tables (seperated by vertical bar)

c:\tools>db.exe export customers|orders|products --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Multiple tables (using pattern)

In below example all tables where name staring with cust will be exported from sales schema.

c:\tools>db.exe export cust% --schema Sales --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – SQL Query

In the following example we are exporting output of SQL Query to Excel file. If you have complex query with multi line then you can use Map file with one dataset node.

c:\tools>db.exe export "select * from customers where country like 'US%'" --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Split Files and Tabs

Sometimes you may have need to split large amount of data into multiple files. With ZappyShell command line you can split data three different ways. Lets check each example.

SQL Server Excel Export – Split By Row Count

In below example we are exporting SQL Server customer table to Excel files and splitting files with maximum 1000 records per file option.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --split --splitrows 1000 --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Split By File Size

In below example we are exporting SQL Server customer table to Excel files and splitting files with maximum file size = 10MB option. You also use other size specifier like KB, GB, BYTES etc.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --split --splitsize 10MB --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Split By Group By Column

In this example we have exported customers in to separate file by country.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --groupby Country --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Table to Excel, Split files by Country Column

SQL Server Excel Export – Split sheets By Group By Column

In this example we have exported customers into separate sheet by country.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --groupby Country --excel-split-ontab --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

ZappyShell – Export SQL Server to Excel Split on tabs

Exporting Compressed Excel files

If you want to output compressed Excel (GZIP format) then use below command.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --gzip --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

Using date time place holders in Excel file name

If you want to use date time place holders in file name so it automatically uses specified date time parts use below command simply putting specifiers in file name anywhere.

c:\tools>db.exe export customers --out c:\data\cust_{{YYYY}}_{{MM}}_{{DD}}_{{HH24}}{{MI}}{{SS}}.xlsx --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

Conclusion

In this article we have gone through few ways to use ZappyShell Command Line Tools to export data from SQL Server to Excel. You can also use same command line to export in CSV, JSON, PDF format.

Reference Links

Download ZappyShell

Introduction

The problem

Solution

Export Tables to CSV Using – ZappyShell Command Line Tools