Search code examples
sql-serverbcp

BCP Format file to bulk import in SQL Server throws ODBC error

When I execute the below bcp command to import data from a .csv file into my table in SQL Server, I get an ODBC error.

Here is the command:

DECLARE @Error INT

EXEC @Error = master..xp_cmdshell 'bcp DB.dbo.tbl_CASHBAL IN "H:\Imports\CASH BAL.csv" -f H:\CASHBAL.fmt -S myserver -U user -P xxxx'

SELECT @Error

Here is the table structure in SQL:

CREATE TABLE [UBS].[dbo].tbl_BilotherCASHBALN 
(
    [DATE] VARCHAR(100),
    [SCODE] VARCHAR(100),
    [MY-ACC-N] VARCHAR(100),
    [YOUR-ACC-N] VARCHAR(100),
    [CASH-BAL] VARCHAR(100)
)

This is my format file:

12.0
5
1       SQLCHAR             0       100     "\t"     1     DATE                 SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       100     "\t"     2     SCODE               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       100     "\t"     3     MY-ACC-N              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       100     "\t"     4     YOUR-ACC-N              SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       100     "\r\n"   5     CASH-BAL             SQL_Latin1_General_CP1_CI_AS

And this is the error I get when I execute my bcp command:

Error = [Microsoft][ODBC Driver 11 for SQL Server]
Unexpected EOF encountered in BCP data file

I have included a screenshot of the complete error message here:

ODBC error.

And here is one row of data from the .csv file (which I need in the SQL Server table without double quotes):

"2021-01-30","IX","0001234567","XYZ01234","2305123.19"
Solution

  • Finally I was able to figure this out. I simply needed to modify the format file by adding one 'space' as the field terminator of my last column. I had not noticed that there was a space after my last column, and that there was a space after every last item in each row. Also, I needed to add one extra column in my format file that has double quote as the delimiter, and for this I set the column number to 0, so it is not imported. The final format file with proper field terminators looks like this:

        8.0
    6
    1       SQLCHAR             0       1    "\""         0   Unwanted         ""
    1       SQLCHAR             0       100     "\",\""     1     DATE                         SQL_Latin1_General_CP1_CI_AS
    2       SQLCHAR             0       100     "\",\""     2     SCODE               SQL_Latin1_General_CP1_CI_AS
    3       SQLCHAR             0       100     "\",\""     3     MY-ACC-N              SQL_Latin1_General_CP1_CI_AS
    4       SQLCHAR             0       100     "\",\""     4     YOUR-ACC-N              SQL_Latin1_General_CP1_CI_AS
    5       SQLCHAR             0       100     "\" "   5     CASH-BAL             SQL_Latin1_General_CP1_CI_AS

    And the execution line within SQL Server Management Studio looks like this (I added F 2 to skip the header:)

        DECLARE @Error INT
    EXEC @Error = master..xp_cmdshell 'bcp DB.dbo.tbl_CASHBAL IN "H:\Imports\CASH BAL.csv" -f H:\CASHBAL.fmt -F 2 -S myserver -U user -P xxxx'

    SELECT @Error