Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trouble Importing CSV. "Truncation" despite Varchar(max) Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 5:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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
Posted Wednesday, February 20, 2013 6:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,654, Visits: 5,208
Is this a one-off import or are you setting up a repeatable process?

MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1422051
    Posted Wednesday, February 20, 2013 6:59 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Saturday, April 12, 2014 11:40 AM
    Points: 2,795, Visits: 8,297
    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
    Posted Wednesday, February 20, 2013 11:59 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: 2 days ago @ 3:56 PM
    Points: 1,654, Visits: 5,208
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1422237
    Posted Thursday, February 21, 2013 12:34 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, July 23, 2013 9:10 AM
    Points: 6, Visits: 18
    //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 »

    Add to briefcase

    Permissions Expand / Collapse