Export CSV File from Azure SQL Databases

There are many ways to export CSV file from Azure SQL Databases. This article shows two approaches: bcp and sqlcmd commands.

Use bcp command

Command bcp full name is bulk copy program and its a utility tool with SQL Server different versions. The tool is available on Azure Cloud Shell. We can use this tool to export simple CSV that doesn't require double quotes or other complex transformations.

Steps

Follow the steps below to use bcp command.

1. Log in to Azure portal.
2. Click Cloud Shell.
3. Choose Bash.
4. Once initialized, we can use a command like the following to export data:
   

   bcp schema.object_name out ./data/file.csv -t "," -w  -S server-name.database.windows.net -U username -d database

   Replace the bold parts accordingly.

   The above command exports data from schema.object_name (can be view or table) to a local file named file.csv in data folder. As password is not specified, the program will ask for password input interactively. 

The following screenshot is one example:

Use SqlCmd command via PowerShell

If we want to be more flexible about the output CSV file format, we can use PowerShell Sqlcmd command.

Steps

1. Log in to Azure portal.
2. Click Cloud Shell.
3. Choose PowerShell.
4. Run Invoke-Sqlcmd command and then use Export-Csv to export.
   

   Invoke-Sqlcmd -Query "select * from schema.object_name" -ServerInstance "server-name.database.windows.net" -Database database -Username username -Password "your-pwd" -IgnoreProviderContext | Export-Csv -path ./data/file.csv -Delimiter "," -Encoding UTF8

   The above command performs similar action as the bcp command but with more extra features. For example, Export-Csv will double-quote all fields by default and we also specified the output file encoding too.

For more details about these two PowerShell commands, please find out more in References section. 

Move to Azure Blob Storage

The exported files are located in Cloud Shell (Azure storage account file share image). If you want to move the files to other blob storage containers, simply use az storage command.

Refer to az storage | Microsoft Docs for more details about this command. 

References

• bcp Utility - SQL Server | Microsoft Docs
• Invoke-Sqlcmd (SqlServer) | Microsoft Docs
• Export-Csv (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Docs