Create CSV report from SQL database

Question

What is the best way to Create CSV report from SQL database?

Can this be done using visual studio C#?

Goal: Generate a CSV report weekly from SQL database.

Thanks

Answer 1

This task can be done in a huge variety of ways. To decide which consider the following:

• What tools and technologies are already in use?
• Do you need capabilities such as logging and alerting; so you know if something goes wrong & what/where it's gone wrong?
• Do you have multiple scheduled tasks / do you want them all managed centrally or is it OK for them to be defined in a variety of disparate locations
• Who needs to support this / what tools make this easy for them?
• What are the security risks & implications of each approach?
• What happens if a server goes down; do you need high availability?

Below I've listed a few simple approaches along with links to related content; but this is only to get you on track; as there is no one golden answer to this question; but many options to pick from, each with their own pros and cons.

SQL

You can use the SQL agent to schedule a task.

You can use xp_cmdshell to execute a BCP or SQLCMD call to export SQL data to CSV. An example using BCP can be found here: https://stackoverflow.com/a/43991158/361842 NB: Enabling xp_cmdshell is a security risk; since this runs tasks under the SQL service's service account; so may allow those with access to use xp_cmdshell to execute their own scripts which could then impersonate this account / giving those users access to privileges not assigned to their accounts.

The advantage of this method is it's 100% SQL; so no worries about additional dependencies.

PowerShell

Exporting from a database to CSV is trivial in PowerShell; it can be done with this single line of code:

Invoke-Sqlcmd -ServerInstance 'myServer\instance' -Database 'myDb' -Username 'user' -Password 'super$£cret' -Query 'select some, stuff from myTable' | Export-csv -NoTypeInformation -Path '\\server\share\path\file.csv'

To have this occur on a weekly basis, you can use the Windows Task Scheduler; just save the above to a file (e.g. c:\scheduledTasks\myTask.ps1) and create a new task with action defined as:

• Program: Powershell.exe
• Add Arguments: -ExecutionPolicy Bypass -File "c:\scheduledTasks\myTask.ps1"
• Start In: c:\scheduledTasks\

The advantage of this is PS is installed on most machines, it's quick and easy to run up, and it's very adaptable. The downside is you need to consider security; i.e. you don't want to put credentials in plain text so you'd either need to use Windows authentication or encrypt the crednetials; and you'd also need to ensure the directory hosting the scripts is locked down to avoid anyone amending your script for their own purposes, or simply to show them the unencrypted credentials.

C#

An example of a C# program to fetch data from a database and output to CSV can be found here: https://stackoverflow.com/a/24458994/361842

This is pretty good as the compiled file is less prone to attach than a script is (though remember the exe can always be swapped for a different exe if it's being run with the scheduler). To avoid that, you may want to write this to run as a service. More on that here: https://stackoverflow.com/a/503606/361842

Answer 2

If these are reports you are running manually, then I would recommend getting an SQL GUI tool (I use Navicat, but there are many other options). Most of these will allow you to define queries, and some even fairly advanced reports, which you can then easily run and export as a CSV.

If you want to run them automatically, then you will need to write some code to perform the task. I would suggest that Python would do this more easily than C#, but it is certainly doable in C#. To accomplish this you will need to find the appropriate C# API for your database (unless .NET has this built in - it has been awhile since I’ve worked in that arena), and use it to query the DB. Then creating the CSV file should be reasonably simple using the standard file writing classes available to you.