Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Strip double quotes from an import file in Integration Services SSIS

MSSQLTips author Greg Robidoux By:   |   Read Comments (20)   |   Related Tips: More > Integration Services Development

Problem
When loading data using SQL Server Integration Services (SSIS) I am importing data from a CSV file.  Every single one of the columns in the CSV file has double quotes around the data.  When using the Data Flow Task to import the data I have double quotes around all of the imported data.  How can I import the data and remove the double quotes?

Solution
This is a pretty simple solution, but the fix may not be as apparent as you would think.  Let's take a look at our example.

Here is the sample CSV file as it looks in a text editor.  You can see that all of the columns have double quotes around the data even where there is no data.  The file is comma delimited, so this should give us enough information to import the data column by column.

To create the package we use a Data Flow Task and then use the Flat File Source as our data flow source.

When setting up the Flat File Connection for the data source we enter the information below, basically just selecting our source file.

If we do a quick preview on the dataset we can see that every column has the double quotes even the columns where there is no data.  If you open the text file in Excel the double quotes are automatically stripped, so what needs to be done in SSIS to accomplish this.

On this screen you can see the highlighted area and the entry that is made for the "Text qualifier".  Here we enter in the double quote mark " and this will allow SSIS to strip the double quotes from all columns.

If we do another preview we can see that the double quotes are now gone and we can move on to the next part of our SSIS package development.

As mentioned above this is a simple fix to solve this problem.  If you are faced with this issue, hopefully this gives you a quick answer to get your development moving forward.  This same technique can be used to strip any other text qualifier data from your files.

Next Steps



Last Update: 8/24/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, September 04, 2014 - 8:43:46 PM - Alex Read The Tip

I have the same issue; how do we get around this?


Wednesday, May 28, 2014 - 9:38:03 AM - Inam Read The Tip

Thank you. your post solved my problem.


Wednesday, October 16, 2013 - 5:51:24 AM - Bhoj Read The Tip

Good One !!


Tuesday, April 16, 2013 - 5:04:03 AM - Mikel Read The Tip

Hi,

Is there a way to remove the double quote for those column with no data only.  We want to keep the double quote for those column that has data.


Thursday, April 04, 2013 - 11:34:02 AM - Driq Read The Tip

 

Hehe..this is so simple,saves a lot of time. Thanx.


Monday, February 25, 2013 - 3:13:30 PM - Carol Read The Tip

OK - I found the problem, and it's environment specific.  My development environment substitutes _x0022_ in the "TextQuallifier" property, and my Production Environment doesn't understand that...  Right click your .dtsx, view the source and look for TextQualifier.  If you port it over to your production environment, fix the TextQualifier and recompile, it finally works....

Thank you Jamie Thomson!

http://sqlblog.com/blogs/jamie_thomson/archive/2011/10/11/31040.aspx

 


Thursday, February 07, 2013 - 1:06:52 AM - Carol Read The Tip

My SSIS package runs great interactively from Visual Studio.  However, after I deploy to SQL Studio 2008/R2 and schedule it the quotes are imported into the table.

Can anyone please help?

Thanks


Thursday, January 31, 2013 - 6:15:09 AM - Ashish Read The Tip

 

How about quote and coma inside quote?

how parse folowing txt or csv file formate?

"12333","Ashish","Ashish , Patel 6"","24566"

1. 12333

2. Ashish

3. Ashish , Patel 6"

4. 24566


thanks


Thursday, January 03, 2013 - 4:50:33 PM - Suman Read The Tip

Thanks a lot, this worked like a charm.

 

 

 


Friday, November 30, 2012 - 10:03:15 PM - Ben.Gu Read The Tip

Thank you very much.

it worked.


Friday, November 16, 2012 - 4:58:06 PM - Joanie Read The Tip

Thank you.  Worked very well!


Friday, August 03, 2012 - 11:06:49 AM - Prolay Read The Tip

Thanks a lot , a little " makes a lot difference

 


Friday, June 15, 2012 - 9:14:01 AM - FunkyKoval Read The Tip

Hi

 

I have similar problem, also I work with file with double quotes at begin and at the end of some columns in csv file

but unfortunately some lines are wrong, they start with double quot but do NOT end with it!

of course data flow task fail, how do you sugest to repair the before running data flow tusk to be the most effective

unfortunately files are quite big >200MB

 

Funky


Thursday, February 23, 2012 - 11:56:15 AM - Suresh Read The Tip

 

Thanks Greg, It fixed my issue


Monday, February 20, 2012 - 9:43:19 AM - AmyNxDx Read The Tip

Thanks! This simple fixed helped me TONS!!


Tuesday, May 03, 2011 - 8:27:23 PM - Adam Read The Tip

Do'H!!!

Such a simple fix. I started manually pulling out the data and ran into a bunch of data conversion errors down the road.

Saved me a bunch of time. Thanks!


Wednesday, March 23, 2011 - 12:07:15 PM - Pete Read The Tip

I have the same problem; how do we get around this!?


Wednesday, June 30, 2010 - 12:20:07 PM - jerryol Read The Tip

Ha, ha.  When I google "undouble kirk haselden" I do get 4 hits.  Three of them point to this article, and one is junk.  Thanks anyway.


Monday, August 31, 2009 - 6:13:21 PM - MikeInNH Read The Tip

 Hi William

Yeah, the tip published here doesn't work so good if the field separator (comma) is included in the quoted text.  However, I believe if you Google "undouble kirk haselden" you will get about 4 hits.  You probably know that Haselden is the "father of SSIS" - His solution is to use a script component to parse lines.  After some Googling you can probably get the code (in Visual Basic .NET I believe) for the component. 

 Hope that helps. 

Mike 


Monday, April 07, 2008 - 9:57:46 AM - william.weber Read The Tip

I realize this is a very old tip, but I've been searching endlessly for a solution to my problem.

How do you deal with embedded text qualifiers in SSIS? I know that DTS dealt with this stuff correctly and I can't for the life of me figure out how to make SSIS read the file correctly. For example:

"This is field1","This is ""field2"""

 In DTS if you selected " as your text qualifier it would correctly give you the following:

This is field1, This is "field2"

In SSIS it just breaks. Any help is greatly appreciated.




 
Sponsor Information