How to set up a custom first delimiter in excel and then use standard one

Question

I am trying to create an excel workbook that will automate a reconciliation process and I came across an issue that I can't overcome.

The initial input file is a CSV file that is comma delimited. The problem is that some of the entries have extra comma so when I do text to columns some of the cells have incorrect content. The next cell after first column has text in it so I am trying to find a way to use it as delimiter. Here are how entries look:

First row: a, b, c, d (always delimited with commas hence separate part in code) Second row (in 90% of cases): a, b, c, d Second row (in 10% of cases): a1, a2, b, c, d

B in the above line is a text and has quotes so looks like this "b".

Is there any way to adjust text to column to make it work properly?

Here is the code I have put together so far:

Sub ExampleSplit1()
      Range("A1").TextToColumns _
      Destination:=Range("A1"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=True, _
      Semicolon:=False, _
      Comma:=True, _
      Space:=False, _
      Other:=False, _
      OtherChar:="-"

      Range(Range("A2"), Range("A2").End(xlDown)).TextToColumns _
      Destination:=Range("A2"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=True, _
      Semicolon:=False, _
      Comma:=True, _
      Space:=False, _
      Other:=False, _
      OtherChar:="-"

End Sub

I want the final result to look as follows:

Start here - https://stackoverflow.com/questions/12197274/is-there-a-way-to-import-data-from-csv-to-active-excel-sheet — Frank Ball, Apr 07 '20 at 21:20

score 0 · Accepted Answer · answered Apr 08 '20 at 09:20

Post suggested by @Frank Ball had solution that worked for me. Below is the code

Sub CSV_Import()
Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1") 'set to current worksheet name

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With
End Sub

How to set up a custom first delimiter in excel and then use standard one

1 Answers1