BCP Utility to Import data from delimited file using Format file

Phaneendra Babu Subnivis 41

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

AmeliaGu-MSFT 13,986 Reputation points Microsoft Vendor

2021-03-05T09:45:53.04+00:00

Hi PhaneendraBabuSubnivis-7461,
Was your issue resolved?
Did the answers help you?
If not, please reply and tell us the current situation in order to provide further help.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

2 answers

Tom Phillips 17,741 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.
Please sign in to rate this answer.
Phaneendra Babu Subnivis 41 Reputation points

2021-03-02T14:02:42.02+00:00

Thanks Tom, I am trying to load data into Stage table which has exact same structure as that of the feed file. I am currently working on SQL Server 2019 on Linux.
Below is the command:
<Path_to_bin>/bcp <DB_NAME>.DBO.<Stage_Table_Name> IN <path_to_Data_Files>/<file_name>_20210302.CSV -c -F2 -t § -S <Server_IP_Address> -U <User_Id> -P <Password> -e <path_to_error_data>/<bad_file>_20210302.TXT
The above command works outside shell script but it is not working when I execute from shell script. Below is the error I am getting: #@ Row 2, Column 1: Unexpected EOF encountered in BCP data-file @#
Could you please throw some light on this? My guess is that it is to do with New line character but not sure how to fix that.

Tom Phillips 17,741 Reputation points

2021-03-02T14:35:32.107+00:00

Try adding "-r \r" to your command to change the row terminator to cr instead of cr\lf.

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15

Phaneendra Babu Subnivis 41 Reputation points

2021-03-03T10:12:02.863+00:00

Already tried, it didnt help Tom :(

Tom Phillips 17,741 Reputation points

2021-03-03T12:58:53.47+00:00

You will need to look at your file, likely with a hex editor, to determine the proper row delimiter.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Erland Sommarskog 115.6K 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
Please sign in to rate this answer.
Phaneendra Babu Subnivis 41 Reputation points

2021-03-03T05:18:43.047+00:00

Thank you Erland. I will check.

Oscar Castro 21 Reputation points

2021-03-03T14:45:54.63+00:00

Thanks for your help!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

BCP Utility to Import data from delimited file using Format file

2 answers

Your answer