Post reply

Getting extra quote marks when Importing data with a single quote mark to identify inches

  • June 11, 2008 at 3:26 pm

    #192408

    I have a sql 2005 table containing a field for item description, when importing descriptions with a quote mark for inches they now have extra quotes. How do I prevent this from happening again when I import the next time (currently implementing new software and transferring data to test databases)?

    I get: "30"" Nova Keyboard Tray"

    but it should be: 30" Nova Keyboard Tray

    Thanks

    Dedra

  • June 11, 2008 at 3:38 pm

    #827567

    How are you doing the import?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 11, 2008 at 3:47 pm

    #827571

    Using bulking insert from a .csv file. No commas in data.

    Thanks

  • June 11, 2008 at 4:02 pm

    #827576

    Can you post your BULK INSERT statement and a couple of lines of sample data mimicing what your data looks like with regards to the quotes?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 12, 2008 at 8:16 am

    #827886

    Using bulking insert from a .csv file. No commas in data.

    umm...

    csv = comma separated values

    you must have commas.

    Open the file in Notepad rather than Excel to make sure Excel is not dropping identifiers.

  • June 12, 2008 at 9:21 am

    #827942

    commas are not part of the data to be imported, just as field separators

    code:

    bulk insert [prod_inSight].[dbo].[saitemoptions]

    from

    'j:\imports\items.csv' with (fieldterminator = ',')

    Data:

    96405,2524-0140076076,"SHEET 4 X 8 5/8"" DBL-SIDED HPL",1,2,EA,NOVA_NT_DOMAIN\dwagoner,39363.34861,NOVA_NT_DOMAIN\dwagoner,39363.60903,39363,65537,1

    field three imports as: "SHEET 4 X 8 5/8" DBL-SIDED HPL" should be SHEET 4 X 8 5/8" DBL-SIDED HPL

    Just noticed: data looks like SHEET 4 X 8 5/8" DBL-SIDED HPL when viewing csv in excel but looks like

    "SHEET 4 X 8 5/8" DBL-SIDED HPL" when viewing in notepad

    Thanks!

  • June 12, 2008 at 9:28 am

    #827950

    Couldn't you just update the column after the data insert by saying

    UPDATE Mytable

    SET Col3 = SUBSTRING(Col3 ,2,LEN(@Col3 )-2)

    This will remove the outer two quotes provided they are always there.

    If they not always there then just advance the SET statement.

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • June 12, 2008 at 9:30 am

    #827952

    Sorry it should have read

    SUBSTRING(COl3,2,LEN(Col3)-2)

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • June 12, 2008 at 9:54 am

    #827967

    Bulk Insert is just taking what you have in your data file. If the source data has the extra double quotes, bulk insert will treat them as wanted data. You'll have to either change the way the source file is generated, or go back and run the update statement to clean the data up.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 12, 2008 at 11:18 am

    #828002

    The update statement works.

    Thanks:)

Viewing 10 posts - 1 through 9 (of 9 total)

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