Troubleshooting BCP and Format File Errors

bcp format sql sqlbulkcopy

Question

First off, sorry for the long post. I wanted to be thorough with my examples/data, and the bulk of this post is just that.

I inherited a Bulk Import Process using a format file (.fmt) at my new job. This process was created by the guy that worked here before me, and it is my job to learn this process (and fix it now). I have limited knowledge of this stuff, but I have done some research. After a few weeks, I haven't really gotten anywhere. Here is what I am working with...

--BCP Command to import data from C:\Desktop\20180629_2377167_PR_NP.txt to table LA_Temp.dbo.ProvReg

bcp LA_Temp.dbo.ProvReg IN C:\Desktop\20180629_2377167_PR_NP.txt -f C:\Desktop\PROVREG.FMT -T -S SERVERNAME -k -m 1000000

--Table Structure which format file is created from:

SELECT [NPI]
  ,[D1]
  ,[EntityType]
  ,[D2]
  ,[ReplaceNPI]
  ,[D3]
  ,[ProvName]
  ,[D4]
  ,[MailAddr1]
  ,[D5]
  ,[MailAddr2]
  ,[D6]
  ,[MailCity]
  ,[D7]
  ,[MailState]
  ,[D8]
  ,[MailZip]
  ,[D9]
  ,[MailCountry]
  ,[D10]
  ,[MailPhone]
  ,[D11]
  ,[MailFax]
  ,[D12]
  ,[LocAddr1]
  ,[D13]
  ,[LocAddr2]
  ,[D14]
  ,[LocCity]
  ,[D15]
  ,[LocState]
  ,[D16]
  ,[LocZip]
  ,[D17]
  ,[LocCountry]
  ,[D18]
  ,[LocPhone]
  ,[D19]
  ,[LocFax]
  ,[D20]
  ,[Taxonomy1]
  ,[D21]
  ,[Taxonomy2]
  ,[D22]
  ,[Taxonomy3]
  ,[D23]
  ,[OtherProvID]
  ,[D24]
  ,[OtherProvIDType]
  ,[D25]
  ,[ProvEnumDate]
  ,[D26]
  ,[LastUpdate]
  ,[D27]
  ,[DeactivateRC]
  ,[D28]
  ,[DeactivateDate]
  ,[D29]
  ,[ReactivateDate]
  ,[D30]
  ,[Gender]
  ,[D31]
  ,[License]
  ,[D32]
  ,[LicenseState]
  ,[D33]
  ,[AuthorizedContact]
  ,[D34]
  ,[ContactTitle]
  ,[D35]
  ,[ContactPhone]
  ,[D36]
  ,[PanelOpen]
  ,[D37]
  ,[Language1]
  ,[D38]
  ,[Language2]
  ,[D39]
  ,[Language3]
  ,[D40]
  ,[Language4]
  ,[D41]
  ,[Language5]
  ,[D42]
  ,[AgeRestrict]
  ,[D43]
  ,[PCPMax]
  ,[D44]
  ,[PCPActual]
  ,[D45]
  ,[PCPAll]
  ,[D46]
  ,[EnrollInd]
  ,[D47]
  ,[EnrollDate]
  ,[D48]
  ,[FamilyOnly]
  ,[D49]
  ,[SubSpec1]
  ,[D50]
  ,[SubSpec2]
  ,[D51]
  ,[SubSpec3]
  ,[D52]
  ,[ContractName]
  ,[D53]
  ,[ContractBegin]
  ,[D54]
  ,[ContractEnd]
  ,[D55]
  ,[Parish1]
  ,[D56]
  ,[Parish2]
  ,[D57]
  ,[Parish3]
  ,[D58]
  ,[Parish4]
  ,[D59]
  ,[Parish5]
  ,[D60]
  ,[Parish6]
  ,[D61]
  ,[Parish7]
  ,[D62]
  ,[Parish8]
  ,[D63]
  ,[Parish9]
  ,[D64]
  ,[Parish10]
  ,[D65]
  ,[Parish11]
  ,[D66]
  ,[Parish12]
  ,[D67]
  ,[Parish13]
  ,[D68]
  ,[Parish14]
  ,[D69]
  ,[Parish15]
  ,[D70]
  ,[PCPInd]
  ,[D71]
  ,[DisplayOnline]
  ,[D72]
  ,[ExpAgeRestrict]
  ,[D73]
  ,[Suffix]
  ,[D74]
  ,[Title]
  ,[D75]
  ,[PrescriberInd]
  ,[Spaces]
  ,[End]

FROM [LA_Temp].[dbo].[ProvReg]

--Example Text File Data (this is one line) 9999999999 ^0^ ^ ^3800 HMA BLVD STE 305 ^ ^METAIRIE ^LA^70006 ^ ^5048729679^ ^3800 HMA BLVD ^ ^METAIRIE ^LA^70006 ^ ^9999999999^ ^207Q00000X^ ^ ^0000000^2001^ ^00000000^ ^00000000^00000000^F^ ^LA^ ^ ^ ^N^1^0^0^0^0^2^00000^00000^00000^ ^ ^ ^ ^ ^ ^000000000000000000000000000000^00000000^00000000^26^00^00^00^00^00^00^00^00^00^00^00^00^00^00^0^0^Accept patients of age 000-000^ ^MD ^ ^

--Format file

11.0

153

1 SQLCHAR 0 40 "\t" 1 NPI SQL_Latin1_General_Pref_CP1_CI_AS

2 SQLCHAR 0 2 "\t" 2 D1 SQL_Latin1_General_Pref_CP1_CI_AS

3 SQLCHAR 0 2 "\t" 3 EntityType

...all the way to...

153 SQLCHAR 0 2 "\r\n" 153 End

I have changed directories, servername, and some of the text file data to maintain security, however, it is very similar.

Here is the problem I am encountering: With the "\t" used in the format file I just created from the SQL table, I get the error: [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data-file.

If I change this to just "" or "^" (as I 'think' it should be since the text file is using carrot delimiter), the rows began to copy with error [Microsoft][SQL Server Native Client 11.0]String data, right truncation SQLState = 22001, NativeError = 0. BCP copy in failed.

If anyone can please point me in the right direction here for troubleshooting this issue, or if you see anything out of place, please let me know. As I mentioned, I have been at this for some time, and can use any suggestions I can get. Unfortunately, there is no one at my company I can ask about this.

Popular Answer

try adding the -e option to your bcp command. this will give you an error file in which BCP will write some samlpe lines from the file that it had problems with. Very helpful with troubleshooting the type of error you are getting now (you are correct to change your delimiter in the format file).

The error you are getting now "string data" and "truncation" is just as it states. However, this truncation can be occurring for a number of reasons. The destination table's columns may not be large enough to hold the data that is contained between the defined field delimiters. There may be delimiters appearing in your data and so this could be tricking the bcp utility into thinking a column has ended before it was intended to end in the file (this is less likely with the delimiter you are using... but ya never know... I always prefer fixed width if possible.). And, of course, the source of the data may very well have written you a file that contradict whatever agreed upon spec led you to define your destination as you have.

The error is accurate, teh trick is finding where. Use the -e option to allow BCP to capture problematic lines:

BCP table_dest IN "C:\FILE.TXT" -S SVR -T -f"C:\FORMAT_FILE.txt" -e"C:\ERROR_FILE.txt"

The "error_file.txt" will include line numbers and will include a sample of lines that it couldn't handle. Just copy and past to find in the file youare trying to load to see for yourself.

Strongly suggest using a more advanced text editing tool. Do not use windows notepad or wordpad. Use something like notepad++ or ultraedit to inspect ascii text files.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why