Exporting SQL Query Results to a CSV file using SQLCMD and C#

Exporting SQL Query Results to a CSV file using SQLCMD and C#

On 8 March, 2013 08:00:44, in Programming, by Dirk Strauss

Command Prompt to run SQLCMDToday a colleague asked me how to export SQL Query Results to a CSV file. This got me thinking that it would be a nice exercise to do this from a C# application.

So to start off, I downloaded the correct AdventureWorks database for my version of SQL from the following CodePlex page.

I attached the database and wrote a short piece of code to execute SQLCMD from C#.

The SQL command I wanted to execute is as follows:

SELECT BusinessEntityID,AccountNumber,Name,PreferredVendorStatus,ActiveFlag,ModifiedDate FROM AdventureWorks2008R2.Purchasing.Vendor WHERE CreditRating >= 3

The command that needs to execute from C# is thus as follows:


SQLCMD -S DIRK-DELL\SQLSERVER -d AdventureWorks2008R2 -U sa -P xxx -Q "SELECT [BusinessEntityID],[AccountNumber],[Name],[PreferredVendorStatus],[ActiveFlag],[ModifiedDate] FROM AdventureWorks2008R2.Purchasing.Vendor WHERE [CreditRating] >= 3 " -s "," -o "c:\temp\VendorResults.csv"

The format is as follows:

SQLCMD -S YourSQLServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\Yourfilename.csv”

Running this from  the command prompt will create a CSV file at the path you specified. So putting this all together, fire up your Visual Studio and create the following method.

private void RunProcess()
{
string FileName = “SQLCMD”;
string Arguments = @”-S DIRK-DELL\SQLSERVER -d AdventureWorks2008R2 -U sa -P xxx -Q “”SELECT [BusinessEntityID],[AccountNumber],[Name],[PreferredVendorStatus],[ActiveFlag],[ModifiedDate] FROM AdventureWorks2008R2.Purchasing.Vendor WHERE [CreditRating] >= 3 “” -s “”,”" -o “”c:\temp\VendorResults.csv”"”;

ProcessStartInfo proc = new ProcessStartInfo(FileName, Arguments);
proc.UseShellExecute = false;
proc.CreateNoWindow = true;
proc.WindowStyle = ProcessWindowStyle.Hidden;

Process p = new Process();
p.StartInfo = proc;

p.Start();
p.WaitForExit();
}

Running your application creates the CSV file in the path specified. Opening the file, you can see the results.

CSV File Output from C# SQLCMD

The uses for this are endless, and can extend the functionality of your applications tremendously with a few lines of simple C# code.

Reference: Pinal Dave

The following two tabs change content below.

Dirk Strauss

Software Developer
Dirk is a Software Developer from South Africa. He loves all things Technology and is slightly addicted to Twitter and Jimi Hendrix. Apart from writing code, he also enjoys writing human readable articles. "I love sharing knowledge and connecting with people from around the world. It's the diversity that makes life so beautiful." Dirk feels very strongly that pizza is simply not complete without Tabasco, that you can never have too much garlic, and that cooking the perfect steak is an art he has yet to master.

Latest posts by Dirk Strauss (see all)

Tagged with:  
  • Guillaume

    Hi Dirk,

    I want to develop a generic export module for a BI database. I find your code interesting but I wonder if this code can run in SSIS ?

    Thanks

    • Dirk Strauss

      I have never tried to do this myself, but this would make a nice project to try out. Have you given it a shot?

  • Chris

    Hi Dirk,

    In general there’s always the problem with the column separators when exporting data to a text file : you’ll always run into trouble when using a separator. Trust me: even if you use char(27), there will be somebody who’ll succeed to enter this character in the “comment” field :-(

    In my experience the best way is always to use fixed length columns. I know it’s a bit of a waste of disk space but it guarantees you problemless transfers between Windows-Unix-Apple-Mainframe.

    Have a nice week,

    Chris.

    • Dirk Strauss

      Hi Chris

      You’re absolutely right. Thank you for the feedback.

    • http://withparity.blogspot.com/ Chis Nelson

      Chris,

      If you need a delimiter character, the pipe, char(124), is usually the best choice, since it rarely appears in normal data fields. Fixed width data has it’s own set of challenges.

      • Dirk Strauss

        Hi Chris

        Thank you for your comment. I was trying to look for adding a tab character as the delimiter for the SQLCMD but unfortunately I was unsuccessful. I agree with you on the pipe, but was wondering how I could specify a tab delimited file with SQLCMD. Do you have any suggestions?

  • Chris

    What happens if you have a field which contains a comma in the data?

    • Dirk Strauss

      Hi Chris. You will need to sanitize the data. You will notice in the output in the screenshot, there is such a field with a comma.

  • Srikanth

    Hi, This article is nice to read and simple to understand.
    I have a similar requirement where I have to extract records from the database and write them to an excel file. I use BCP OUT for the same. It is simple but I ended up corrupting one field, like the account number that is shown in exponential format in Excel. Could you please suggest me an easy way to extract and not corrupt my fields in Excel?
    The method I use is: C# invokes the BCP command. BCP command executes query to output data to excel.

    Thanks in advance!
    Srikanth

    • Dirk Strauss

      Hi Srikanth

      Do you have a code example to illustrate your problem?

  • http://withparity.blogspot.com/ Chris Nelson

    The problem with this method is that the trailing white space isn’t trimmed from the fields and the character fields with commas are not enclosed with double quotes. (See record 13). So your file ends up being much larger than a “standard” or “clean” CSV file and harder to parse for many applications.