Excel Comma Delimited

sunshine25

New Member
Joined
Dec 14, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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:

VBA 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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
.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

New Member
Joined
Dec 14, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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:
VBA Code:
.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:
1607952309076.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
Yes my data looks like this:
1607952309076.png
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

New Member
Joined
Dec 14, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,892
Messages
5,598,702
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top