Import text file into SQL 2005 db

[dkaplan]

I can't seem to get SQL 2005 to import a text file.
My intent is to bring in each line in the text file as a separate row. So the result looks like:
ID col1
1 one long row of text (len = 255)
2 another long row of text
.....

No columns, no delimiters

I used to do this all the time in SQL 2000, but when I try in 2005 (via the Import Export Wizard) I get the error:

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".


I've seen some evidence in Google that this is a known bug in 2005. Any ideas?

 

[AlexCuse]

Doesn't sound like a bug to me. Sounds like at some point, one column gets too long for the defined column width (are you positive its never longer than 255?)

If you have a table already established, it could be that you have a unicode (nvarchar, ntext) column that your are trying to import a non-unicode string. Or vice versa.

With a file like that, I'd loop over the rows and convert it to a .csv. Or bring it into a one column table, and split off the ID and everything else into separate columns using T-SQL (have a look at SUBSTRING and CHARINDEX functions).

If you need more help, please post the specifics on your table (the table creation script even) and the file.

Thanks,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Glasgow]

May also be worth looking at BULK INSERT. Once you get it working it might be quicker than using the wizard.

 

[dkaplan]

Thanks, Alex

You were right. There was a truncation going on.

The solution was to go to the advanced screen in the "Choose data source" page (of the import wizard) and change the outputColumnWidth setting. The default was 50.