Excel Comma Delimited

[sunshine25]

Hi Guys,

I am trying to import a .txt file that will have comma as a delimiter. However one of the fields has a comma (that is not a delimiter) in it. For example 'Cleo will sing, and play the banjo.' I have code that imports it but then it puts the 'and play the banjo' into the next field over. Below is the code:

Sub browseFilePath()
'-----------------------------------------
'Import System Data

    Dim rg As Range
    Dim xAddress As String
    Dim ActSheet As String
   
    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
    Dim xFileName As Variant
    Dim Answer As Integer
   
     Dim c As Long
     Dim splitstring As String
     Dim myarray() As String
    
    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False

    With fileExplorer
    .Filters.Clear
    .Filters.Add "TXT Files", "*.txt"
        If .Show = -1 Then 'Any file is selected
            Cells(2, 41).Value = .SelectedItems.Item(1)
            xFileName = Cells(2, 41).Value
           
            'Create the CSV and import the file

            ActSheet = "System Data"
           
            If sheetExists(ActSheet) Then
                Answer = MsgBox("Are you sure you want to Replace the existing sheet?", vbYesNo + vbQuestion, "Empty Sheet")
                If Answer = vbYes Then
                    Worksheets(ActSheet).Delete
                Else
                    Exit Sub
                End If
            End If


                    Sheets.Add(After:=Sheets(2)).Name = ActSheet
                    Sheets(ActSheet).Cells.Clear
                    Sheets(ActSheet).Activate

                    Set rg = Worksheets(ActSheet).Cells(1, 1)
                    xAddress = rg.Address
                   


                With ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress))
                        .FieldNames = True
                        .RowNumbers = False
                        .FillAdjacentFormulas = False
                        .PreserveFormatting = True
                        .RefreshOnFileOpen = False
                        .RefreshStyle = xlInsertDeleteCells
                        .SavePassword = False
                        .SaveData = True
                        .RefreshPeriod = 0
                        .TextFilePromptOnRefresh = False
                        .TextFilePlatform = 936
                        .TextFileStartRow = 1
                        .TextFileParseType = xlDelimited
                        .TextFileTextQualifier = xlTextQualifierDoubleQuote
                        .TextFileConsecutiveDelimiter = False
                        .TextFileTabDelimiter = True
                        .TextFileSemicolonDelimiter = False
                        .TextFileCommaDelimiter = True
                        .TextFileSpaceDelimiter = False
                        .TextFileTrailingMinusNumbers = True
                        .Refresh BackgroundQuery:=False
                         End With
   
    
        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            Cells(6, 3).Value = "" ' when cancelled set blank as file path.
        End If

    End With
err:
    Exit Sub
   
End Sub

 

[Joe4]

Welcome to the Board!

What exactly does the data you are trying to import look like?

The issue is that when importing the data, it needs a way to differentiate commas that are to be treated as delimiters versus commas that are meant to be treated as literal data.
The way to do that is with a Text Qualifier argument. Most of time, double-quotes are used to indicate that. So that the data might look something like this:

1,Bob,"Cleo will sing, and play the banjo."

So, in this example, the first two commas are meant to be treated as delimiters, and the third is meant to be treated as literal data.

In looking at your code, it looks like your code accounts for that, with this line here:

.TextFileTextQualifier = xlTextQualifierDoubleQuote

So it looks like it wants to use double-quotes to denote literal text.

If your data uses something else, like single-quotes, you need to update this argument to reflect that.
If your data does not contain any sort of text-delimiters, and your data looks something like this:

1,Bob,Cleo will sing, and play the banjo.

then you are going to have a problem, because there is nothing in the data to differentiate literal text commas versus delimiters.
If there is nothing in the data to differentiate the two, then there probably isn't very much you can do that, unless you can define some "hard-and-fast" rule that you can program against (someething like "the first two commas are delimiters and the third is a literal comma).

 

[sunshine25]

Welcome to the Board!

What exactly does the data you are trying to import look like?

The issue is that when importing the data, it needs a way to differentiate commas that are to be treated as delimiters versus commas that are meant to be treated as literal data.
The way to do that is with a Text Qualifier argument. Most of time, double-quotes are used to indicate that. So that the data might look something like this:

1,Bob,"Cleo will sing, and play the banjo."

So, in this example, the first two commas are meant to be treated as delimiters, and the third is meant to be treated as literal data.

In looking at your code, it looks like your code accounts for that, with this line here:

.TextFileTextQualifier = xlTextQualifierDoubleQuote

So it looks like it wants to use double-quotes to denote literal text.

If your data uses something else, like single-quotes, you need to update this argument to reflect that.
If your data does not contain any sort of text-delimiters, and your data looks something like this:

1,Bob,Cleo will sing, and play the banjo.

then you are going to have a problem, because there is nothing in the data to differentiate literal text commas versus delimiters.
If there is nothing in the data to differentiate the two, then there probably isn't very much you can do that, unless you can define some "hard-and-fast" rule that you can program against (someething like "the first two commas are delimiters and the third is a literal comma)

Yes my data looks like this:

 

[Joe4]

Yes my data looks like this:

Then that is a problem. How is Excel supposed to know which commas should be delimiters and which should not?

I often tell people that Excel/VBA is not magic. It is very literal, and can only do what you tell it to. That means everything is "rule driven". Usually, if you can clearly define the rule, you can program the rule. But if you cannot clearly define a rule that works for any situation, you probably are not going to be able to program anything to do what you want.

So if you cannot define which commas Excel VBA should treat as delimiters and which ones they shouldn't, you probably aren't going to be able to do what you want.
How would anyone even looking at it manually be able to tell?

Where exactly are you getting this data from? You may need to go back to that source and tell them that they need to use Text Qualifiers, so then you have data that you can work with. That is a pretty standard thing when creating CSV files (to use Text Qualifiers when there are commas in the data). Even Excel will do that automatically, when creating CSV files from Excel.

 

[sunshine25]

Then that is a problem. How is Excel supposed to know which commas should be delimiters and which should not?

I often tell people that Excel/VBA is not magic. It is very literal, and can only do what you tell it to. That means everything is "rule driven". Usually, if you can clearly define the rule, you can program the rule. But if you cannot clearly define a rule that works for any situation, you probably are not going to be able to program anything to do what you want.

So if you cannot define which commas Excel VBA should treat as delimiters and which ones they shouldn't, you probably aren't going to be able to do what you want.
How would anyone even looking at it manually be able to tell?

Where exactly are you getting this data from? You may need to go back to that source and tell them that they need to use Text Qualifiers, so then you have data that you can work with. That is a pretty standard thing when creating CSV files (to use Text Qualifiers when there are commas in the data). Even Excel will do that automatically, when creating CSV files from Excel.

Thank you very much for this!

 

[Joe4]

You are welcome.

We used to have the same problem years ago with a CSV file that we received from a vendor. It took some effort, but we finally got them to correct the format of the CSV to use Text Delimiters so then we were able to use/process the file.