• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get value from text file array

Nu2Java

Member
Hi all... I am trying to search a comma delimited text file for a value. I am using this code to try and achieve the goal, but I am not certain if this is even the right way. I am very new to dealing with arrays. The file I am searching contains around 200 lines which is formatted like this:

Code:
apple, banana, pear, orange, grapes, watermelon

I need to find "orange" which is always located after the 3rd comma. Once I used this code I found, it seems to find the first value in the text file and then if I change what the messagebox returns, I get errors. Hopefully someone can help me better understand this. Thanks for any help :)


Code:
Sub findit
Dim arr
arr = FileToArray(fpath)

MsgBox arr(1, 1) 'print some values
End Sub

Function FileToArray(fpath) As Variant

    Dim txt As String, arr, d, r, c, rv(), u

    'read in the entire file
    With CreateObject("scripting.filesystemobject").opentextfile(fpath)
        txt = .readall()
        .Close
    End With

    arr = Split(txt, ",") 'split lines to an array

    u = UBound(Split(arr(0), "|")) 'assume all lines have same # of fields
    ReDim rv(1 To UBound(arr) + 1, 1 To u + 1) 'size the output array

    'fill the output array
    For r = 0 To UBound(arr)
        d = Split(arr(r), "|")
        For c = 0 To u
            rv(r + 1, c + 1) = d(c)
        Next c
    Next r

    FileToArray = rv

End Function
 
Hi,​
as it's unclear just well elaborate with details the purpose of your VBA function,​
what should be the result according to the text sample …​
Better is to join a text sample file according to forum rules.​
Checking LBound and UBound of the result array may help.​
 
As Marc said, if you attach a sample txt file, it is easier to help. Click the paperclip Attach Files button in lower left below reply box to attach a file.

Looking at your function, it looks like you want to split the data into a matrix. Your first split is doing it by comma. That is a field delimiter in your example. The usual first step is to split by vbcrlf. The 2nd step would be to split each line by the comma to get your fields, not the vertical bar.

Once data is into the array, you can splice the 4th "field" or just iterate each row/record in the array and get the 4th fields value to compare.

Of course you can always just use Excel's OpenText to open the delimited file into a workbook and use Range Find or such. Array approach as you are doing is probably the faster method.
 
As I have an idea without iterating / looping just with some Excel basics but I need a real text file attachment to 'check it out' …​
 
Thanks guys.. I have attached a text file. In this file, I would be searching a value in the entire file that would be located after comma #7. Basically I have a userform with textbox containing a value. If that value is NOT found in the text file, then I proceed with my other code. Sorry for my confusion in the initial question. The next iteration of this would be, if the value is found in todays date... then.
 

Attachments

  • test - Copy.txt
    1.1 KB · Views: 5
As to check if a text file contains something you very do not need to build any array​
so according to your attachment what exactly do you wanna search ?​
As the better explanation accuracy, the more targeted solution …​
 
Yes, you have me thinking more and more about this. I think the original way I was going was to find a value at a specific location. But I think now I can just do a search of the entire file. In my case, its just serial numbers. So in the text file there are random numbers located after comma 7.
 
If you just want to Find if the value exists anywhere in the file or which line it exists, I have a simple method for that. If you then want to do something with those lines, that is another task.

For the original question, I worked up a simple example. You can adapt that possibly. If the value occurs in one field/column, the 1d array can be filtered with Filter().

Some of these routines are just to make testing easier. Not all would likely be needed for your project. You can replace the value of fn with your text file to test that. The StrToTXTFile line is just for testing a simple txt file. That can be deleted.
Code:
Sub Test_DelimitedTextFileToArray()
  Dim fn As String, a, b

  fn = Environ("temp") & "DelimitedTextFile.txt"
  StrToTXTFile fn, "apple,banana,pear,orange,grapes,watermelon" & _
    vbCrLf & "1,2,3,4,5,6"

  a = DelimitedTextFileToArray(fn)
  Show2DArray a

  b = Application.Index(a, 4, 0)
  MsgBox Join(b, vbCrLf)
End Sub

Function DelimitedTextFileToArray(filePath As String, Optional Delimiter As String = ",")
  'PURPOSE: Load an Array variable with data from a delimited text file
  'similar to: Sub DelimitedTextFileToArray() at www.TheSpreadsheetGuru.com

  Dim TextFile As Integer, FileContent As String, LineArray() As String
  Dim DataArray() As String, TempArray() As String, rw As Long, col As Long
  Dim x As Long, y As Long

  'Exit if filepath does not exist
  If Dir(filePath) = "" Then
    MsgBox "File does not exist!" & vbCrLf & filePath, vbCritical, "Macro Ending"
    Exit Function
  End If

  'Initialize row counter variable rw
  rw = 0

  'Open the text file in a Read State
  TextFile = FreeFile
  Open filePath For Input As TextFile
   
  'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

  'Close Text File
  Close TextFile
   
  'Separate Out lines of data
  LineArray() = Split(FileContent, vbCrLf)

  'Read Data into an Array Variable
  For x = LBound(LineArray) To UBound(LineArray)
    If Len(Trim(LineArray(x))) <> 0 Then
      'Split up line of text by delimiter
      TempArray = Split(LineArray(x), Delimiter)
       
      'Determine how many columns are needed
      col = UBound(TempArray)
       
      'Re-Adjust Array boundaries
      ReDim Preserve DataArray(col, rw)
       
     'Load line of data into Array variable
     For y = LBound(TempArray) To UBound(TempArray)
      DataArray(y, rw) = TempArray(y)
     Next y
   End If
     
   'Next line
   rw = rw + 1
     
  Next x

  DelimitedTextFileToArray = DataArray()
