Solved

bcp newline characters (\r\n)

Posted on 2002-04-24
19
4,140 Views
Last Modified: 2007-12-19
Hello All,

I am creating a format file out of my MS SQL 2000 table, by executing:
bcp <DBName>..<table> format <path>\_format.txt -f <path>\format.txt -S <server> -c -t "" -r \n -U<user> -P <pswd>

Note, that I am overriding the default Row-Terminator parameter with "\n" (newline) character. However, the bcp automatically prefixes the "\r" (carriage return) in front of my "\n" (newline). And in created format file I have "\r\n" instead of "\n".

The problem is that I do mean that row terminator is "\n" and not "\r\n". (This is how I get files from my provider.)

Does anyone knows how to turn off OR workaround this "artifical intelligence" and have "\n" as a row-terminator?

Regards,
  Andrey.
0
Comment
Question by:andrey070798
  • 10
  • 5
  • 3
  • +1
19 Comments
 
LVL 2

Expert Comment

by:GregJennings
Comment Utility
From BOL:

--------------------------------
A common row terminator used when exporting SQL Server data to ASCII data files is \r\n (carriage return, newline). Using both characters as the row terminator ensures that each row of data appears on its own line in the data file. However, it is only necessary to enter the characters \r\n as the terminator when manually editing the terminator column of a bcp format file. When you use bcp interactively and specify \n (newline) as the row terminator, bcp prefixes the \r (carriage return) character automatically.
--------------------------------

It looks like you might be able to do it by using a format file instead of specifying the parameters on the command line.
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
The above is an excerpt from Transact SQL help.

Yes. When I manually edit the format.txt and manually delete "\r" then everything works fine.

But I need an automated way for format.txt to have "\n" as a row-terminator character. Preferably using bcp...

Regards,
   Andrey
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
The above is an excerpt from Transact SQL help.

Yes. When I manually edit the format.txt and manually delete "\r" then everything works fine.

But I need an automated way for format.txt to have "\n" as a row-terminator character. Preferably using bcp...

Regards,
   Andrey
0
 
LVL 2

Expert Comment

by:GregJennings
Comment Utility
What do you mean when you say ...an automated way for format.txt to have "\n"...?
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
The goal is to have "\n" in format.txt without any human doing it manually.

I plan to have a scheduled tasks for various tables, therefore, I need to take care of this \r\n automatically.

I can not assume that there will be a person (or even 3-rd party application) 24-hour monitoring the directory and once the format.txt file appears, edit it.

The simplest way (as I see it) is to turn off the \r additions, or trick it somehow.

Regards,
   Andrey
0
 
LVL 2

Expert Comment

by:GregJennings
Comment Utility
I understand now.  I can't find any way to turn that off.

From where are you executing the bcp program?  Could you immediately after executing the bcp program also execute a small console app that would just read the format.txt file and do the replacement?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
According to BOL, \n is the default row terminator.  So, you could try removing the "-r \n" parameter.

This sounds weird, but it may also be because the file suffix is ".TXT". "Windows rules" for .TXT may be that its end-of-record marker is \r\n.  It sounds dopey, but maybe change the file suffix to ".ZZZ" and see if you have the same problem.

You might also specify an error file (-e) and see if provides any further info/msgs.
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
Currently I have a stored procedure that

Step 1. Creates a format file
Step 2. Uses the format file for data import

I beleive that there should be something in between these steps. Like executing the "small console app". What that app should be? Do I have to write my own?

There is also a way to "store the correct format file contents" in another table and do bcp OUT into the format file...

I am not sure which one to choose, and is there other ways to solve it.

Regards,
   Andrey.
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
To ScottPletcher:
No, file extension .zzz is not working.

Removing -r\n from command line still does not help, because "\r\n" is the DEFAULT row-terminator with -c option.

Regards,
   Andrey
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Accepted Solution

by:
GregJennings earned 200 total points
Comment Utility
Here's a small C program you can call between steps 1 and 2 above:


#include <stdio.h>
#include <string.h>


int main(int argc, char **argv)
{
   char *pch;
   char sin[1000];
   FILE *fp_in, *fp_out;

   if( argc < 3 )
   {
      printf( "SYNTAX:  fixrt <input file> <output file>\n" );
      return 1;
   }

   fp_in = fopen( argv[1], "rt" );
   if( fp_in == NULL )
   {
      printf( "Can't open file for input!\n");
      return 2;
   }

   fp_out = fopen( argv[2], "wt" );
   if( fp_out == NULL )
   {
      fclose( fp_in );
      printf( "Can't open file for output!\n");
      return 3;
   }

   while( fgets( sin, sizeof(sin)-2, fp_in ) )
   {
      pch = strstr( sin, "\\r\\n" );
      if( pch != NULL )
         {
         strcpy( pch, pch+2 );  // shift string left to cover \r
         //putchar( ':' );  // uncomment this line to see progress
         }
      fputs( sin, fp_out );
      //putchar( '.' );  // uncomment this line to see progress
   }

   fclose( fp_in );
   fclose( fp_out );

   return 0;
}


