Thursday, September 01, 2011

Bulk Insert: An unexpected end of file was encountered in the data file

I recently encountered a error in a BULK INSERT operation. The BULK INSERT was using a non-xml format file to skip some columns in the csv file and also to map the fields in the csv file with the columns in the table. Of course, I was importing into a temp table that servers as a staging environment. And wwhile doing so I got the foll. error:

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error.


On further investigation, I figured out that the issue was with the "Number of columns" in the format file. My csv file had 91 columns. The temp table had 104 columns. The format file was used to skip 15 columns. Initially I had "Number of Columns" at 46 (the value was hard coded from earlier days). This caused the "An unexpected end of file" error.

default non-XML format file for myTestSkipCol



While researching this error, this is what I found:
  • When "Number of columns" is less than the actual columns mapped, the error is:
Msg 4832, Level 16, State 1, Line 2Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 2The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 2Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
  • When "Number of columns" is more than the total number of columns in table or in format file, the error is:
Msg 4823, Level 16, State 1, Line 2Cannot bulk load. Invalid column number in the format file
  • When "Number of columns" is any number between the number of mapped columns and the total number of columns in the table, the BULK INSERT worked fine.
Apart from the above mentioned cause, there are many other causes for the "unexpected end of line" error. Some of these are:

  • In the data file, one or more rows have less columns that the expected number of columns.
  • Wrong field terminator mentioned in format file or the BULK INSERT command
  • Wrong row terminator mentioned in format file or the BULK INSERT command




17 comments:

Md. Shihab Uddin said...

how to solve this issue ?

Md. Shihab Uddin said...

how to solve this issue ?

Anonymous said...

@_DEADPresidentz i may be balck but i aint black under the light

------------------------------------------------------
lightsmade.com

Anonymous said...

-- generic cialis online
-- [url=http://buycialisonlinetoday.com/#bwww.blogger.com] generic cialis online
[/url] http://buycialisonlinetoday.com/#77052 -- cialis no prescription

Anonymous said...

[url=http://superonlinecasino24.com ]no deposit bonus [/url]physical basis, but it is time to know that abnormal states of Einzugs in die Hauptstadt zugesichert. online casino gratis startgeld

Anonymous said...

The Weight Watchers program was made around a good-term support system. Fad diets promote eating an individual food, limiting entire daily food groups or cutting calorie consumption to really low levels phen375 there is just not evidence what so ever that anaemia might be a unwanted effect. Appetite stimulants and multivitamin supplements may be given to revive appetite and enhance the body's ability to absorb dietary nutrients. Always set food products over a platter - Putfoods on a real plate, not only a Chinette plate, plus eat sitting at a table http://www.phen375factsheet.com so once we go to a spike inside the amount of cases being diagnosed, it's a fair assumption who's's caused by lifestyle choices. There will vary religions, different races, different cultures, and different languages [url=http://www.phen375factsheet.com]phen375 reviews[/url] contrary to popular belief, caffeinated and sugary drinks can certainly still promote good hydration, although less effectively as pure water.

Anonymous said...

http://site.ru - [url=http://site.ru]site[/url] site
site

Anonymous said...

[url=http://buyviagrapremiumpharmacy.com/#gorcg]generic viagra[/url] - buy viagra , http://buyviagrapremiumpharmacy.com/#kjfdz buy viagra

Anonymous said...

[url=http://buycialispremiumpharmacy.com/#wmoji]buy cialis online[/url] - buy cialis , http://buycialispremiumpharmacy.com/#jvxic cialis online

Anonymous said...

http://www.msn.com/ - [url=http://www.msn.com/]msn[/url] msn
msn

Anonymous said...

order cialis online get cialis online
generic cialis
cialis 20mg tablets location
http://genericcialis-us.com/#44991 - cialis location

Anonymous said...

[url=http://buyonlineaccutaneone.com/#unnvn]accutane online without prescription[/url] - order accutane , http://buyonlineaccutaneone.com/#xguhq buy generic accutane

Anonymous said...

As well no credit check needed two paydays, here are suitable supply of finances available for you? The credit to be found in such short term payday loans are in the range of $80 to $1500. They should have a better credit ratings are loans along with waste your time and efforts. Same day loans One time it will get approved, the cash automatically provider accordingly, as soon as the along with return it back in the lender.

Anonymous said...

I believe that every states will have laws barring them out of prosecuting anyone. Each person's problem is different, thus try to call a possible to more affordable that a lot of removed for an amount about. Last but not least you could search for ways to promote some advantageous loan net study in the very competitive online marketplace. fast same day loans As long as debtor's current economical status is definitely sound, he may easily have approval form of hosting loans?

Coach Factory said...

true religion outlet, longchamp handbags, true religion, prada handbags, cheap michael kors, louboutin, michael kors, longchamp handbags, prada outlet, ray ban sunglasses, air jordan, true religion jeans, oakley sunglasses, tiffany and co, michael kors outlet, ray ban sunglasses, burberry outlet online, oakley sunglasses, nike air max, air max, michael kors outlet online, polo ralph lauren outlet, nike free, oakley sunglasses, michael kors outlet online, polo ralph lauren outlet, ray ban, air max, jordan shoes, coach factory, michael kors handbags clearance, nike free, nike roshe run, longchamp outlet, coach purses, toms shoes, nike air max, gucci outlet, nike shoes, michael kors, louboutin, louis vuitton handbags, tory burch outlet, coach outlet, coach outlet store

Coach Factory said...

hollister, hogan, mac cosmetics, mulberry, new balance shoes, nike huarache, abercrombie and fitch, rolex watches, lululemon, yoga pants, juicy couture outlet, timberland, juicy couture outlet, michael kors, mont blanc, nike air max, birkin bag, louis vuitton, vans, lancel, supra shoes, louis vuitton, nike free, abercrombie and fitch, ray ban pas cher, ferragamo shoes, nike trainers, oakley pas cher, ralph lauren, hermes, montre pas cher, roshe run, karen millen, north face jackets, nike roshe, wedding dresses, ralph lauren, reebok outlet, converse shoes, burberry, hollister, louboutin, beats by dre, longchamp, longchamp, louis vuitton, giuseppe zanotti, north face outlet, nfl jerseys, air max

Coach Factory said...

oakley outlet, canada goose outlet, chi flat iron, jimmy choo, valentino shoes, oakley sunglasses outlet, insanity, moncler, oakley vault, tn pas cher, cheap sunglasses, oakley, herve leger, ugg, moncler, marc jacobs, ugg boots, moncler, ugg boots, moncler outlet, gucci, mcm handbags, ghd, ralph lauren, canada goose, uggs on sale, canada goose outlet, babyliss pro, cheap oakley sunglasses, p90x3, canada goose, bottega veneta, cheap sunglasses, celine handbags, canada goose, soccer jerseys, instyler ionic styler, ugg boots, oakley sunglasses cheap, canada goose, ugg, asics gel, oakley sunglasses cheap, cheap oakley sunglasses, oakley vault, louboutin, oakley sunglasses outlet, soccer shoes, ray ban