End Function

Sub StrToTXTFile(filePath As String, str As String)
  Dim hFile As Integer
  If Dir(FolderPart(filePath), vbDirectory) = "" Then
    MsgBox filePath, vbCritical, "Missing Folder"
    Exit Sub
  End If

  hFile = FreeFile
  Open filePath For Output As #hFile
  If str <> "" Then Print #hFile, str
  Close hFile
End Sub

Function FolderPart(sPath As String) As String
  FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function

'similar to, http://www.cpearson.com/excel/vbaarrays.htm
Public Sub Show2DArray(ByRef myArry As Variant, Optional delimit As String = vbTab)  '<-- Note ByRef modifier.
  Dim x As Long
  Dim y As Long
  Dim s As String

  s = ""
  For y = LBound(myArry, 2) To UBound(myArry, 2)
    For x = LBound(myArry, 1) To UBound(myArry, 1)
      s = s & myArry(x, y) & delimit
    Next x
    If Mid(s, Len(s) - 1, 1) = delimit Then s = Left(s, Len(s) - Len(delimit))
    s = s & vbNewLine
  Next y
  MsgBox s
End Sub
 
Thank you Kenneth... I am going to play around with this and see how it works for me. I appreciate your help and time.
 
Just for giggles, here is the FIND method that also works as a UDF.
Code:
Sub Test_StrFIndInTxtFile()
  Debug.Print StrFindInTxtFile(",24,", ThisWorkbook.Path & "\test - Copy.txt", False)
End Sub

'=StrFindInTxtFile("ken",A4)
Function StrFindInTxtFile(str As String, txtFile As String, Optional tfIgnoreCase As Boolean = True)
  Dim ic As String, s As String, a
  ic = " "
  If tfIgnoreCase Then ic = " /I "
 
  If Dir(txtFile) = "" Then
    StrFindInTxtFile = "NA"
    Exit Function
  End If
 
  s = "cmd /c find /c " & ic & """" & str & """" & " " & """" & txtFile & """"
  a = Split(CreateObject("Wscript.Shell").Exec(s).StdOut.ReadAll, ".TXT: ")
  Select Case a(UBound(a))
    Case 0:
      StrFindInTxtFile = False
    Case Else:
      StrFindInTxtFile = True
  End Select
End Function
 
It looks like you're trying to get the 4th column of data from your text file. Try:
Code:
Sub findit()
Dim arr
fpath = "C:\Users\Smith\AppData\Local\Temp\test - Copy.txt" 'adjust this path and file name!!
arr = FileToArray(fpath)
zzz = Application.Transpose(Application.Index(arr, , 4))
p = Join(zzz, vbLf)
MsgBox p    'print some values
End Sub

Function FileToArray(fpath) As Variant
Dim txt As String, arr, d, r, c, rv(), u
'read in the entire file
With CreateObject("scripting.filesystemobject").opentextfile(fpath)
  txt = .readall()
  .Close
End With
arr = Split(txt, vbCrLf)    'split lines to an array
u = UBound(Split(arr(0), ","))    'assume all lines have same # of fields
ReDim rv(1 To UBound(arr) + 1, 1 To u + 1)    'size the output array
'fill the output array
For r = 0 To UBound(arr)
  d = Split(arr(r), ",")
  For c = 0 To u
    rv(r + 1, c + 1) = d(c)
  Next c
Next r
FileToArray = rv
End Function
 
Last edited:
Power Query can do this sort of thing. In the attached, update cell A1 to your actual path and filename, then right click somewhere in the table at cell B3 and choose Refresh.
 

Attachments

  • chandoo45931.xlsx
    16.3 KB · Views: 2
Wow! Thanks all for the various options... I think this will keep me quite busy trying out everything. But I want to learn more about arrays and such so it will be great. Thanks again to all, much appreciated.
 
According to your attachment both demonstrations playing with arrays and Excel / VBA basics
(see in the Locals window how are the variables V & W at the Stop break) :​
Code:
Sub Demo1a()
        T$ = ThisWorkbook.Path & "\test - Copy.txt":  If Dir(T) = "" Then Beep: Exit Sub
        R& = FreeFile
        Open T For Input As #R
        V = Split(Input(LOF(R), #R), vbCrLf)
        Close #R
        ReDim W(UBound(V))
    For R = 0 To UBound(V)
        W(R) = Evaluate("{""" & Replace(V(R), ",", """,""") & """}")
    Next
        V = Application.Index(W, Evaluate("ROW(1:" & UBound(W) + 1 & ")"), _
                                 Evaluate("COLUMN(" & [A1].Resize(, UBound(W(0))).Address & ")"))
        Stop
End Sub
Code:
Sub Demo1b()
        T$ = ThisWorkbook.Path & "\test - Copy.txt":  If Dir(T) = "" Then Beep: Exit Sub
        R& = FreeFile
        Open T For Input As #R
        V = Split(Input(LOF(R), #R), vbCrLf)
        Close #R
        ReDim W(UBound(V))
    For R = 0 To UBound(V)
        W(R) = Split(V(R), ",")
    Next
        V = Application.Index(W, Evaluate("ROW(1:" & UBound(W) + 1 & ")"), _
                                 Evaluate("COLUMN(" & [A1].Resize(, UBound(W(0)) + 1).Address & ")"))
        Stop
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Back
Top