Problem impoting Flat file with Embedded text qualifier and separator
HI All, I am importing comma separated CSV file in Sql Server Database using Import data wizard and there by creating SSIS pkg automatically. I have used double quotes (") as text qualifier. In one of the field value is "Right frac. ""In 2011, America's ...""" . The data which need to get imported is Right frac. ""In 2011, America's ..."" but in this specific case it is getting divided into two as "Right frac. ""In 201 and America's ...""" and getting imported into two separate columns. Any Solution to this issue? Thanks, MonalisaMona
May 16th, 2012 6:11am

Hi, What is your column separator? Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 8:36am

comma (,)Mona
May 16th, 2012 10:05am

Problem is that SSIS is getting confused with multiple level double quotes. I can think of two options : a) Change your text delimiter (the outer double quotes) to some other character like [~] Given that you're using double quotes and apostrophes within your textual content, you'll have to choose another symbol like [~] or [^] Your sample sentence would look like ^Right frac. "In 2011, America's ..."^ (note that you won't need to duplicate double quotes) b) Interpret and manipulate text file via VB or C# scripts, which is tediousSebastian Sajaroff Senior DBA Pharmacies Jean Coutu
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 10:39am

Problem is that SSIS is getting confused with multiple level double quotes. I can think of two options : a) Change your text delimiter (the outer double quotes) to some other character like [~] Given that you're using double quotes and apostrophes within your textual content, you'll have to choose another symbol like [~] or [^] Your sample sentence would look like ^Right frac. "In 2011, America's ..."^ (note that you won't need to duplicate double quotes) b) Interpret and manipulate text file via VB or C# scripts, which is tediousSebastian Sajaroff Senior DBA Pharmacies Jean Coutu
May 16th, 2012 10:42am

HI All, I am importing comma separated CSV file in Sql Server Database using Import data wizard and there by creating SSIS pkg automatically. I have used double quotes (") as text qualifier. In one of the field value is "Right frac. ""In 2011, America's ...""" . The data which need to get imported is Right frac. ""In 2011, America's ..."" but in this specific case it is getting divided into two as "Right frac. ""In 201 and America's ...""" and getting imported into two separate columns. Any Solution to this issue? Thanks, Monalisa Mona Mona, The embedded comma is the issue, if you just replace the delimiter all will work. I bloged about this. One method is to use PowerShell: http://geekswithblogs.net/Compudicted/archive/2011/09/22/how-to-remove-quotes-and-replace-the-delimiter-in-a.aspx and another is the Script Transformation: http://geekswithblogs.net/Compudicted/archive/2011/09/19/ssis-how-to-remove-occasional-quotes-and-replace-the-column.aspxArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 11:02am

HI All, I am importing comma separated CSV file in Sql Server Database using Import data wizard and there by creating SSIS pkg automatically. I have used double quotes (") as text qualifier. In one of the field value is "Right frac. ""In 2011, America's ...""" . The data which need to get imported is Right frac. ""In 2011, America's ..."" but in this specific case it is getting divided into two as "Right frac. ""In 201 and America's ...""" and getting imported into two separate columns. Any Solution to this issue? Thanks, Monalisa Mona Mona, The embedded comma is the issue, if you just replace the delimiter all will work. I bloged about this. One method is to use PowerShell: http://geekswithblogs.net/Compudicted/archive/2011/09/22/how-to-remove-quotes-and-replace-the-delimiter-in-a.aspx and another is the Script Transformation: http://geekswithblogs.net/Compudicted/archive/2011/09/19/ssis-how-to-remove-occasional-quotes-and-replace-the-column.aspxArthur My Blog
May 16th, 2012 11:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics