Skip to content

Instantly share code, notes, and snippets.

@hanssens
Created January 16, 2012 09:19
  • Star 14 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save hanssens/1619958 to your computer and use it in GitHub Desktop.
[C#] SQL Query to Databable to CSV
// Simple example for
// 1.) Read a sql server query to datatable;
// 2.) Export it to .csv
class Program
{
static void Main(string[] args)
{
var connectionString = @"data source=bla bla bla";
var selectQuery = "select * from my-table;";
var table = ReadTable(connectionString, selectQuery);
WriteToFile(table, @"C:\temp\outputfile.csv", false, ",");
Console.WriteLine("Press any key to quit...");
Console.ReadKey();
}
public static DataTable ReadTable(string connectionString, string selectQuery)
{
var returnValue = new DataTable();
var conn = new SqlConnection(connectionString);
try
{
conn.Open();
var command = new SqlCommand(selectQuery, conn);
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(returnValue);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
return returnValue;
}
public static void WriteToFile(DataTable dataSource, string fileOutputPath, bool firstRowIsColumnHeader = false, string seperator = ";")
{
var sw = new StreamWriter(fileOutputPath, false);
int icolcount = dataSource.Columns.Count;
if (!firstRowIsColumnHeader)
{
for (int i = 0; i < icolcount; i++)
{
sw.Write(dataSource.Columns[i]);
if (i < icolcount - 1)
sw.Write(seperator);
}
sw.Write(sw.NewLine);
}
foreach (DataRow drow in dataSource.Rows)
{
for (int i = 0; i < icolcount; i++)
{
if (!Convert.IsDBNull(drow[i]))
sw.Write(drow[i].ToString());
if (i < icolcount - 1)
sw.Write(seperator);
}
sw.Write(sw.NewLine);
}
sw.Close();
}
}
@Fuchida
Copy link

Fuchida commented May 14, 2014

@hanssens Thank for this gist, uber helpful saved me boat loads of time !

@NicuAhmadi
Copy link

Thank you for this gist! This was extremely helpful!

@SRM057
Copy link

SRM057 commented Oct 30, 2016

Thanks.. It was helpful

@gigabyte2177
Copy link

Thanks! Totally saved me time.

@cbirchy87
Copy link

Works well. However one of my cells has a , in it which is moving half the content to another cell. What can i do to over come this

@twgjhughes
Copy link

@cbirchy87, did you ever figure out how to accomplish this? Thanks...

Works well. However one of my cells has a , in it which is moving half the content to another cell. What can i do to over come this

@ankonina
Copy link

Thanks you so much!!

@warmfire540
Copy link

Thank you kindly for this!!

@thalaeg
Copy link

thalaeg commented Nov 13, 2020

Thanks @hanssens! Here is a little update if you want to add it.

In case you have commas in your data you need to escape.

Line 73

sw.Write(EscapeCommasInStringForCSV(drow[i].ToString()));

And then add this method

public static string EscapeCommasInStringForCSV(string input)
{
    if (input.Contains(","))
    {
        input = "\"" + input + "\"";
    }
    return input;
}

@stef61
Copy link

stef61 commented Nov 27, 2020

Thanks a lot for this code !

@SaleemVFX
Copy link

this worked so well thanks so much for this

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment