Разбор csv и замена запятых, заключенных в двойные кавычки..

---

Здравствуйте Гуру

У меня есть очень сложный csv-файл, который содержит данные, как показано ниже, фактически я удалил несколько столбцов, все еще это длинная строка, но я хочу привести такую сложную строку в качестве примера, чтобы получить лучший результат..

10.0,"[""Categories""]",,0,,,,,"[""Automotive and Transport"", ""Business and Finance"", ""Chemicals and Materials"", ""Company Reports"", ""Consumer Goods and Services"", ""Country Reports"", ""Energy and Natural Resources"", ""Food and Beverage"", ""Government and Public Sector"", ""Healthcare"", ""Humanities Books"", ""Industry Standards"", ""Manufacturing and Construction"", ""Military Aerospace and Defense"", ""Pharmaceuticals"", ""Science Books"", ""Telecommunications and Computing""]","[""Automotive"", ""Commercial Aerospace"", ""Maritime"", ""Private Transport Services"", ""Public Transport"", ""Rail"", ""Banking"", ""Business"", ""Economics"", ""Finance"", ""Insurance"", ""Legal"", ""Property"", ""Adhesives and Sealants"", ""Advanced Materials"", ""Ceramics"", ""Chemicals"", ""Composites"", ""Glass"", ""Metals and Minerals"", ""Nanomaterials"", ""Packaging"", ""Plastics"", ""Pulp and Paper"", ""Recycling and Recycled Materials"", ""Rubber"", ""Textiles"", ""Wax"", ""Wood and Wood Products"", ""Arts and Crafts Supplies"", ""Baby and Child Products"", ""Clothing"", ""Consumer Electronics"", ""Consumer Services"", ""Household"", ""Media and Entertainment"", ""Personal Care Products"", ""Retail"", ""Sporting Goods and Equipment"", ""Toys"", ""Travel and Tourism"", ""ICON Country Market Reports"", ""Biofuels"", ""Combined Heat and Power (CHP)"", ""Electricity"", ""Energy Maps"", ""Energy Storage"", ""Environmental"", ""Fossil Fuels"", ""Mining"", ""Nuclear Power"", ""Pipelines"", ""Renewable Energy"", ""Utilities"", ""Water"", ""Agriculture"", ""Beverage"", ""Food"", ""Food Ingredients"", ""Food Processing"", ""Food Safety"", ""Food Service"", ""Tobacco"", ""Government"", ""International Relations"", ""Public Sector"", ""Biotechnology"", ""Healthcare Services"", ""Laboratory Equipment"", ""Medical Devices"", ""Library and Information Science"", ""Political Science"", ""Psychology"", ""Society and Social Sciences"", ""Construction"", ""Engineering"", ""Manufacturing and Industry"", ""Security Services"", ""Ammunition"", ""Biodefense"", ""C4ISR"", ""Homeland Defense"", ""Infantry Weapons and Equipment"", ""Military Aircraft"", ""Military Logistics"", ""Military Unmanned Systems"", ""Missiles and Missiles Technology"", ""Naval Vessels"", ""Radar Systems"", ""Tanks and Armored Vehicles"", ""Anesthetic Drugs"", ""Animal Pharmaceuticals"", ""Antibiotics"", ""Biopharmaceuticals"", ""Clinical Trials"", ""Drug Delivery"", ""Drug Discovery"", ""Drugs by Therapeutic Area"", ""Generic Drugs"", ""Oncology Drugs"", ""Over the Counter (OTC) Drugs"", ""Pharmaceutical Intermediates"", ""Pharmaceutical Manufacturing"", ""Pharmacoeconomics"", ""Vitamins and Dietary Supplements"", ""Biology and Life Sciences"", ""Chemistry"", ""Geography"", ""Mathematics"", ""Physics"", ""Computing and Technology"", ""Internet and E-Commerce"", ""Telecommunications and Networks""]","","width=device-width, initial-scale=1.0, maximum-scale=1.0",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"{""Content-Type"":""text/html; charset=utf-8"",""Via"":""1.1 varnish"",""Fastly-Debug-Digest"":""e8f4fd7caeda67cf3daa747e38e1310cb8d84d9e2af22e6156b3d792b4ef7821"",""Content-Length"":""400501"",""Accept-Ranges"":""bytes"",""Date"":""Wed, 05 Apr 2017 21:56:34 GMT"",""Age"":""16376"",""Connection"":""keep-alive"",""X-Served-By"":""cache-lhr6350-LHR, cache-iad2128-IAD"",""X-Cache"":""HIT, HIT"",""X-Cache-Hits"":""1, 1"",""X-Timer"":""S1491429394.073711,VS0,VE7"",""Cache-Control"":""no-cache, no-store, private, must-revalidate, max-age=0, max-stale=0, post-check=0, pre-check=0"",""Expires"":""0"",""Pragma"":""no-cache""}"

Как вы можете видеть, есть дополнительные запятые в составе вложенных двойных кавычек.. Я хочу проигнорировать их и заменить разделитель запятой любым уникальным символом. Эти данные имеют много специальных символов, таких как |, &, ^ и т. д. поэтому мне придется принять решение об уникальном разделителе.

Ищете что-то в powershell или vbscript

Я записал макрос, открыв csv-файл в excel, а затем заменил все запятые на "##". Это работает вручную, но когда макрос выполняется, я могу видеть много строк с запятыми, заключенными в двойные кавычки.

Файл на самом деле огромен около 7 ГБ так что на самом деле я не могу сделать это вручную

Что я уже пробовал:

Set wb = Application.Workbooks.Open(ToPath, UpdateLinks:=0)

    DisplayAlerts = False
    ActiveSheet.Cells.Select
    Cells.Replace What:=",", Replacement:=";", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    wb.Close savechanges:=True
    Application.WindowState = xlNormal

---