This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(220.8, 26%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
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)
);
Since creating this question, discovered that in my create table script, that there was a missing field. It was only creating 121 columns. That has since been resolved and still same error.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 12%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJL%3C/text%3E%3C/svg%3E)
Create a sample case for us to reproduce your issue with a few columns. Maybe you can find the issue along the way. Without seeing your datatable and code, it is too hard or impossible to know where the issue is. Thanks.
P.S.
I saw you posted your format file but we don't have the table and your query. let's try to use the top 5 and the last from your format file and create a sample table with these six columns and data types. Your bulk insert query as well.
Ok, I have edited the post to have the table create script. Will get some sample data created. The actual data is quite confidential
bulk insert equi2022
From 'C:\Users\yourname\Desktop\test.txt'
WITH (FORMATFILE = 'C:\Users\yourname\Desktop\formatfileequi-2.txt'
);
Can you change your format file 4th column with number not stringNumber, remove the padding infront of the number as length:
and the Prefix length should not be all 0, try to use 2.
122
1 SQLCHAR 2 1 "" 1 transcode ""
2 SQLCHAR 2 2 "" 2 stfips ""
3 SQLCHAR 2 4 "" 3 year ""
4 SQLCHAR 2 1 "" 4 qtr ""
5 SQLCHAR 2 10 "" 5 uiacct ""
6 SQLCHAR 2 5 "" 6 run ""
Ok, will try that. Have to pair down my data set so to make this not an all-day task
When I do this and cut the format file down to just the first six, I get the following error. So this is using a six column format file and data that is only 23 characters long so only takes those first six fields into account. Sample data is below
Msg 4832, Level 16, State 1, Line 11
Bulk load: An unexpected end of file was encountered in the data file.
F3220222000001070000000
F3220222000001450000000
F3220222000001450000001
F3220222000001450000002
F3220222000001450000005
did what you suggested and new error. read above
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @twilcox197805 ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Best regards,
Seeya
15.0
6
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".
bulk insert [testedb].dbo.equi2022
From 'C:\temp\test.txt'
WITH (FORMATFILE = 'C:\temp\formatfileequi-2.txt'
);
Load your sample data into the table. You need to make sure you have a rowTerminator defined in your format file.
create table equi2022(
Transcode char(1),
Stfips char(2),
Year char(4),
qtr char(1),
uiacct char(10),
run char(5)
)
For test data in test.txt:
F3220222000001070000000
F3220222000001450000000
F3220222000001450000001
F3220222000001450000002
F3220222000001450000005
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.
Yes, the vastly trimmed down example works. in the bigger picture, I had to get very creative in order to have the bulk insert. The problem column is something after comment3.