none
about BULK INSERT: how to remove the double quote? RRS feed

  • Question

  • suppose the file content is:
    "1","2","3"
    "11","21","31"
    "10","20","30"

    How to let bulk insert remove the double quote during the loading? I want to load them as 1,2,3 instead of "1", "2", "3"
    Monday, October 18, 2010 2:12 AM

Answers

  • create table dbo.tb(c1 varchar(30),c2 varchar(30), c3 varchar(30))
    go
    /*

    "P","ISSUE","Misc Material Issue"
    "P","MOVE","Misc Material Move"
    "J","POST","Misc Post Material"
    "J","MOVE","Misc Material Move"

    8.0
    4
    1 SQLCHAR 0 0 "\"" 0 first_quote ""
    2 SQLCHAR 0 30 "\",\"" 1 c1  ""
    3 SQLCHAR 0 30 "\",\"" 2 c2  ""
    4 SQLCHAR 0 30 "\r\n"  3 c3  ""
    */
    bulk insert dbo.tb
    from 'c:\txt.txt'
    with (formatfile='c:\fmt.fmt')
    go
    select *
    from dbo.tb
    go
    drop table dbo.tb


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 18, 2010 6:50 AM
    Answerer