Avatar of jdana
jdanaFlag for United States of America

asked on 

Proper Syntax for Newline (-r) Argument

I'm trying to import series of pipe-delimited files from a third party calendar application called Office Tracker into SQL Server 2005 using BCP.  I'm in good shape EXCEPT for the Newline character (-r).  I've attached a copy of the import file (sample_calendar.txt).  The character that's giving me trouble is the row delimiter.  I pasted a sample of the data below.  It's between the "AM" and the "26".  Is it a carriage-return, line-feed, carriage-return/line-feed, or something else?

3/25/08 1:00 AM

I then tried to import it.  

1) I first tried to import the file replacing the mysterious row delimiter with an "Enter key" character in Notepad:

bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|"

This worked just fine.  I think my column delimiter argument (-t "|") is correct.  

2) I then took a crack at the true data file using the following commands:

bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|"
bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r "
bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r\r
bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r\n
bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r \n
bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r\0

All of them produced the following error:

SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation

3) I then took a crack at these commands:

bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r "r"
bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r "n"
bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r "0"

With these, I was able to import ONE row of data.  Then the compiler balked:

Starting copy...
SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

So it looks like the format 3 is the correct one, but I have no idea what argument goes with -r.

bcp [2005ElTesto].dbo.OTImport in "calendar.txt" -T -c -t "|" -r "<???WhatGoesHere???>"

I've attached a more complete summary of my BCP attempts (Attempts-at-newline.txt) in case it proves useful.

Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of jdana
Flag of United States of America image


I doesn't work if I omit the -r parameter.  

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation

However, if I copy and paste the data from Notepad into a new document using Wordpad, the import then works with an omitted -r parameter.  The Office Tracker row terminator is something funky.  Any idea how determine exactly what it is?  
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo