semil colon delimited filed that has semi colon in the data

Question

semil colon delimited filed that has semi colon in the data

J.O.

Right there with Babe

Points: 756
More actions
June 25, 2010 at 10:57 am

#147697

I was wondering how I could get around this problem. Here is just some dummy data to sum my problem:
123;John Adams;123 Fake St;some data goes here; but it has a semicolon in the middle;10/12/1978
How can I keep the semi colon in the field 'some data goes here; but it has a semicolon in the middle' so that SSIS doesn't think its a delimiter? Any help is appreciated.

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

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

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

Do you have any control over generating the file? If so, use text qualifiers around your text data. It should ingore any delimiter character inside of the text qualifiers. Alternatively, change the delimiter to a pipe or another character that will not appear inside of a text column.

If not, you may need to rebuild the file in SSIS into a format that it can use. To do this, you'll need to :

1. identify columns that could potentially contain the delimiter inside of a text column.

2. send the file through a data flow task that opens the file, examines the data, clean up the text columns, and streams the file to a new file that will be easily consumed by a flat file source component.

John Rowan

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

J.O. Right there with Babe Points: 756 More actions · Answer 2

No control over the file. 🙁 Thanks for your suggestion I was afraid of that.

WayneS SSC Guru Points: 95460 More actions · Answer 3

Can you get the delimiter changed?

If you don't have any control over the file, then you need to be screaming really loud to who does about how they should not be choosing a delimiter for the file that is in the data.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

WayneS (6/25/2010)
Can you get the delimiter changed?
If you don't have any control over the file, then you need to be screaming really loud to who does about how they should not be choosing a delimiter for the file that is in the data.

I second the screaming.

John Rowan

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 5

All those in favor of screeaming say I

Knock

The screaming passes!

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 6

[font="Comic Sans MS"]

Another option - a change anyway on flatfile - to add a text qualifier. Example ""

"123";"John Adams";"123 Fake St";"some data goes here; but it has a semicolon in the middle";"10/12/1978"

This is one of the key purpose of text qualifier - to distinguish between ; in the middle or as a delimeter.

[/font]

[font="Comic Sans MS"]--
Sabya[/font]