none
Bulk insert and Double Quotes RRS feed

  • Question

  • Hi,

    i have been struggling with this problem for a few weeks so i am really hoping that some one can help me.

     

    i have a system to in importing large amounts of data from text files in the a SQL 2005 DB. work fine 90% of the time but there are 2 files that are causign me really problems.

    the delimiter in the fil is "," and there are commas with out quotes in some fo the fields so i can not ignor the quotes.

     

    this command will import the files fine

    Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

    WITH

    (FIELDTERMINATOR = '","')

     

    but as not all the fields in [Raw Call Data] are used all of the time i need to use format files.

    so my question is simple who do i do this with a format file ?

    Thursday, May 24, 2007 3:34 PM

Answers

  • that will work, but i have to use a format file as there are 50 fields in raw call data and i am only use 19 for this file, other files that are imported use all 50.

     

    I have found the solution my self by accident

    you can use the following format file to do it

    9.0
    19
    1       SQLCHAR       0       255     "\",\""                   1     Field1      Latin1_General_CI_AS
    2       SQLCHAR       0       255     "\",\""                    2     Field2      Latin1_General_CI_AS
    3       SQLCHAR       0       255     "\",\""                    3     Field3      Latin1_General_CI_AS
    4       SQLCHAR       0       255     "\",\""                    4     Field4      Latin1_General_CI_AS
    5       SQLCHAR       0       255     "\",\""                    5     Field5      Latin1_General_CI_AS
    6       SQLCHAR       0       255     "\",\""                    6     Field6      Latin1_General_CI_AS
    7       SQLCHAR       0       255     "\",\""                    7     Field7      Latin1_General_CI_AS
    8       SQLCHAR       0       255     "\",\""                    8     Field8      Latin1_General_CI_AS
    9       SQLCHAR       0       255     "\",\""                    9     Field9      Latin1_General_CI_AS
    10      SQLCHAR       0       255     "\",\""                    10     Field10      Latin1_General_CI_AS
    11       SQLCHAR       0       255     "\",\""                    11     Field11      Latin1_General_CI_AS
    12       SQLCHAR       0       255     "\",\""                    12     Field12     Latin1_General_CI_AS
    13       SQLCHAR       0       255     "\",\""                    13     Field13      Latin1_General_CI_AS
    14       SQLCHAR       0       255     "\",\""                    14     Field14      Latin1_General_CI_AS
    15       SQLCHAR       0       255     "\",\""                    15     Field15      Latin1_General_CI_AS
    16       SQLCHAR       0       255     "\",\""                    16     Field16      Latin1_General_CI_AS
    17       SQLCHAR       0       255     ","                    17     Field17      Latin1_General_CI_AS
    18       SQLCHAR       0       255     ","                    18     Field18      Latin1_General_CI_AS
    19       SQLCHAR       0       255     "\r\n"                 19     Field19      Latin1_General_CI_AS

    Thursday, May 24, 2007 4:13 PM

All replies

  •  

    Code Snippet

    Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

    WITH

    (FIELDTERMINATOR = '","'

    , FORMATFILE = 'format_file_path')

     

     

    Thursday, May 24, 2007 3:52 PM
  • that will work, but i have to use a format file as there are 50 fields in raw call data and i am only use 19 for this file, other files that are imported use all 50.

     

    I have found the solution my self by accident

    you can use the following format file to do it

    9.0
    19
    1       SQLCHAR       0       255     "\",\""                   1     Field1      Latin1_General_CI_AS
    2       SQLCHAR       0       255     "\",\""                    2     Field2      Latin1_General_CI_AS
    3       SQLCHAR       0       255     "\",\""                    3     Field3      Latin1_General_CI_AS
    4       SQLCHAR       0       255     "\",\""                    4     Field4      Latin1_General_CI_AS
    5       SQLCHAR       0       255     "\",\""                    5     Field5      Latin1_General_CI_AS
    6       SQLCHAR       0       255     "\",\""                    6     Field6      Latin1_General_CI_AS
    7       SQLCHAR       0       255     "\",\""                    7     Field7      Latin1_General_CI_AS
    8       SQLCHAR       0       255     "\",\""                    8     Field8      Latin1_General_CI_AS
    9       SQLCHAR       0       255     "\",\""                    9     Field9      Latin1_General_CI_AS
    10      SQLCHAR       0       255     "\",\""                    10     Field10      Latin1_General_CI_AS
    11       SQLCHAR       0       255     "\",\""                    11     Field11      Latin1_General_CI_AS
    12       SQLCHAR       0       255     "\",\""                    12     Field12     Latin1_General_CI_AS
    13       SQLCHAR       0       255     "\",\""                    13     Field13      Latin1_General_CI_AS
    14       SQLCHAR       0       255     "\",\""                    14     Field14      Latin1_General_CI_AS
    15       SQLCHAR       0       255     "\",\""                    15     Field15      Latin1_General_CI_AS
    16       SQLCHAR       0       255     "\",\""                    16     Field16      Latin1_General_CI_AS
    17       SQLCHAR       0       255     ","                    17     Field17      Latin1_General_CI_AS
    18       SQLCHAR       0       255     ","                    18     Field18      Latin1_General_CI_AS
    19       SQLCHAR       0       255     "\r\n"                 19     Field19      Latin1_General_CI_AS

    Thursday, May 24, 2007 4:13 PM