Export SQL Server Data into CSV file

Question

I am trying to export data into a csv file from sql server. I have looked for help online and other support forums, but I can't find out how to do this? I have written my own code, but it doesn't work - it just keeps on loading... and fails.

Please help. Here is the code I wrote.

  SqlConnection sqlCon = new SqlConnection("REMOVED");
  string fileName = "test.csv";
  SqlCommand sqlCmd = new SqlCommand();
  sqlCmd.CommandText = "Select * from products.products";
  sqlCmd.Connection = sqlCon;
  sqlCon.Open();

    using (var CommandText = new SqlCommand("select * from products.products"))
    using (var reader = sqlCmd.ExecuteReader())
    using (var outFile = File.CreateText(fileName))
    {
        string[] columnNames = GetColumnNames(reader).ToArray();
        int numFields = columnNames.Length;
        outFile.WriteLine(string.Join(",", columnNames));
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                string[] columnValues = 
                    Enumerable.Range(0, numFields)
                              .Select(i => reader.GetValue(i).ToString())
                              .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                              .ToArray();
                outFile.WriteLine(string.Join(",", columnValues));
            }
        }
    }
}
private IEnumerable<string> GetColumnNames(IDataReader reader)
{
    foreach (DataRow row in reader.GetSchemaTable().Rows)
    {
        yield return (string)row["ColumnName"];
    }
}

Are you able to decipher at least at what point it's hanging on? — Dom, Jun 27 '14 at 18:31
Basics first, give us some background so we can better understand the environment you're working in. Do you need to do that only once or regularly? Are you bound to using C# for that or you can turn to tools bundled with `SQL Server`? — bartover, Jun 27 '14 at 18:32
The one issue I see as a potential gotcha is the file you are trying to write. Where are you expecting the file "test.csv" to reside? Maybe mapping to the file by using an absolute path. — mrpotocnik, Jun 27 '14 at 18:33
There is no error it just doesn't do anything it loads for 2minutes and doesn't download the csv file. — user3768157, Jun 27 '14 at 18:39

score 20 · Answer 1 · edited Oct 28 '20 at 13:43

For what it's worth, if all you want is to take a query and dump the contents somewhere, it looks like you're doing a bit more work than you have to. The complexity may add to the challenge in debugging.

A really bare bones example of reading a query and directing output to a file might look like this:

SqlConnection sqlCon = new SqlConnection("REMOVED");
sqlCon.Open(); 

SqlCommand sqlCmd = new SqlCommand(
    "Select * from products.products", sqlCon);
SqlDataReader reader = sqlCmd.ExecuteReader();

string fileName = "test.csv";
StreamWriter sw = new StreamWriter(fileName);
object[] output = new object[reader.FieldCount];

for (int i = 0; i < reader.FieldCount; i++)
    output[i] = reader.GetName(i);

sw.WriteLine(string.Join(",", output));

while (reader.Read())
{
    reader.GetValues(output);
    sw.WriteLine(string.Join(",", output));
}

sw.Close();
reader.Close();
sqlCon.Close();

While it may not look dramatically shorter than the code you listed, I do think it's simpler and will be easier to debug, out of the box. I haven't tested this, so I can't say for certain it works, although I would think it's pretty close.

Another thing worth mentioning... neither of these is true CSV output. You need to be sure you handle embedded commas, return characters, etc, should they be in any of the output. That's easy enough to do, though.

Chris, You'r right, but... There could be another schema, not dbo, then you must use this schema. In your answer query "Select * from products.products" is OK if first "products" is schema name not database name and second "products" is a table name. Otherwise, query must be rewriten to "Select * from products.dbo.products" — EKOlog, Jun 27 '14 at 20:43
It worked but it outputs an HTML format and it loads up to be 25mb is there anyway to make it strictly text?? UPDATE: Nevermind it doesn't work — user3768157, Jun 27 '14 at 20:57

Export SQL Server Data into CSV file

1 Answers1

Linked