Error in Importing Pipe delimited data with BCP in SQL Server

Question

I am trying to import pipe delimited data with BCP in SQL server but getting error. I have tried all the possible solution on the internet but couldn't get through.

Sample data -

1000004178|Valentin|FR12562062232|Valentin|Che St Georges| | |Alfortville|FR|2021-06-28 16:26:58|2021-06-28 16:26:58
1000007913|INDUSTRIAS MOLINERAS Y AFINES DEL NORTE S.A.C.I.A.|Z|INDUSTRIAS MOLINERAS Y AFINES DEL NORTE S.A.C.I.A.|Bv. Hipolito Yrigoyen|1648| |Reconquista|AR|2021-06-28 16:26:58.827|2021-06-28 16:26:58.827
1000011097|MARTINI RINALDO|MRTRLD46E16C139J|MARTINI RINALDO|VIA MASSIMO D' AZEGLIO 3| | |CERRO MAGGIORE|IT|2021-06-28 16:26:58.827|2021-06-28 16:26:58.827
1000017707|Energo stroj OOO||?????? ????? ???|??. ????????, ?. 18, ????. 2| | |??????|RU|2021-06-28 16:26:58.827|2021-06-28 16:26:58.827

Table schema -

CREATE TABLE dbo.sample(
    [PartyID] [bigint] NOT NULL,
    [Name] [nvarchar](255) NULL,
    [VATNumber] [varchar](255) NULL,
    [LegalName] [nvarchar](255) NULL,
    [AddressLine1] [nvarchar](500) NULL,
    [AddressLine2] [nvarchar](500) NULL,
    [AddressLine3] [nvarchar](500) NULL,
    [City] [nvarchar](100) NULL,
    [Country] [nvarchar](100) NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL
    );

Enter the file storage type of field PartyID [bigint]: Enter prefix-length of field PartyID [0]: Enter field terminator [none]: |

Enter the file storage type of field Name [nvarchar]: Enter prefix-length of field Name [2]: 0 Enter length of field Name [510]: Enter field terminator [none]: |

Enter the file storage type of field VATNumber [char]: Enter prefix-length of field VATNumber [2]: 0 Enter length of field VATNumber [255]: Enter field terminator [none]: |

Enter the file storage type of field LegalName [nvarchar]: Enter prefix-length of field LegalName [2]: 0 Enter length of field LegalName [510]: Enter field terminator [none]: |

Enter the file storage type of field AddressLine1 [nvarchar]: Enter prefix-length of field AddressLine1 [2]: 0 Enter length of field AddressLine1 [1000]: Enter field terminator [none]: |

Enter the file storage type of field AddressLine2 [nvarchar]: Enter prefix-length of field AddressLine2 [2]: 0 Enter length of field AddressLine2 [1000]: Enter field terminator [none]: |

Enter the file storage type of field AddressLine3 [nvarchar]: Enter prefix-length of field AddressLine3 [2]: 0 Enter length of field AddressLine3 [1000]: Enter field terminator [none]: |

Enter the file storage type of field City [nvarchar]: Enter prefix-length of field City [2]: 0 Enter length of field City [200]: Enter field terminator [none]: |

Enter the file storage type of field Country [nvarchar]: Enter prefix-length of field Country [2]: 0 Enter length of field Country [200]: Enter field terminator [none]: |

Enter the file storage type of field CreatedDate [datetime]: Enter prefix-length of field CreatedDate [0]: Enter field terminator [none]: |

Enter the file storage type of field UpdatedDate [datetime]: Enter prefix-length of field UpdatedDate [0]: Enter field terminator [none]: \n

Do you want to save this format information in a file? [Y/n] n

Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Unexpected EOF encountered in BCP data-file

0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1

The above is the code which I'm trying and not working.

bcp dbo.[sample]  in C:\sample.txt -S <serverInstance> -U <username> -P <pwd>

trying the above bcp command.

theres simply too many different things that can cause this and you havent really given us enough information to assist with. Can you go through this similar page and see if any of it helps https://stackoverflow.com/questions/26339092/unexpected-eof-encountered-in-bcp — Doug Coats, Jun 28 '21 at 16:57
Can you please tell me how to import data with unicode characters using bcp? @DougCoats — vineet jaggi, Jun 28 '21 at 18:11
Does this answer your question? [Unexpected EOF encountered in BCP](https://stackoverflow.com/questions/26339092/unexpected-eof-encountered-in-bcp) — Ian Kemp, Jun 28 '21 at 21:43

score 0 · Answer 1 · answered Jun 29 '21 at 07:32

If by "Unicode characters" you mean that the sample.txt file uses a UCS-2 encoding then there's some work you need to do. The error message that you're reporting is likely because the sample.txt file starts with a U+FEFF byte order mark (i.e.: the bytes FF FE):

 [Microsoft][ODBC Driver 13 for SQL Server]Unexpected EOF encountered in BCP data-file

First Step: The bcp.exe tool doesn't understand byte order marks, so you can remove them from your file. You can do that with some PowerShell like this...

[Byte[]] $RawBytes = Get-Content "sample.txt" -AsBytestream -Raw
# Remove the U+FEFF byte order mark...
if ($RawBytes[0] -eq 255 -and $RawBytes[1] -eq 254) {
    $RawBytes = $RawBytes[2..($RawBytes.Length - 2)]
}
# Write the BOM-free result...
[System.IO.File]::WriteAllBytes("sample-nobom.txt", $RawBytes);

Second step: Generate a "BCP Format" file for Unicode import...

bcp dbo.[sample] format nul -f sample.fmt -w "-t|" -S <serverInstance> -U <username> -P <pwd>

Note:

the -t parameter specifies the field terminator character. We need to quote the parameter in this case because the | is a special command shell character.
the -w parameter enables Unicode character import/export

This will generate a sample.fmt format file similar to the following (your collation may be different):

14.0
11
1       SQLNCHAR            0       42      "|\0"        1     PartyID                      ""
2       SQLNCHAR            0       510     "|\0"        2     Name                         Latin1_General_100_CI_AS
3       SQLNCHAR            0       510     "|\0"        3     VATNumber                    Latin1_General_100_CI_AS
4       SQLNCHAR            0       510     "|\0"        4     LegalName                    Latin1_General_100_CI_AS
5       SQLNCHAR            0       1000    "|\0"        5     AddressLine1                 Latin1_General_100_CI_AS
6       SQLNCHAR            0       1000    "|\0"        6     AddressLine2                 Latin1_General_100_CI_AS
7       SQLNCHAR            0       1000    "|\0"        7     AddressLine3                 Latin1_General_100_CI_AS
8       SQLNCHAR            0       200     "|\0"        8     City                         Latin1_General_100_CI_AS
9       SQLNCHAR            0       200     "|\0"        9     Country                      Latin1_General_100_CI_AS
10      SQLNCHAR            0       48      "|\0"        10    CreatedDate                  ""
11      SQLNCHAR            0       48      "\r\0\n\0"   11    UpdatedDate                  ""

Third step: Import your (now BOM-free) sample data...

bcp dbo.[sample] in sample-nobom.txt -f sample.fmt -w -S <serverInstance> -U <username> -P <pwd>

See the bcp Utility reference for more information about commandline parameters/switches.

Error in Importing Pipe delimited data with BCP in SQL Server

1 Answers1