Bulk Insert TSQL - Error Loading 1

[smedvid]

Really scratching me head on this one, hope someone can help. I do not have SSIS, DTS or other ETL Tool (i.e. Data Stage) at present, and need to load large text file (174 Cols) into SQL Server on a Daily Basis. Used Bulk Insert TSQL (automated via a Job), and worked great, until vendor added one more field at end (as col 174) with bar delimiters.

Also, I loaded file into single field, and appears to be 173 bar symbols per line. Which is correct.

Appears that the last column is shifting. and including bar symbol in the last column. Strangest thing I have ever seen. The file is pretty big (600,00o rows), and I am using a Hex Editor to view End of Line, and delimiters.

Real Basic TSQL - BULK INSERT

BULK INSERT  dbo.tbl_DataDropTemp 
FROM '\\Server\Daily and Weekly Reports\Data Drops\ProdDataDrop.txt' 
WITH ( FIRSTROW = 2,FIELDTERMINATOR = '|', BATCHSIZE = 10000, 
	MAXERRORS = 3,
ERRORFILE ='\\Server\Daily and Weekly Reports\Data Drops\ImportError.txt'  )

Any suggestions are greatly apprecited. I know old DOS BCP from days of old, and may give that a try. I have never used (or needed) a Format file, and not sure how to even create one. My preference is to try ot stick with the BULK INSERT TSQL, if possible...

I did confirm EOL is CR+LF, and Bar Symbol is Delimiter via Hex Editor.

One thought as I close question... Could the data have an embedded CR/LF in one or more fields (cols), such as a comments field where the user entered a soft CR+LF? That would throw off positioning?

btw, vendor did not send a test file so we could catch this... just pushed to production (new field 174); not a best practice!!! Always test before deploying to production!!!!

Thanks Again!

Steve



Steve Medvid
IT Consultant & Web Master

 

[yelworcm]

Well, I think I can replicate the problem in a simple way. Does the number of fields in the table match the number of fields in the file? It seems that once BULK INSERT hits the last column, it no longer looks for fieldterminators, but looks for the line terminator. If the field counts match on both sides, there is a small possibility that SQL Server is caching the field count somewhere.

 

[smedvid]

yelworcm,

Thanks so much for pointing at the obvious. Sometimes you can't see the forest through the trees. The real issue was that the vendor made changes to the import file and deployed it to production without notifying us; or even allowing us to review/test. I have since taken steps to ensure that will not happen again in the future.

And, a Star for your help and quick response!

Take Care,

Steve

Steve Medvid
IT Consultant & Web Master