Trouble Importing CSV. "Truncation" despite Varchar(max)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Trouble Importing CSV. "Truncation" despite...

Trouble Importing CSV. "Truncation" despite Varchar(max)

Rate Topic

Display Mode

Topic Options

Author

Message

homebrew01

Posted Wednesday, February 20, 2013 5:04 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,554, Visits: 7,211

I'm trying to import a .csv file. One column has text from a website comment filed, so there are all kinds of characters entered. Using the Import Wizard, I set the incoming column length to 8000, and the column in my staging table to varchar(max) or nvarchar(max), but I still get these messages. I removed any commas from the file, but there are ampersands and whatnot in that colum. A warning box sometimes comes up when defining the wizard options, and I choose "ignore" for data truncation, but no effect.

Is there a foolproof way to "import everything" into a SQL table ?

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for 
column "Status" returned status value 4 and status text 
"Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task 1: The "output column "Status" (14)" failed because truncation occurred, 
and the truncation row disposition on "output column "Status" (14)" specifies failure on truncation.
 A truncation error occurred on the specified object of the specified component.

Post #1422025

mister.magoo

Posted Wednesday, February 20, 2013 6:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 1,298, Visits: 3,884

Is this a one-off import or are you setting up a repeatable process?

MM

Post #1422051

homebrew01

Posted Wednesday, February 20, 2013 6:59 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,554, Visits: 7,211

Today it is a one-off. But I will be setting up a weekly process for future imports.
I thought I would use the wizard the first time, then save it as a package for future use.

There are probably some funky characters in the text strings that SQL doesn't like.

EDIT: There were some commas in one of the columns that i removed, and in one text box there was 3 pages of diatribe about the economy that had 8,900 characters. I modifed those, and the import worked, but a cumbersome solution.

Post #1422063

mister.magoo

Posted Wednesday, February 20, 2013 11:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 1,298, Visits: 3,884

Well, if the import file is invalid because it contains unquoted separators in the data, you will always struggle.

You should get the producer of the data to remove invalid characters and/or look at using a more unusual character as a delimiter.

The "pipe" symbol | is quite often used because it doesn't often appear in common language...

MM

Post #1422237

bdloving 4446

Posted Thursday, February 21, 2013 12:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 1:14 PM
Points: 6, Visits: 17

//one or more characters had no match in the target code page//

seems to be wierd characters,
you could try a varbinary field instead of varchar

Post #1422752

« Prev Topic | Next Topic »

Permissions