Solved

DTS import error: Too many columns found...

Posted on 2010-11-18
4
448 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 250 total points
ID: 34173426
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
ID: 34292125
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
ID: 34498509
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
ID: 34502097
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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