Solved

bcp newline characters (\r\n)

Posted on 2002-04-24
19
4,574 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 3
  • +1
19 Comments
 
LVL 2

Expert Comment

by:GregJennings
ID: 6966054
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
ID: 6966087
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
ID: 6966095
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 2

Expert Comment

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

Author Comment

by:andrey070798
ID: 6966203
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
ID: 6966266
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:Scott Pletcher
ID: 6966295
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
ID: 6966324
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
ID: 6966338
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
 
LVL 2

Accepted Solution

by:
GregJennings earned 200 total points
ID: 6966463
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:Scott Pletcher
ID: 6966505
You can also download Borland's 5.5 version of the C/C++ compiler free.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6966520
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
ID: 6966603
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
ID: 6967020
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
ID: 6967357
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
ID: 6968481
Thank you gchavezr, i resolved my problem.
0
 
LVL 1

Author Comment

by:andrey070798
ID: 6968489
Thank you GregJennings for your effort.
0
 
LVL 1

Author Comment

by:andrey070798
ID: 6968503
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
ID: 6968729
Thanks, andrey, and good luck!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server stored procedure parameter 10 20
t-sql, insert data in table normally, but read from view 3 30
Want an individual results display div 8 40
SQL eating up memory? 16 38
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

761 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