I am facing an issue while doing SQL Server BULK INSERT into Database table from csv file. And the references say, SQL server doesn't ignore commas that are part of strings enclosed with double quotes.

My file look like this


Desired output

I myself thinking of 2 approaches for this.

1) Replace commas as part of the strings
enclosed in double quotes to "|"

The reason I am trying to replace with | is to replace the same again with ',' while loading the data into final tables. As I can't do this with SQL Server, I want to replace this either by using VBA.
I think this can done with regular expression but I am really poor in understanding it.

2) csv to tab delimited conversion - I developed the below code after referring to other blogs to replace the commas with vbtab delimiter and ignore the commas enclosed in the double quotes.

Sub csv2tab()

Const ForReading = 1

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\smallerfile_11.csv", ForReading)

Do Until objFile.AtEndOfStream

    strLine = objFile.ReadLine

    strReplacer = Chr(34) & "," & Chr(34)

    strLine = Replace(strLine, strReplacer, vbTab)

    strLine = Replace(strLine, Chr(34), "")
    strNewText = strNewText & strLine & vbCrLf



Set objFile = objFSO.OpenTextFile("C:\test.txt", ForWriting)

objFile.WriteLine strNewText


End Sub
I would be happy with any solution. Thanks for your time..