In this article, I’m going to show you how to create a batch file to export SQL query results to a Text file. I will also demonstrate how to add a button in MS Access 2016 to run the batch script.
While this article focuses on SQL Server, you may want to check the following source that explains how to use Spool in Oracle to export SQL query results.
To start, I’ll review a simple example to demonstrate how you can export SQL query results to a Text file.
Let’s say that you have a database in SQL Server, where:
- The Database Name is: TestDB
- The Table Name is: dbo.Person
This is the data within the dbo.Person table:
The goal is to export the results of the following SQL query using a batch script:
select * from TestDB.dbo.Person
This is the data that you’ll get when running the above query:
Let’s now review the steps to export the SQL query results to a text file.
Steps to Create Batch File to Export SQL Query Results to a Text File
(1) To start, open the Command Prompt:
(2) Then, type the following command: sqlcmd /?
This will give you a legend, for the command sqlcmd, which you can then reference when creating the batch command:
(3) Now, type the below command, and then press Enter (explanation to be provided below). Please note, that you may need to adjust the code to fit your server name, and any other parameter, such as your database name, and the location where the TXT file will be saved.
sqlcmd -S DORON\SQLEXPRESS -d TestDB -E -Q " select * from TestDB.dbo.Person " -s "," -o "C:\Users\Doron E\Desktop\Export.txt"
Next, I’ll explain each component of the code, so that you can adjust it to fit your parameters:
• sqlcmd is the command
• -S stands for the server name that you connect in SQL Server. Here, my server name is DORON\SQLEXPRESS
• -d is the database name. My database name is TestDB
• -E stands for trusted connection
• -Q is the place where you’ll need to put your query in quotations “select * from TestDB.dbo.Person”
This is how the query would look like in SQL Server:
• -s is for the column separator. In our case, it is going to be a comma “,”
• -o is the location where the file will be saved (as well as the type of file that will be saved at that location. In our case, it is a TXT file): “C:\Users\Doron E\Desktop\Export.txt”
Now let’s see how you can create the actual batch file to export the SQL query results to a Text file.
Create the Batch file
(1) First, open Notepad and then copy the following code (adjusted to your server name, and any other parameter, such as your database name, etc…):
@echo off sqlcmd -S DORON\SQLEXPRESS -d TestDB -E -Q " select * from TestDB.dbo.Person" -s "," -o "C:\Users\Doron E\Desktop\Export.txt"
(2) Next, save the Notepad file with the suffix extension of “.bat”
Here, I chose to save the file as “export.bat”
(2) Double-click on your batch file (i.e., the export.bat file), and your TXT file with the query results will now get generated:
Add a button in MS Access 2016 to run the batch script
To call the Batch script from Access, you’ll need to use the “Shell” command.
Let’s review the steps:
(1) In MS Access 2016, go to the Create tab, and then press on the Form Design to create a new form, where you will place your button:
(2) Under the Design tab, press on the “button shape” and then place your button on the form itself:
(3) Since we won’t be using the Command Button Wizard, then press Cancel:
(4) On the Property Sheet, select “[Event Procedure]” for the On Click option. Then, press on the 3 dots (“…”)
Please note that if you can’t see the Property Sheet, you can always populate it on your screen by pressing “F4” while in the Design or Layout Views.
(5) Type the following code, which includes the Shell command to run the batch script when pressing the button:
Let me explain the components in the brackets for the Shell command:
• The following portion represents the location where I stored the batch file: C:\Users\Doron E\Desktop\Batch\
• The following part represents the name of the batch file: export
• Finally, the last portion represents the file type of: .bat
(6) Save your work and then switch to the Form View, where you’ll be able to press the button to run your batch script from Access:
To take things one step further, you can even import your TXT file into Access (the TXT file will be created once you press on the button to run the batch script).
In fact, you can create another button (or use an existing one) to import your TXT file into Access using one click!
We just saw how to export SQL query results to a Text File. This method would work when dealing with SQL server. Yet, if you’re dealing with Oracle, you may want to check the following tutorial that explains how to export SQL query results using Spool.
We also saw how to use a batch file to export those query results. Batch files are indeed useful to work with. You may then want to visit the following tutorials that deal with Batch files:
- Generating backup files with timestamps
- Creating the Matrix effect
- Creating a batch file to run Python script
Finally, you can read more about sqlcmd by visiting the sqlcmd utility documentation.