Solved

DTS import error: Too many columns found...

Posted on 2010-11-18
4
439 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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 40
t-sql need help on t-sql 10 25
Powershell v3 - SQLCMD 3 26
question about results where i dont have a match 3 20
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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