COMMA WITHIN NAME FIELD CAUSING COMMA-DELIMITED FILE IMPORT ERROR

Question

Post reply

COMMA WITHIN NAME FIELD CAUSING COMMA-DELIMITED FILE IMPORT ERROR

Nilssond

Hall of Fame

Points: 3285
More actions
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,,,,,,,,

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

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/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

LinksUp SSCertifiable Points: 6526 More actions · Answer 4

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/

John Rowan SSC Guru Points: 56440 More actions · Answer 5

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

Sergiy SSC Guru Points: 110208 More actions · Answer 6

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