Solved

DTS import error: Too many columns found...

Posted on 2010-11-18
4
434 Views
Last Modified: 2012-06-22
Hi experts, I have a file to import in SQL server 2000 ( about 7mb) and i get this error:

Too many columns found in current row, non white space character found after last default data..

how do i debug through this and look for what part is importing bad data? and is this mostly an error with special characters, or too many fields..?

attached is the file if anyone is interested to take a look...thank you in advance. demand.txt
0
Comment
Question by:sassy168
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 250 total points
Comment Utility
I have had this sort of thing happen when I was importing CSV data and someone embedded a comma in a street address.  After looking at your data, it appears that you have data that uses ? as a column delimiter, so I would suspect that there is a spurious ? somewhere in the data.

I would approach this from one of three directions:
Option 1:
I might try importing the data into an MS Access database.  The good thing about importing it into Access is that access will import all of the data and give you an error table that will indicate rows that had errors.  

Option 2:
I might also try breaking the input data into multiple (maybe 10 initially) files and then importing (and appending) the various smaller failes in an attempt to narrow down the location of the data issue.  (7Mb is a bit large to start manually searching. ;-)  Once you hit the segment that has the issue, I'd divide that one again.  At some point, you will have a file segment that is workable for manually analyzing the problem.

Option 3:
I might also consider loading the entire data file into a staging table that has one big column for the input data.  I would then create a query that uses SUBSTR to break out the data.  It may well be that using that query, you can load the data intop a target table (one that duplicates the real target table's colun layout and constraints) and then analyze that target table for erroneous column entries.
0
 
LVL 3

Accepted Solution

by:
gmleeman earned 250 total points
Comment Utility
Rows 17828 through to 17835 all have an issue.
Too many delimiters (?) on these rows, can you edit this manually?  Notepad will do it

Someone entered this as a value: "CAN WE HAVE XLS YET?"  And the extra ? has thrown it off

Delete the extra ? for these 8 rows and it should be perfect for import

Hope this helps
0
 
LVL 3

Expert Comment

by:gmleeman
Comment Utility
Hi sassy168,

surely the combined answers from myself and 8080 Diver are helpful in this regard.  And were provided to you in good time.

8080 Diver gave you possible debugging assistance, and I found the exact cause (and therefore the solution) of the import problem.

Perhaps some points to both of us would be more polite than simply deleting the question.

What do you think?

Cheers
0
 

Author Closing Comment

by:sassy168
Comment Utility
I solved it by importing them in excel and saw the problem was extra column so i deleted it from there and re imported. wasn't able to import into access sine there was somethingwrong with the delimiter...
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now