Post reply

Extra Terminator (,) in field causing double quotes to be displayed in record

  • April 20, 2009 at 12:49 pm

    #72013

    Hi,

    I have a datafile that contains a terminator within the datavalues (,) and when importing that data into a data table the data returns double quotes within the file. I came across this issue when using the Bulk Insert.

    SET QUOTED_IDENTIFIER OFF

    BULK INSERT GloverTest

    FROM 'C:\merged.csv'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = ''

    )

    I receive the following results in the datatable:

    HIGHS-4 "1 High Street LLC"

    HIGHS-4 "1 High Street LLC"

    The CSV file is

    "HIGHS-4","1 High Street,LLC"

    "HIGHS-4","1 High Street,LLC"

    The issue is the comma within the data itself how can BULK Insert resolved this. In Access the text qualifier is used.

    Thanks,

  • April 20, 2009 at 4:58 pm

    #981112

    Why QUOTED_IDENTIFIER is OFF?

    _____________
    Code for TallyGenerator

  • April 20, 2009 at 5:54 pm

    #981124

    I was trying to load additional parameters. I didn't have that set before and still encountered the same issue

  • April 20, 2009 at 9:59 pm

    #981184

    Set up a linked server to text file and load from the file like from remore table.

    Or use OPENQUERY.

    See BOL for details. Topic "Linked Servers".

    Example from there:

    --Create a linked server.

    EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    'c:\data\distqry',

    NULL,

    'Text'

    GO

    --Set up login mappings.

    EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL

    GO

    --List the tables in the linked server.

    EXEC sp_tables_ex txtsrv

    GO

    --Query one of the tables: file1#txt

    --using a 4-part name.

    SELECT *

    FROM txtsrv...[file1#txt]

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply