[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Error when importing text file using import wizard

Dear all,

We tried to import a txt file using SQL 2008 import wizrd, and we get error, anyone know what it means?

DBA100. import wizard error.
0
marrowyung
Asked:
marrowyung
2 Solutions
 
djon2003Commented:
The first error talks about Encoding. I would try to open it in notepad and save it in Unicode format.
After that, if there is still the second error, I would look the format of the data in the file.
0
 
raja_ind82Commented:
Please refer the below links:
http://www.bimonkey.com/2009/06/flat-file-source-error-the-column-delimiter-for-column-columnname-was-not-found/
http://support.microsoft.com/kb/2445326
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/94399ff2-616c-44d5-972d-ca8623c8014e/

Your columns obviously is longer than the resulting column that you have in your target table. Check the mappings - maybe this is a very long character string, and the default length for the VARCHAR column in SQL Server is too small

Fiddling with the metadata in the Data Source's "Advanced" tab is then what you want to do to resolve the problem. Try to reset the whole thing by playing with the settings in "Suggest Types", or tweak settings on a field-by-field basis. A truly discouraging amount of iterations was needed in my case (broad input file), but eventually you can get it to work.
0
 
raja_ind82Commented:
The flat file defaults initially all columns to characters with length
of 50. You can change this by going to the “Advanced” page of the Flat
File connection page and change the length manually, or you can click on
"Suggest Types..." to get suggested column metadata attributes based on
sampling a certain number of rows from the file."
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
mcs0506Commented:
Hi,
I have a little tweak , first you export text file from that table where you want to import and then compare the both text files and hopefully you find the error from text file.


Regards,

Dani
0
 
marrowyungAuthor Commented:
mcs0506:

it is a bit complicate but we are trying to cahnge different option, we have use "suggested types" but it doesnt' works.

DBA100.
0
 
DFW_EdCommented:
have you considered using bcp instead of the import wizard?

http://msdn.microsoft.com/en-us/library/ms162802.aspx
0
 
marrowyungAuthor Commented:
DFW_Ed,

Is there any good example of bcp part of the table as a backup? and then later on import it back when we need to antoher table with the same structure?

For exampe, BCP out part of the table to a txt file, then we create another table with different name of the same schema, then we import txt file back to that new table ?

DBA100.
0
 
marrowyungAuthor Commented:
finally we don't do this.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now