Solved

DTS import error: Too many columns found...

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert null in sql server 12 34
Sql Server group by 10 27
SQL Server Import/Error Wizard error 12 19
access query to sql server 3 20
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

773 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