SQL Table to Text Delimited File

David Smith

Cam some guide me on writing a stored procedure to export a table to a text delimited file step by step. I am going to make a table up. I want to export the entire table below to text fdelimited file. Path is going to be C:\Temp\ColorTable.Text Can someone assist

Table Name = ColorTable

ID	ColorSerial	ColorPart	ColorType

6 Replies

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

Vulpes14y ago
I'm not saying that there isn't a way to get the BCP utility to work correctly from within Management Studio. What I am saying is why bother when it works fine from the command line as it was designed to do?

.csv files are just a form of text file. It doesn't matter what the extension is - it can be anything you like including .txt.

The SqlBulkCopy class can only be used to copy data to a SQL Server database. It cannot be used to copy data from the latter to a text file.

When you're using the Process class you can use the Process.WaitForExit() method, with or without a timeout argument, to block the current thread until the process has terminated.

If you don't want to block the thread but to carry on with something else whilst you're waiting for the Process to terminate, you can handle the Process.Exited event. The EnableRaisingEvents property must be set to true before this event can be used.
David Smith14y ago
Can you give me the logic to use the SqlBulkCopy to copy data table to text file.

this works below, but once I execute the bat file I have no way of knowing when its down, what I am doing is copying all data to text files, then afterwards, delete from the database. So is there a way to know if the bat file has finished exectuting berfore deleting from the database.

      process.StartInfo.UseShellExecute = false;

      process.StartInfo.FileName = Directory.GetCurrentDirectory.;

      if (File.Exists(RapidViewClient.Properties.Settings.Default.ExamplePath))
      {
         process.StartInfo.CreateNoWindow = false;
           process.Start();
      }
David Smith14y ago
Vuples so you basically just told me that SQL Management Studio is a waste their time using the bcp, because it wont write to the file to disk, is that what you are saying lolololol. I can use either a .csv or a .txt file. The customer prefers .txt files. So can the headers happen with the text file. I am getting ready to check the link below.
Vulpes14y ago
I'm not in fact telling you to do it in code rather than using a stored procedure or the BCP utility.

I referred you to the previous thread for different ways of creating a CSV file. I had no idea at the time that you had a million records to process. If I had known, then I'd have suggested you use BCP which is designed for importing or exporting large numbers of records.

It's also a command line utility and should therefore be run from the command prompt rather than from within Management Studio, which is probably why the data file is not getting created when you try the latter.

Unfortunately, there isn't an option to include the column names as the first row of the CSV file. You could therefore just open the file and add them manually or use a hack I found in this thread:

http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an
David Smith14y ago
VUPLES: Can you tell me not to use the stored procedure instead. You are telling me to do it in code. Can you give me an explanation why. I am still learning. I know when I call it from the command line it operates fast, And I have over a million records.

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
Vulpes14y ago
Check this thread for various ways of doing this with or without writing a stored procedure:

http://www.c-sharpcorner.com/Forums/Thread/161804/covert-sql-data-into-csv-file.aspx