How to read values from Txt File as unique values

[Luthius]

Guys
I have some values inside a txt file and I would like to "read them" importing them as unique values in my excel spreadsheet using VBA .

The values are separated by comma:

• Apple, Beer
• Orange,Peach,Pineaple
• Wine
• Water,
• Peach,Wine
• Orange,Apple,Water
• etc.

If possible, also sort these unique values as well.

Luthius

 

[JazzSP8]

Can you not change the extension to ".csv" and open it?

Excel should separate them on it's own then.

Or just get the data in there and use Data > Text to Columns and use "," as the delimiter when prompted.

 

[Rick Rothstein]

Does this macro do what you want (change the path/filename to match your file's actual path and name)(output to Column A starting at Row 1)...

[table="width: 500"]
[tr]
	[td]Sub UniqueTextFileItems()
  Dim R As Long, FileNum As Long, TotalFile As String, Data As Variant
  FileNum = FreeFile
  Open "[B][COLOR="#FF0000"]c:\temp\test.txt[/COLOR][/B]" For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    TotalFile = Space(LOF(FileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , , TotalFile
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
  Data = Split(Join(Split(TotalFile, vbCrLf), ","), ",")
  With CreateObject("Scripting.Dictionary")
    For R = 0 To UBound(Data)
      If Len(Data(R)) Then .Item(Data(R)) = 1
    Next
    Data = .Keys
  End With
  With CreateObject("System.Collections.ArrayList")
    For R = 0 To UBound(Data)
      .Add Data(R)
    Next
   .Sort
    Range("[B][COLOR="#FF0000"]A1[/COLOR][/B]").Resize(.Count) = Application.Transpose(.ToArray)
  End With
End Sub[/td]
[/tr]
[/table]

 

[Luthius]

Thanks for both solutions.

Rick, is it possible you explain about the line

[COLOR=#333333][FONT=monospace]  Data = Split(Join(Split(TotalFile, vbCrLf), ","), ",")[/FONT][/COLOR]

Sounds a bit complicated to understand it.

 

[Rick Rothstein]

Thanks for both solutions.

Rick, is it possible you explain about the line

[COLOR=#333333][FONT=monospace]  Data = Split(Join(Split(TotalFile, vbCrLf), ","), ",")[/FONT][/COLOR]

Sounds a bit complicated to understand it.

The TotalFile variable contains the entire text file. Each line of the text file is separated by a Carriage Return, Line Feed combination of characters (that how PCs separate lines in a text file) and vbCrLf is a built in constant equal to that two character combination. The Split function breaks the text file into an array of separate lines by using the Carriage Return, Line Feed combination as its delimiter. Since each cell contains comma separated values, I rejoined the array of individual lines using a comma for the delimiter... this created a comma delimited list of all the lines in the file. Finally, I split this long list of lines specifying the comma as the delimiter which created an array consisting of all the lines in the file which I then assigned to the variant Data variable.

 

[Luthius]

Thanks for the explanation. Clever solution.
Lets consider closed this thread. Thanks again