You call it like this:

fixrt inputfilename outputfilename

I compiled and tested it and it works.  I can email you the compiled version if you don't have a C compiler.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You can also download Borland's 5.5 version of the C/C++ compiler free.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Your comment about the default terminator is interesting.  My copy of SQL 2000 BOL states this about bcp -c:

"
-c

Performs the bulk copy operation using a character data type. This option does not prompt for each field; it uses char as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.
"
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
I found a 3-rd party utility at http://www.simtel.net/pub/msdos/txtutl/gsar110 that will replace "\r\n" with "\n" in my format.txt file.
Most likely I will put it in between step 1 & step 2, but not sure yet.

Are theere any other ways?

Regards,
   Andrey
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
All,
here is an SQL stored procedure that EMULATES bcp format option. ( to the certain extent of course :-) )

I am using this one, during Step 1, INSTEAD of "bcp format"
----------------------------------------------------------
create Procedure usp_ToolExportCDRFormat (
@vchrTableName       varchar (30),
@vchrFormatFilePath  varchar (255),
@vchrFormatFileName  varchar (255),
@vchrFieldDelimiter  varchar (10),
@vchrRowDelimiter    varchar (10),
@vchrUserName        varchar (30),
@vchrUserPassword    varchar (30)
)
As
set nocount on

declare
    @_vchrQuery                    varchar (1024),
    @_vchrSQLServerName               varchar (30)
   
select @_vchrSQLServerName = '<server name>'
   
select @_vchrQuery =
'Create View Tab_Format_View ' +
'as ' +
     'select ''8.0'' as Txt, 0 as Ord ' +
     'UNION '  +
     
     'select convert(varchar, count (*)) as Txt, 1 as Ord ' +
     'from INFORMATION_SCHEMA.COLUMNS where Table_Name = ''' + @vchrTableName + ''' ' +

     'UNION ' +
     'select ' +
         'convert(varchar, ORDINAL_POSITION) + char (9) + ' +
     'case when DATA_TYPE like ''%char'' then ''SQLCHAR'' ' +
     'when DATA_TYPE like ''%int'' then ''SQLINT'' ' +
     'else ''SQLCHAR'' ' +  
     'end + char (9) + ' +
     ' ''0'' + char (9) + ' +
     'case when DATA_TYPE like ''%int'' then ''12'' ' +
      'else convert (varchar, CHARACTER_MAXIMUM_LENGTH)' +
     'end  + char (9) +' +
     ' ''"'' + ' +
     'case ' +  
     'when ORDINAL_POSITION = (select count(*) from INFORMATION_SCHEMA.COLUMNS ' +
     'where Table_Name = ''' + @vchrTableName + ''')' +
     'then ''' + @vchrRowDelimiter + ''' ' +
     'else ''' + @vchrFieldDelimiter + ''' '  +
     'end + ''"'' +  char (9) + ' +
     'convert(varchar, ORDINAL_POSITION) + char (9) + ' +
     'COLUMN_NAME + char (9) + ' +          
     'case when DATA_TYPE like ''%int'' then ''""'' ' +
     'else COLLATION_NAME ' +
     'end  as Txt, ' +
     '1 + ORDINAL_POSITION as Ord ' +
'from INFORMATION_SCHEMA.COLUMNS ' +
'where Table_Name = ''' + @vchrTableName + ''' '
execute (@_vchrQuery)          
     


select @_vchrQuery = 'grant select on Tab_Format_View to ' +  @vchrUserName
execute (@_vchrQuery)          
     
     
select @_vchrQuery = 'master..xp_cmdshell '+char(39)+'bcp "select txt from <dbname>..Tab_Format_View order by Ord" queryout ' +
@vchrFormatFilePath + @vchrFormatFileName +
' -c -t\t -r\n -U ' + @vchrUserName + ' -P ' + @vchrUserPassword +
' -S ' + @_vchrSQLServerName +
char(39) + ', no_output '
execute (@_vchrQuery)          

drop view Tab_Format_View          
   
return
-----------------------------------------------------------

Thanks for everyone.
0
 

Expert Comment

by:gchavezr
Comment Utility
Did you resolve your problem....

I have a store procedure that I use for workaround the granted permisions of the limited access to xp_cmdshell (only members of the sysadmin role have access to xp_cmdshell using the SQL Server service account)
and permits you export a table using oleObjects. I saw the code and it permits change the Row delimiter with ascii code "select CHAR(92) + CHAR(110)".

If you have interested tell me and I will send you.

Greeting

gchavezr
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
Thank you gchavezr, i resolved my problem.
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
Thank you GregJennings for your effort.
0
 
LVL 1

Author Comment

by:andrey070798
Comment Utility
This is not the solution but I appreciate your effort and ideas.

Finally I am using a SQL stored proecedure that EMULATES [bcp format], you may find text in comments.

Best regards,
   Andrey
0
 
LVL 2

Expert Comment

by:GregJennings
Comment Utility
Thanks, andrey, and good luck!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now