I have source file in which 90% of first field is empty. I want to load this file to SQL server table with BCP utility. When i run BCP command, BCP utility is not able to recognize or distinguish records.
My Source file has data as below.
|100168|27238800000|14750505|1|273
|100168|27238800000|14750505|1|273
|100681|88392930052|37080101|1|252
|101014|6810000088|90421505|12|799
|101595|22023000000|21050510|8|780
I am using
**bcp [DBNAME].[dbo].[TABLE1] in \\filelocation\filename -e \\filelocation\filename_Error.txt -c -t | -S ServerName -T -h TABLOCK -m 1**
I am getting error message in error.txt
as #@ Row 1, Column 28: String data, right truncation @# 100168 27238800000 14750505 1 273
100168|27238800000|14750505|1|273. Here BCP is not able to recognize records. Due to this BCP is trying loaded next record data into last field which is causing data truncation.
Table schema is
CREATE TABLE [DBO].[TABLE1](
FLD1 VARCHAR(10)
,FLD2 VARCHAR(10)
,FLD3 VARCHAR(22)
,FLD4 VARCHAR(15)
,FLD5 VARCHAR(10)
,FLD6 VARCHAR(12) )
You need to quote the pipe. Pipe (character |) is used for redirecting standard output for command lines
The following simplified line works with your sample
bcp.exe [db].dbo.[table1] in "path\Data.dat" -S".\instance" -T -c -t"|"
I omitted the error limit -m, log -e and table lock hint -h, but those should not affect the import, but if you still have an issue try quoting parameters like the server name and filenames
I used a text file with standard \r\n row terminators as expected by -c