Covert SQL Data into .CSV file

Question

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

Harshil Shah · Answer

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.

Rajesh Kumar · Answer

Thanks for ur code, but the .csv file didn't get the column header. How to get column headers in that file.

Senthilkumar · Answer

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

George eric · Answer

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; } }

David Smith · Answer

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

Harshil Shah · Answer

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.

Gohil Jayendrasinh · Answer

if you use SSMS 2008 then Tools->Options->Query Results->Sql Server->Results To Grid Quote strings containing list separators when saving .csv results

Vulpes · Answer

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.

David Smith · Answer

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>

David Smith · Answer

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.

Jignesh Trivedi · Answer

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.

Suthish Nair · Answer

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.

Shubham Srivastava · Answer

Thanks to give your attention for this query

Vulpes · Answer

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 { DataRow row = dt.Rows[i]; for(int j = 0; j { if (row.ItemArray[j] != null && !Convert.IsDBNull(row.ItemArray[j])) { sw.Write(row.ItemArray[j].ToString()); } else { sw.Write(""); } if (j { sw.Write(separator); } else if (i { sw.WriteLine(); } } } sw.Close(); }

Covert SQL Data into .CSV file

Answers (14)