Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Embedded quotes in CSV file - Flat file import

Answered Embedded quotes in CSV file - Flat file import

  • Monday, November 22, 2010 10:26 AM
     
     
    Sign In to Vote
    0
    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
     

All Replies

  • Monday, November 22, 2010 10:32 AM
    Answerer
     
     
    Sign In to Vote
    0

    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 12:30 PM
     
      Has Code
    Sign In to Vote
    0
     
    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
     
  • Tuesday, November 23, 2010 6:38 AM
    Answerer
     
     
    Sign In to Vote
    0

    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/
     
  • Wednesday, November 24, 2010 5:04 AM
     
     
    Sign In to Vote
    0
    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:37 AM
     
     
    Sign In to Vote
    0
    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
     
  • Thursday, November 25, 2010 9:17 PM
     
     Answered
    Sign In to Vote
    0

    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