Embedded coma in coma seperated csv file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Embedded coma in coma seperated csv file

Author

Topic

AustraliaDBA
Starting Member

38 Posts

Posted - 2011-05-01 : 20:04:07

Hi All,

Please help me got stuck and unable to figure out what to do. i have to import data from comma seperated csv file into sql database and it is on going task. but can't accomplish it because ssis don't know how to handle it. any advice

Thanks

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-01 : 20:25:15

You mean you have a comma inside a field?
That's invalid and there's not a lot you can do about it.
The comma should be escaped or the strings delimitted.

How do you know that it's a comma in a field and not a separator to start the next field? If you can define a rule for that then you can code it but it might not be easy.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AustraliaDBA
Starting Member

38 Posts

Posted - 2011-05-01 : 20:46:16

when i preview the data i can see irrevalent data in teh fileds than i looked at the csv file and figured out there is , in between the text in few fields.
if it comes to programming in SSIS i have no idea from where to start as i am a beginner.. :(

Thanks

AustraliaDBA
Starting Member

38 Posts

Posted - 2011-05-02 : 00:33:54

any suggestion about dealing with this problem without changing the data.. please advise....

Thanks

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-02 : 07:15:03

I am just restating what Nigel already described. There are 3 possible scenarios:

1. Clean data. As an example this:

Title,Author
Catalan's Constant,Greg Fee
The Man Who Knew Infinity,Robert Kanigel

2. Unclean data - comma in the data itself, and no escape character

Title,Author
Catalan's Constant,Greg Fee
The Man Who Knew Infinity,Robert Kanigel
Pleasures of Pi,e and Other Interesting Numbers,Adrian Yeo

3. Data with escape charcter

"Title","Author"
"Catalan's Constant","Greg Fee"
"The Man Who Knew Infinity","Robert Kanigel"
"Pleasures of Pi,e and Other Interesting Numbers","Adrian Yeo"

One and three can be processed correctly. I know of no automated way to process two. The only possibility that I can think of is to ask the person/organization that provided the data to redo it with escape characters as in three OR use a different delimiter that is not in the data - people typically use pipe (|)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-02 : 09:56:57

Also try this
http://beyondrelational.com/blogs/madhivanan/archive/2010/12/15/bulk-insert-comma-as-part-of-data.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources