InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 06:32:56 GMT20http://www.sqlservercentral.com/Forums/Topic1406531-391-1.aspx[quote][b]Jeff Moden (1/13/2013)[/b][hr][quote][b]jerry-621596 (1/13/2013)[/b][hr]SSIS would handle this very nicely and be easily repeatable.[/quote]Agreed. ACE drivers with OPENROWSET would also work well.[/quote]Thanks, I'll research that.Mon, 14 Jan 2013 04:49:27 GMTFrank Cazabonhttp://www.sqlservercentral.com/Forums/Topic1406531-391-1.aspxSorry, I should have said this was SQL Server 2008 R2 Express. I don't think SSIS is available.Mon, 14 Jan 2013 04:48:14 GMTFrank Cazabonhttp://www.sqlservercentral.com/Forums/Topic1406531-391-1.aspxThanks Dwain,I'll try that outMon, 14 Jan 2013 04:47:28 GMTFrank Cazabonhttp://www.sqlservercentral.com/Forums/Topic1406531-391-1.aspx[quote][b]jerry-621596 (1/13/2013)[/b][hr]SSIS would handle this very nicely and be easily repeatable.[/quote]Agreed. ACE drivers with OPENROWSET would also work well.Sun, 13 Jan 2013 20:39:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1406531-391-1.aspxSSIS would handle this very nicely and be easily repeatable.Sun, 13 Jan 2013 19:23:03 GMTjerry-621596http://www.sqlservercentral.com/Forums/Topic1406531-391-1.aspxI'm not sure if this will help you, but the following code will replace the comma embedded between quotation marks (as long as it doesn't appear in the first position) with a semicolon.[code="sql"];WITH MyData (ID, strcol) AS ( SELECT 1, '"BUNGALOW 101,RIDGEWAY DR","POINT FORTIN",,,"TT","868-389-9769",,"ZPXB303191",1'), NewData (ID, ItemNumber, strcol) AS ( SELECT ID, ItemNumber ,strcol=CASE WHEN CHARINDEX(',', Item) > 1 THEN REPLACE(Item, ',', ';') ELSE Item END FROM MyData CROSS APPLY PatternSplitCM(strcol, '["]'))SELECT ID, ( SELECT strcol + '' FROM NewData b WHERE a.ID = b.ID ORDER BY ItemNumber FOR XML PATH(''))FROM NewData aGROUP BY ID[/code]The PatternSplitCM FUNCTION can be found in the 4th article linked into my signature line.You would need to decide what character was appropriate to use in place of semicolon, then REPLACE it back to comma after BULK INSERT completes its task (if that is possible).Sun, 13 Jan 2013 18:44:35 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1406531-391-1.aspxI am receiving a text file with comma separated data with quotation marks around certain fields that I need to bulk insert into SQL Server 2008 R2. An example of the data is:"BUNGALOW 101,RIDGEWAY DR","POINT FORTIN",,,"TT","868-389-9769",,"ZPXB303191",1As you can see there is a comma in the first field.Using this Bulk Insert command causes that first field to be split into two columns. I know BOL says this is what is supposed to happen and to choose the separators carefully, but I have no control over the text file which is created by a 20 year old system which cannot be changed.Any ideas how I can import this?Sun, 13 Jan 2013 16:48:30 GMTFrank Cazabon