Post reply

COMMA WITHIN NAME FIELD CAUSING COMMA-DELIMITED FILE IMPORT ERROR

  • April 4, 2016 at 2:54 pm

    #306106

    I am importing a comma-delimited text file to a SQL table. All records but two successfully import but the 2 remaining records have a comma within the last name field area which causes them to err out.

    Examples are: ,,,,,,Jones, Jr,,,,,,,,

  • April 4, 2016 at 2:57 pm

    #1869650

    is your data double quoted, meaning the value featuring the comma is properly delimited so you can process it?

    1,Academy award winners,"Jones, James Earl",moredata

    if it's not, you need to go back to the source and get it delimited properly. quoted identifiers are a must when the data can contain the field delimiter, like a comma.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 4, 2016 at 3:12 pm

    #1869654

    Or change the delimiter to something that should not ever be in the source data like a pipe | or tidle ~.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • April 4, 2016 at 5:17 pm

    #1869675

    LinksUp (4/4/2016)

    Or change the delimiter to something that should not ever be in the source data like a pipe | or tidle ~.

    'Tiddled' is what I was last Friday night. You meant 'tilde', I think 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • April 5, 2016 at 10:51 am

    #1869903

    Phil Parkin (4/4/2016)

    'Tiddled' is what I was last Friday night. You meant 'tilde', I think 🙂

    😀

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • April 5, 2016 at 11:37 am

    #1869935

    I prefer changing the delimiter also. Adding quoted identifiers is nice, but then you have similar issues when you have commas and quotes inside your strings. I've see it too many times. I always prefer a non-comma delimited file. Pipes are nice, tilde is nice too.

    John Rowan

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

  • April 5, 2016 at 4:37 pm

    #1870022

    John Rowan (4/5/2016)

    I always prefer a non-comma delimited file. Pipes are nice, tilde is nice too.

    Unless those characters are used for something else.

    I used to import catalogue files from a supplier which was replacing TAB, CR, LF characters in "Product Description" column with "unused" characters.

    And, of course, because "Product Description" was filled up by user not aware of my file formatting problems, often from abroad, so they could put in there any character they feel like putting into it.

    And the process was expected to load the catalogue every morning regardless of content of the descriptions.

    _____________
    Code for TallyGenerator

Viewing 7 posts - 1 through 6 (of 6 total)

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