Covert SQL Data into .CSV file

Harshil Shah

14y11.5k

Hello,

I want to Export SQL data into .CSV file from Query.can you give me advise how to do it.

Thanks in advanse.

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Harshil Shah13y ago
hi,

please refer,

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

http://www.codeproject.com/Articles/9495/A-Simplified-SQL-CSV-Import-Export-Functionality

http://www.virtualobjectives.com.au/sqlserver/saving_to_csv.htm

for header try View instead of query using SQL BCP.

write View like this

Select 0 Orders, 'Header Name' as Name
Union
Select ID, Name FROM TableName

hope this help.
0
Rajesh Kumar13y ago
Thanks for ur code, but the .csv file didn't get the column header. How to get column headers in that file.
0
Senthilkumar14y ago
Hi,

I think the user wants to export the data into csv from the query window itself.

He is not expecting to do in the code level. If i am right then its one step to do this.

write the query and get the result in the grid format.

right click on the result area

Save it as .csv

0
George eric14y ago
With the assient of Spire.Dataexport. You can Covert SQL Data into .CSV file
Below is the code
using (OleDbConnection oleDbConnection = new OleDbConnection())
     {
         oleDbConnection.ConnectionString = this.textBox1.Text;
         OleDbCommand oleDbCommand = new OleDbCommand();
         oleDbCommand.CommandText = this.textBox2.Text;
         oleDbCommand.Connection = oleDbConnection;
        using (OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
         {
             DataTable dt = new DataTable();
            da.Fill(dt);
             dataGridView1.DataSource = dt;
         }
     }
0
David Smith14y ago
Ok this works below from the command line, when I execute this in management studio, it is executing properly, but never writes the file to disk. But if execute the same bcp from the command line it works just find. Can some tell me why is not writing the file to local disk. And also how to include the headers from the table in the bcp, It only returns the rows and columns without the header fields.

BEGIN

DECLARE @sql varchar(8000)

SET @sql = 'bcp "SELECT * FROM ColorDatabase.dbo.ColorTable" queryout "C:\Temp\Example.txt" -c -t; -T -SXXXXXXXXXX'

Print @sql

exec master..xp_cmdshell @sql

END
0
Harshil Shah14y ago
Hi,

When i am using BCP,below error occur.

Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Error = [Microsoft][SQL Native Client]Login timeout expired
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].

Thanks in advanse.
0
Gohil Jayendrasinh14y ago
if you use SSMS 2008
then
Tools->Options->Query Results->Sql Server->Results To Grid
Quote strings containing list separators when saving .csv results
0
Vulpes14y ago
Here's a link to the documentation for the BCP utility which includes some examples:

http://msdn.microsoft.com/en-us/library/ms162802.aspx

As you'll see the first parameter is the name of the table which in your example will be ColorDatabase.ColorTable.

After 'out' you need the path of the text file you're going to create.

The other parameters should be self-explanatory.
0
David Smith14y ago
can someone break this syntax down to me, for some reason its not working
Lets say I have a Database call Named or called ColorDatabase and the table name ColorTable how to put this in the syntax below, I am struggling

BCP master..sysobjects out c:\sysobjects.txt -c -t, -T –S<servername>
0
David Smith14y ago
vuples i dont wont to it that that way, because it over a million records already, i would whether right a stored procedure directly to handle it because it because it would probably be faster.
0
Jignesh Trivedi14y ago
hi,

please refer,

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

http://www.codeproject.com/Articles/9495/A-Simplified-SQL-CSV-Import-Export-Functionality

http://www.virtualobjectives.com.au/sqlserver/saving_to_csv.htm

hope this help.
0
Suthish Nair14y ago
Another way you can directly export from an Stored Procedure, but this file will be stored on Server only. You need to write logic for fetching or downloading the file to user.
0
Shubham Srivastava14y ago
Thanks to give your attention for this query
0
Vulpes14y ago
First load your SQL data into a DataSet in the normal way.

You'll then (for preference) need to create a new CSV file for each DataTable in the DataSet.

You can call this method to save each DataTable to a file by specifying the filename and the separator (normally a comma) between columns:

static void WriteDataTableToCSV(DataTable dt, string fileName, char separator)
{

System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName);
for(int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];

for(int j = 0; j < row.ItemArray.Length; j++)
{
if (row.ItemArray[j] != null && !Convert.IsDBNull(row.ItemArray[j]))
{
sw.Write(row.ItemArray[j].ToString());
}
else
{
sw.Write("");
}

if (j < row.ItemArray.Length - 1)
{
sw.Write(separator);
}
else if (i < dt.Rows.Count - 1)
{
sw.WriteLine();
}
}
}
sw.Close();
}
0