Error Importing .txt file Bulk

I am trying to do a bulk insert of a .txt file into a SQL Server table,  I am getting this error:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

I have looked thru my definition file several times and I'm not seeing the issue.  The only thought I have is that this has something to do with the first column in the table (ID), which is and identify, autonumber column.  There is nothing in my FormatFile for the column since I don't want to import anything into that column.

Any ideas or solutions?

This is my 'code'
Insert into TQDT_ReceiptDetail_Import
select * FROM OPENROWSET( BULK 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Receipts\TQDTFILE.txt',
FORMATFILE = 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\SQLImportFormatFiles\RCTD_RecprtDtl_Import.txt') as a

Open in new window


Here is my table def:
CREATE TABLE [dbo].[TQDT_ReceiptDetail_Import](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SchoolCode] [nvarchar](4) NULL,
	[MuniCode] [nvarchar](4) NULL,
	[RecepitNumber] [nvarchar](8) NULL,
	[ReceiptSeqNum] [nvarchar](2) NULL,
	[TaxYear] [nvarchar](4) NULL,
	[DetailSeqNum] [nvarchar](3) NULL,
	[RecordType] [nvarchar](1) NULL,
	[TaxType] [nvarchar](1) NULL,
	[Series] [nvarchar](2) NULL,
	[Volume01] [nvarchar](3) NULL,
	[Volume02] [nvarchar](1) NULL,
	[Page] [nvarchar](3) NULL,
	[DTDYear] [nvarchar](2) NULL,
	[DTDNumber] [nvarchar](6) NULL,
	[DTDYearAlpha] [nvarchar](1) NULL,
	[AsgnDTDYear] [nvarchar](2) NULL,
	[AsgnDTDNumber] [nvarchar](6) NULL,
	[AsgnDTDYearAlpha] [nvarchar](1) NULL,
	[Docket] [nvarchar](30) NULL,
	[Entry_MM] [nvarchar](2) NULL,
	[Entry_DD] [nvarchar](2) NULL,
	[Entry_YYYY] [nvarchar](4) NULL,
	[Revive_MM] [nvarchar](2) NULL,
	[Revive_DD] [nvarchar](2) NULL,
	[Revive_YYYY] [nvarchar](4) NULL,
	[FaceDueToDate] [nvarchar](11) NULL,
	[PenaltyDueToDate] [nvarchar](11) NULL,
	[INterestDueToDate] [nvarchar](11) NULL,
	[CostDueToDate] [nvarchar](11) NULL,
	[CommDueToDate] [nvarchar](11) NULL,
	[FacePTD] [nvarchar](11) NULL,
	[PenaltyPTD] [nvarchar](11) NULL,
	[InterestPTD] [nvarchar](11) NULL,
	[CostPTD] [nvarchar](11) NULL,
	[CommPTD] [nvarchar](11) NULL,
	[ThisFacePaid] [nvarchar](11) NULL,
	[ThisPenaltyPaid] [nvarchar](11) NULL,
	[ThisIntrestPaid] [nvarchar](11) NULL,
	[ThisCostPaid] [nvarchar](11) NULL,
	[ThisCommPaid] [nvarchar](11) NULL,
	[AddlInterest] [nvarchar](11) NULL,
	[SoldFlag] [nvarchar](1) NULL,
	[Filler] [nvarchar](38) NULL,
	[upsize_ts] [timestamp] NULL,
 CONSTRAINT [aaaaaTQDT_ReceiptDetail_Import_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


This is my FormatFIle
12.0
43
1       SQLCHAR       0       4	       ""        1    SchoolCode      	 ""
2       SQLCHAR       0       4	       ""        2    MuniCode        	 ""   
3       SQLCHAR       0       8	       ""        3    RecepitNumber   	 ""  
4       SQLCHAR       0	      2	       ""        4    ReceiptSeqNum   	 ""   
5       SQLCHAR       0       4	       ""        5    TaxYear         	 ""
6       SQLCHAR       0       3	       ""        6    DetailSeqNum    	 ""
7       SQLCHAR       0       1	       ""        7    RecordType      	 ""
8       SQLCHAR       0       1	       ""        8    TaxType         	 ""
9       SQLCHAR       0       2	       ""        9    Series          	 ""
10      SQLCHAR       0       3	       ""        10   Volume01        	 ""
11      SQLCHAR       0       1	       ""        11   Volume02        	 ""
12      SQLCHAR       0       3	       ""        12   Page            	 ""
13      SQLCHAR       0       2	       ""        13   DTDYear         	 ""
14      SQLCHAR       0       6	       ""        14   DTDNumber       	 ""
15      SQLCHAR       0       1	       ""        15   DTDYearAlpha    	 ""
16      SQLCHAR       0       2	       ""        16   AsgnDTDYear     	 ""
17      SQLCHAR       0       6	       ""        17   AsgnDTDNumber   	 ""
18      SQLCHAR       0       1	       ""        18   AsgnDTDYearAlpha   ""
19      SQLCHAR       0      30	       ""        19   Docket             ""
20      SQLCHAR       0       2	       ""        20   Entry_MM           ""
21      SQLCHAR       0       2	       ""        21   Entry_DD           ""
22      SQLCHAR       0       4	       ""        22   Entry_YYYY       	 ""
23      SQLCHAR       0       2	       ""        23   Revive_MM          ""
24      SQLCHAR       0       2	       ""        24   Revive_DD          ""
25      SQLCHAR       0       4	       ""        25   Revive_YYYY        ""
26      SQLCHAR       0      11	       ""        26   FaceDueToDate  	 "" 
27      SQLCHAR       0      11	       ""        27   PenaltyDueToDate   ""
28      SQLCHAR       0      11	       ""        28   INterestDueToDate  ""
29      SQLCHAR       0      11	       ""        29   CostDueToDate      ""
30      SQLCHAR       0      11	       ""        30   CommDueToDate      ""
31      SQLCHAR       0      11	       ""        31   FacePTD            ""
32      SQLCHAR       0      11	       ""        32   PenaltyPTD         ""
33      SQLCHAR       0      11	       ""        33   InterestPTD        ""
34      SQLCHAR       0      11	       ""        34   CostPTD 		 ""
35      SQLCHAR       0      11	       ""        35   CommPTD 		 ""
36      SQLCHAR       0      11	       ""        36   ThisFacePaid 	 ""
37      SQLCHAR       0      11	       ""        37   ThisPenaltyPaid 	 ""
38      SQLCHAR       0      11	       ""        38   ThisIntrestPaid 	 ""
39      SQLCHAR       0      11	       ""        39   ThisCostPaid 	 ""
40      SQLCHAR       0      11	       ""        40   ThisCommPaid 	 ""
41      SQLCHAR       0      11	       ""        41   AddlInterest 	 ""
42      SQLCHAR       0      1	       ""        42   SoldFlag 	 	 ""
43      SQLCHAR       0      38	       "\r\n"    43   Filler 		 ""

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
You need to include it in your format file anyway...

You dont have to use it, and can even Skip it by having a zero in the destination column

E.G.
12.0
44
1       SQLCHAR       0       4	       ""        0    AutoNumberID   	 ""
2       SQLCHAR       0       4	       ""        2    SchoolCode      	 ""
3       SQLCHAR       0       4	       ""        3    MuniCode        	 ""   
4       SQLCHAR       0       8	       ""        4    RecepitNumber   	 ""  
5       SQLCHAR       0       2	       ""        4    ReceiptSeqNum   	 ""   

Open in new window

Your other choice is to use that value and let it populate the identity by allowing identity_insert : https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql

But if it is in the file being imported, then your format file needs to match...
mlcktmguyAuthor Commented:
Same Result.  This is the revised format file which is also attached.
12.0
44
1       SQLCHAR       0       4	       ""        0    ID	      	 ""
2       SQLCHAR       0       4	       ""        1    SchoolCode      	 ""
3       SQLCHAR       0       4	       ""        2    MuniCode        	 ""   
4       SQLCHAR       0       8	       ""        3    RecepitNumber   	 ""  
5       SQLCHAR       0	      2	       ""        4    ReceiptSeqNum   	 ""   
6       SQLCHAR       0       4	       ""        5    TaxYear         	 ""
7       SQLCHAR       0       3	       ""        6    DetailSeqNum    	 ""
8       SQLCHAR       0       1	       ""        7    RecordType      	 ""
9       SQLCHAR       0       1	       ""        8    TaxType         	 ""
10      SQLCHAR       0       2	       ""        9    Series          	 ""
11      SQLCHAR       0       3	       ""        10   Volume01        	 ""
12      SQLCHAR       0       1	       ""        11   Volume02        	 ""
13      SQLCHAR       0       3	       ""        12   Page            	 ""
14      SQLCHAR       0       2	       ""        13   DTDYear         	 ""
15      SQLCHAR       0       6	       ""        14   DTDNumber       	 ""
16      SQLCHAR       0       1	       ""        15   DTDYearAlpha    	 ""
17      SQLCHAR       0       2	       ""        16   AsgnDTDYear     	 ""
18      SQLCHAR       0       6	       ""        17   AsgnDTDNumber   	 ""
19      SQLCHAR       0       1	       ""        18   AsgnDTDYearAlpha   ""
20      SQLCHAR       0      30	       ""        19   Docket             ""
21      SQLCHAR       0       2	       ""        20   Entry_MM           ""
22      SQLCHAR       0       2	       ""        21   Entry_DD           ""
23      SQLCHAR       0       4	       ""        22   Entry_YYYY       	 ""
24      SQLCHAR       0       2	       ""        23   Revive_MM          ""
25      SQLCHAR       0       2	       ""        24   Revive_DD          ""
26      SQLCHAR       0       4	       ""        25   Revive_YYYY        ""
27      SQLCHAR       0      11	       ""        26   FaceDueToDate  	 "" 
28      SQLCHAR       0      11	       ""        27   PenaltyDueToDate   ""
29      SQLCHAR       0      11	       ""        28   INterestDueToDate  ""
30      SQLCHAR       0      11	       ""        29   CostDueToDate      ""
31      SQLCHAR       0      11	       ""        30   CommDueToDate      ""
32      SQLCHAR       0      11	       ""        31   FacePTD            ""
33      SQLCHAR       0      11	       ""        32   PenaltyPTD         ""
34      SQLCHAR       0      11	       ""        33   InterestPTD        ""
35      SQLCHAR       0      11	       ""        34   CostPTD 		 ""
36      SQLCHAR       0      11	       ""        35   CommPTD 		 ""
37      SQLCHAR       0      11	       ""        36   ThisFacePaid 	 ""
38      SQLCHAR       0      11	       ""        37   ThisPenaltyPaid 	 ""
39      SQLCHAR       0      11	       ""        38   ThisIntrestPaid 	 ""
40      SQLCHAR       0      11	       ""        39   ThisCostPaid 	 ""
41      SQLCHAR       0      11	       ""        40   ThisCommPaid 	 ""
42      SQLCHAR       0      11	       ""        41   AddlInterest 	 ""
43      SQLCHAR       0      1	       ""        42   SoldFlag 	 	 ""
44      SQLCHAR       0      38	       "\r\n"    43   Filler 		 ""

Open in new window

RCTD_RecprtDtl_Import.txt
mlcktmguyAuthor Commented:
Mark, I revised the FormatFile again, still the same error.

The new file is attached.
RCTD_RecprtDtl_Import.txt
Mark WillsTopic AdvisorCommented:
Are you positive that the ID is in the data table ?

Suggest trying change format file (easy this time) change length for ID to be 0
1       SQLCHAR       0       0	       ""        0    ID	      	 ""

Open in new window


Then do
select * FROM OPENROWSET( BULK 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Receipts\TQDTFILE.txt',
FORMATFILE = 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\SQLImportFormatFiles\RCTD_RecprtDtl_Import.txt') as a

Open in new window

Check your columns from result grid.

Then explain what         [upsize_ts] [timestamp] NULL,          is and how it gets populated....

Then try dropping the upsize_TS column from your table (or have to spell out every column on receiving table). You cannot insert a value into a timestamp.
alter table TQDT_ReceiptDetail_Import drop column [upsize_ts] 

Open in new window


Then try your insert
Insert TQDT_ReceiptDetail_Import
select * FROM OPENROWSET( BULK 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Receipts\TQDTFILE.txt',
FORMATFILE = 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\SQLImportFormatFiles\RCTD_RecprtDtl_Import.txt') as a

Open in new window

If you want the timestamp, can add it back in after the initial load.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Perfect, thank you.
Both changes were required to get it to work.  Setting the ID length to zero and removing the timestamp column.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.