One way to deal with the csv file " comma inside quotes" problem.

Sometimes, we got some annoying csv files, they are not properly delimited. When we open it, we saw the "," showing in the file together with the text. And when we try to open it through excel, it won't work either.

One way to deal with this kind of csv file, is:

1. download the csv file, but do not touch it, like opening it or something.
2. change the file name ".csv" to ".txt" without opening it.
3. open an excel, and use "open file" inside the excel to open the file, step by step, use delimiter as "comma". and use the Text_Qualifier field in your Flat file connection manager to as ". This should wrap your data in quotes and only separate by commas which are outside the quotes.

The data looks like below:

 The step to open the new txt file: