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
– Dom, Commented Jun 27, 2014 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
– bartover, Commented Jun 27, 2014 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
– mrpotocnik, Commented Jun 27, 2014 at 18:33

The_Black_Smurf · Accepted Answer · 2020-10-28 13:43:19Z

22

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.

edited Oct 28, 2020 at 13:43

The_Black_Smurf

5,29714 gold badges58 silver badges80 bronze badges

answered Jun 27, 2014 at 18:52

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EKOlog Over a year ago

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"

user3768157 Over a year ago

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

Collectives™ on Stack Overflow

Export SQL Server Data into CSV file

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related