SSIS - remove character X unless it's followed

Question

Let's say I have the following dataset imported from a textfile:

Data
--------------------
1,"John Davis","Germany"
2,"Mike Johnson","Texas, USA"
3,"Bill "The man" Taylor","France"

I am looking for a way to remove every " in the data, unless it's followed or preceded by a ,.

So in my case, the data should become:

Data
--------------------
1,"John Davis","Germany"
2,"Mike Johnson","Texas, USA"
3,"Bill The man Taylor","France"

I tried it with the import tekst file component in SSIS, but that gives an error when I set the column delimiter to ". If I don't set a delimiter, it sees the comma in "Texas, USA" as a split delimiter....

Any suggestions/ideas? The textfile is too large to change this manually for every line so that's not an option.

Answer 1

Bit of a cop-out on the last '"', but:

Create table #test ([Data] nvarchar(max))

insert into #test values ('1,"John Davis","Germany"'            )
insert into #test values ('2,"Mike Johnson","Texas, USA"'       )
insert into #test values ('3,"Bill "The man" Taylor","France"')

select replace(replace(replace(replace([Data],',"',',~'), '",','~,'),'"', ''),'~','"') + '"'
from #test