bcp newline characters (\r\n)

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.
LVL 1
andrey070798Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GregJenningsConnect With a Mentor Commented:
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
 
GregJenningsCommented:
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
 
andrey070798Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
andrey070798Author Commented:
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
 
GregJenningsCommented:
What do you mean when you say ...an automated way for format.txt to have "\n"...?
0
 
andrey070798Author Commented:
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
 
GregJenningsCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
andrey070798Author Commented:
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
 
andrey070798Author Commented:
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
 
Scott PletcherSenior DBACommented:
You can also download Borland's 5.5 version of the C/C++ compiler free.
0
 
Scott PletcherSenior DBACommented:
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
 
andrey070798Author Commented:
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
 
andrey070798Author Commented:
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
 
gchavezrCommented:
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
 
andrey070798Author Commented:
Thank you gchavezr, i resolved my problem.
0
 
andrey070798Author Commented:
Thank you GregJennings for your effort.
0
 
andrey070798Author Commented:
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
 
GregJenningsCommented:
Thanks, andrey, and good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.