Applies to
Summary
This article explains how to export data from a SQL database to the Excel file and how to set some basic export settings for that file.
Description
ApexSQL Pump is a tool that can export data from SQL Server database to 13 commonly used data formats (CSV, Excel, Word, XML, JSON, etc.). Each of these file formats has its own set of options that allows users to configure data export. These options can be saved as a template for later use.
In order to export data, the Export action should be selected in the New project window under the Action tab:
After clicking the OK button, the main application window for selecting tables and/or views will appear. Here the SQL data can be previewed before the exporting process is started.
The next step is to select the Manage button under the Home tab in the main application window:
This will open a new Manage export window for selecting file formats for selecting rows to export and setting SQL scripts that will be executed before and/or after the exporting process.
In the Manage export window, under the Formats tab, different file formats to export data from the SQL database can be selected.
For this example, the MS Excel export format file will be used. When the MS Excel format is chosen, on the right side to this file format will appear the Settings button:
By clicking on the Settings button next to the MS Excel file, the MS Excel options window will open with two tabs, where the options can be set. In this way, it can be chosen how the data will be exported.
Basic tab
Under the Basic tab, two different Excel file versions (.xlsx and .xls) can be chosen depending on which version is wanted to be used for the export.
The appearance of the exported table can be configured by setting the table header font style and table borders options under the Header and Borders sections.
In addition to this, it can be chosen to export multiple tables/views in one Excel file with multiple sheets for each table/view by selecting the Separate files radio button, or to export each table/view in a separate Excel file with using of the One file option:
Additional tab
Under this tab, the font style for data rows can be configured. Furthermore, page settings can be set, such as orientation, margins, and page size.
Both basic and additional options can be saved as a template by clicking on the Export button and used later by clicking Import button:
Note: The Preview button will open a file sample with configured settings, in this case, an Excel file.
The next tab in this window is the Rows tab, here can be selected to export all rows or a range of rows, depending on what is needed to be exported. For this case, the option All rows will be used:
The last tab in the Manage export window is the Additional scripts tab. If a user wants to add, change or delete data before and/or after export data from SQL database into a selected file, here the SQL script for manipulating the data in the SQL database can be used. The pre-processing script will be used before, while the post-processing script after exporting the data:
After setting these export options in the Manage export window and selecting tables and/or views in the main application window, it can proceed with the export step by clicking the Process button in the Home tab in the main application window:
By clicking the Process button, the Job summary window will appear. Here, users can see some basic information before the exporting process begins. In order to proceed, the Export button should be clicked, and data will be exported:
When the exporting process is finished, the View results window will open where the results can be seen:
This is how the Excel file with the export data from SQL database will look like when the process is finished:
The Excel file contains every selected column and row which are chosen during the export set up.
