Text to Columns macro help - use comma as delimiter with commas embedded in fields and retain text qualifier

[jeff_cp]

I'm building a macro / button to import a cap file (simply a txt file renamed to a a file extension of cap) that contains 68 fields, separated by commas. These fields are a mix of numeric and string values and some string values contain commas embedded. The string fields are wrapped in quotes.

What I'm trying to do is split the fields into 68 separate columns, ignoring commas that are in fields wrapped in quotes and retain the text qualifiers for string fields. Currently, I can retain the text qualifiers but if there are commas embedded in the string fields, it's splitting those fields on every comma, which is throwing off the columns.

Is this possible?

This is what I have thus far:

Sub ImportINVENT_CAP()
Dim fName As String, LastRow As Long

Sheets("INVENT.CAP").Select
    Range("B18").Select

fName = Application.GetOpenFilename("Cap Files (*.cap), *.cap")
If fName = "False" Then Exit Sub

LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("B" & LastRow))
            .Name = "sample"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(2, 2, 2, 1, 1, 2, 1, 1, 2, 2, 2, 2, 1, 2, 2, 1, 2, 2, 2, 1, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 1, 1, 1, 1, 2, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

 

[Logit]

Please post a small sample of your CAP file for download. This site does not provide a means for such ... you will need to upload
the sample file to a Cloud site and provide the download link here. Thanks.

 

[jeff_cp]

Please post a small sample of your CAP file for download. This site does not provide a means for such ... you will need to upload
the sample file to a Cloud site and provide the download link here. Thanks.

I'll have to mock up some data as the data in my file is proprietary.

 

[Logit]

That is fine.

 

[jeff_cp]

INVENT.CAP

Shared with Dropbox

www.dropbox.com

That is fine.

 

[jeff_cp]

FYI...there are only 3 fields out of the 68 total fields that could potentially contain commas. These are fields 7, 24 and 27.

 

[Logit]

Does the CAP file have column headers ? If so, please post another example file for download with the headers as well.

 

[Logit]

OK ... I managed to create a small project that will process your CAP file into Excel.

PROBLEM: After processing the file, there multiple columns that display the QUOTE SYMBOLS ( "" ). The issue is there is some other type of invisible TEXT within the cell.
I tried to include code in the macro that would delete those columns as well but was not successful.

Do you have any idea what other data might be included in those cells ?

 

[Logit]

The other approach would be to delete those columns without a reference to what might be in the cells. Will the columns that display
the QUOTE SYMBOLS ( "" ) always be the same columns ?

 

[jeff_cp]

The other approach would be to delete those columns without a reference to what might be in the cells. Will the columns that display
the QUOTE SYMBOLS ( "" ) always be the same columns ?

The cap file doesn't have column headers. The columns that display the quotes will always be the same columns. So in the sample file I provided, if the field is wrapped in quotes, those fields will ALWAYS be wrapped in quotes.