cannot bulk load. Invalid column number in format file

twilcox197805 1 Reputation point


Am trying to upload/import a fixed width file. It is 122 columns and is fixed width. I get the error. I have verified that there are in fact 122 columns in the table and that the numbers line up in perfect ascending order in the formatfile. Any thoughts as to why I get this error?

"Cannot bulk load. Invalid column number in the format file "C:\Users\someone\Desktop\formatfileequi-2.txt".

create table equi2022(
Transcode char(1),
Stfips char(2),
Year char(4),
qtr char(1),
uiacct char(10),
run char(5),
Ein char(9),
Presesaid char(10),
Predrun char (5),
Succuiacct char(10),
Succrun char(5),
legalname char(35),
tradename char(35),
uiaddr1 char(35),
uiaddr2 char(35),
Uicity char(30),
Uistate char(2),
Uizip char(5),
Uizipx char(4),
pladdr1 char(35),
pladdr2 char(35),
plcity char(30),
plstate char(2),
plzip char(5),
plzipx char(4),
moaddr1 char(35),
moaddr2 char(35),
Mocity char(30),
Mostate char(2),
Mozip char(5),
Mozipx char(4),
moaddtype char(1),
Rptunitdes char(35),
phonenum char(10),
Setupdate char(8),
Initliabda char(8),
Endofliabd char(8),
Reactdate char(8),
Status char(1),
Cesind char(1),
Refileresp char(2),
Refileyear char(4),
Oldcnty char(3),
Oldown char(1),
Blank char(4),
Oldtown char(3),
Maxrun char(5),
Mwrmailiand char(1),
Oldnaics char(6),
Datasource char(1),
Specialind char(1),
Agentcode char(4),
Blank2 char(4),
Nsta char(6),
naics char(6),
ownership char(1),
Orgtype char(1),
cnty char(3),
town char(3),
Aux char(1),
mon1emp varchar(8),
mon1ind char(1),
mon2emp varchar(8),
mon2ind char(1),
mon3emp varchar(8),
mon3ind char(1),
totalwage varchar(11),
totalwind char(1),
Taxwage varchar(11),
Contrib varchar(9),
Typecov char(1),
meei char(1),
Rptingchng char(1),
Comment1 char(2),
Comment2 char(2),
Comment3 char(2),
Narrcmnt char(57),
Collectind char(2),
Blank3 char(2),
Uiaddrtype char(1),
Plabigdat char(8),
Geosoftwar char(1),
Geocodesrc char(1),
Matchcode char(4),
Location char(3),
Latitude varchar(9),
Longitude varchar(11),
Newlonglat char(5),
Placecode char(5),
Blank4 char(2),
Censusstate char(2),
Censuscounty char(3),
Censustrac char(6),
Censusblocgroup char(1),
Censusblockcode char(2),
Censusblockcode2 char(1),
Blank5 char(14),
Futurerefileyear char(4),
Futurerefileresp char(2),
Futurenaics char(6),
Futurecnty char(3),
Futuretown char(3),
FutureCMICode char(2),
ssncount char(6),
wrwages char(11),
teleext char (5),
Contactname char(35),
ContactTitle char(35),
Contactemail char(60),
ContactFax char(10),
ContactWebsite char(60),
Blank6 char (1),
wrrecipsucc char(10),
wrcountlargestrecip char(6),
wrlargestcontribsucc char (10),
wrcountlargestcontrib char(6),
wrhires char(6),
wrseparations char(6),
wrnewentrants char(6),
wrexits char(6),
wrcontrecords char(6),
Blank7 char(78)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,272 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points

    1 SQLCHAR 0 1 "" 1 transcode ""
    2 SQLCHAR 0 2 "" 2 stfips ""
    3 SQLCHAR 0 4 "" 3 year ""
    4 SQLCHAR 0 1 "" 4 qtr ""
    5 SQLCHAR 0 10 "" 5 uiacct ""
    6 SQLCHAR 0 5 "\r\n" 6 run ""

    The last line in the format file needs a rowterminator. "\r\n".

  2. Jingyang Li 5,891 Reputation points

    For large fixed length file, you can use SSIS package to design once and run the package as a job through flat file connection manager.