BCP Utility to Import data from delimited file using Format file

Phaneendra Babu Subnivis 41 Reputation points
2021-03-02T05:43:15.363+00:00

Hi,

The scenario which we have is a common one where data is to be imported from feed file into a table. Only catch is that the table structure is not aligned with the feed structure i.e., the table has 3 extra columns than the no. of columns existing in feed file.

So, I was trying the option of creating the format file using BCP command and then try to import the data. However, this is not working in consistent way. Sometimes it gives "Unexpected EOF encountered in BCP data-file", Sometimes it gives "String data, right truncation" error. Basically the behavior is not stable.

Request experts to help suggest the specifics or the basic points to take care in format file creation and Feed file creation so that this behavior is consistent.

PS: I had seen multiple scenarios which were resolved in the social.msdn forums and also the Microsoft Help articles. Some how I couldnt locate any article which explains the details to be complied from feed file and format file perspective which would be a fool proof solution.

Please help.

Regards,
Phaneendra

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,705 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-03-02T12:46:11.137+00:00

    You will always be happier with bcp, if you create a "stage" table which exactly matches the input with all fields as varchar fields. Then merge/insert/update the target table from the stage table.

  2. Erland Sommarskog 101K Reputation points MVP
    2021-03-02T23:13:53.47+00:00

    I have an article on my web site about BCP and BULK INSERT were I discuss format files in quite some detail. You may find it interesting: http://www.sommarskog.se/bulkload.html