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
26|3|2008
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
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 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.
ASKER
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?