PowerShell script to create CSV file from SQL Table

In this article, we will create a CSV file from SQL Database using PowerShell script.

Requirement

Suppose there is a table in the SQL Server database and it contains more than 50,000 records. Now we want to create a CSV file and put all 50,000 records in this file and that should be an automatic process.

Solution

We will write a PowerShell script that will dump the records from the SQL table to CSV or excel file. And that PowerShell script can be scheduled to run at a defined time.

How to create a PowerShell script to create csv file from SQL table ?

Write below PowerShell script into a text file and save the file with the extension .ps1. ps1 is the file extension of PowerShell. Make sure to start SQL Query from the very next line as shown in the below code snippet.

$DBName = "CompanyDB"
$FilePath = "C:\\Data\\File1.csv"
$SQLQuery= @"
select  EmpId, EmployeeName, Department, JoiningDate
from EmployeeTable
"@

Invoke-Sqlcmd   -ServerInstance <IPAddress\InstanceName> -Database  $DBName -Query $SQLQuery | Export-CSV $FilePath -NoTypeInformation

Once you will run this PowerShell file, you may see your data in the specified file.

How to schedule PowerShell script to run on scheduled time?

In order to execute the PowerShell script on a scheduled time, we will create a batch file and through this, we will run PowerShell.

Create a file with the .bat extension.

Powershell.exe -File C:\Powershell\<PowerShellScriptFileName>.ps1

Now, in the task scheduler, we can schedule the batch file and as result, it will run the PowerShell script.

Note: You can create an Excel file with the .xlsx extension instead of a CSV file.

You may also read this – PowerShell Script to Export SharePoint List to Excel or CSV