Solved

bcp newline characters (\r\n)

Posted on 2002-04-24
19
4,366 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
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 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