Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gogetter
Starting Member
18 Posts |
 Posted - 2008-02-26 : 16:30:25
|
| I need to export a large amount of data from an SQL 2000 database table to a text file which will be imported into one of our vendor's mainframe. The problem I am running into is that I cannot seem to get the BCP utility to recognize the row seperating character that I am specifying. In other words, I need to break each result row into three sections in order for the mainframe to properly process the text file. So what I did was hard code a comma (,) as the row terminator within BCP. So my procedure looks like this: declare @dtDateEnd as datetimedeclare @FileDate as char(8)declare @FileName as varchar(100)declare @ServerName as varchar(30)declare @bcpCommand varchar(4000)select @dtDateEnd = convert(datetime, varValue, 1) from HistoryVariables where varName = 'DateEnd'set @serverName = rtrim(@@servername)set @FileDate = rtrim(convert(char(8), @dtDateEnd, 112)) -- Date format = yyyymmddset @Filename = '\\'+ @servername + '\SQLReports\MSN_'+ @FileDate + '.txt'SET @bcpCommand = 'bcp "SELECT * FROM msnpayrollhist..HistoryOut ORDER BY PayrollDate" queryout "' + @FileName + '" -S ' + @servername +' -T -c -t -r ,""'EXEC master..xp_cmdshell @bcpCommand When I execute this it exports the data fine but the row terminator is showing up as part of the data as opposed to seperating the rows. In the table my data looks like this: Col1;col2;col3;col4;col5; ',' Col6;Col7;Col8;Col9;Col10 ','But in the text file output the comma (,) is simply being added to the output of the query. I tried this with carriage return and that didn't help either. What am I missing here? |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-02-26 : 16:43:53
|
| When you specify comma to be the row delimiter as an argument in your bcp command(-r ,)...that means comma is the row delimiter to be used in the exported file and not that comma will be recognised to be a row delimiter in the data.bcp has nothing to do with the content of data.To have what you want,tweak your select statement. |
 |
|
|
gogetter
Starting Member
18 Posts |
Posted - 2008-02-26 : 17:00:49
|
| Thanks for the response. I thought that BCP would interpret the comma as the row delimiter whenever it encountered it? According to a Microsoft article I found it stated the need to use a character that did not appear within the results of your data for this very reason. How would you suggest tweaking the SELECT statement I am using? |
|
|
|
|
|
|
|
|
BCP Query Out Problem