none
Embedded quotes in CSV file - Flat file import

    Question

  • I have embedded double quotes in one of columns of my CSV file. E.g (see 2nd line) "Sateesh","Maduri",1,M "S""Folk", "M",1,M And as we dont have direct configuration properties for this type of flat files while importing into sql server, I have used the example given in http://www.ideaexcursion.com/2008/11/12/handling-embedded-text-qualifiers/ , but I am getting the error "value of type string cannot be converted to Microsoft.SqlServer.Dts.Pipeline.BlobColumn'. I have one column as Text column which have embedded double quotes in it. Please help me as soon as possible.
    Sateesh Maduri
    Monday, November 22, 2010 10:26 AM

Answers

All replies

  • CREATE TABLE TmpStList
    (
     stFName varchar (10) NOT NULL,
     stLName varchar (10) NOT NULL,
     stEmail varchar (30) NOT NULL
    )
    go

    The data file (hawk.dat):

    "Kelly","Reynold","kelly@reynold.com"
    "John","Smith","bill@smith.com"
    "Sara","Parker","sara@parker.com"


    The format file (hawk.bcp):

    8.0
    4
    1    SQLCHAR    0    1     "\""       0    first_quote  ""
    2    SQLCHAR    0    10    "\",\""    1    stFName      ""
    3    SQLCHAR    0    10    "\",\""    2    stLName      ""
    4    SQLCHAR    0    30    "\"\r\n"   3    stEmail      ""


    bulk insert TmpStList from 'C:\Staging\hawk.dat'
    with (formatfile = 'C:\Staging\hawk.bcp')

    select * from TmpStList


    stFName    stLName    stEmail                       
    ---------- ---------- ------------------------------
    Kelly      Reynold    kelly@reynold.com
    John       Smith      bill@smith.com
    Sara       Parker     sara@parker.com


    drop table TmpStList


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, November 22, 2010 10:32 AM
    Answerer
  • I have the embedded double quotes in one column which is text data type. I given sample example below, consider 2nd column in the below example and its a text data type in my case as it contains long text. Please help me.
    
    "Kelly","Reynol""d","kelly@reynold.com"
    "John","Sm""ith","bill@smith.com"
    "Sara","Park""er","sara@parker.com"
    
    

    Sateesh Maduri
    Monday, November 22, 2010 12:30 PM
  • Sateesh

    Hav you tried Import/Export wizard to create a SSIS?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 23, 2010 6:38 AM
    Answerer
  • Yeah I tried using Import/Export from sql server management studio and also from BIDS , but the same issue. We have embedded double quotes in csc file columns text.
    Sateesh Maduri
    Wednesday, November 24, 2010 5:04 AM
  • Yeah I tried using Import/Export from sql server management studio and also from BIDS , but the same issue. We have embedded double quotes in csv file columns text.
    Sateesh Maduri

    Sateesh Maduri
    Wednesday, November 24, 2010 5:37 AM
  • Does this help:

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/02/04/how-to-remove-unwanted-quotation-marks-while-importing-a-data-file.aspx


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Thursday, November 25, 2010 9:17 PM