compare rows in a text file

[hrm1220]

Is there a way to compare rows in a text file usning VBA (Excel 2003)? I need to compare the rows before I import that text file data into excel. So, row 1 is the headers, and rows 2 to 70,000 is the data that I need to compare. I need to see if row 3 and row 2 have the certain data, etc.

the text file is already filtered and in accending order, I just now need to loop through the text file to see if there is a certain criteria met. This criteria I'm unable to put in the filtering since is based on the previous and current row.
Thank you in advance.

 

[macropod]

hi HRM,

For the basic import, you could use code like:

Sub ImportFile()
Dim Counter As Long
Dim X As Long
Dim N As Long
Open "C:\myCSVFile.csv" For Input As #1 'change as required
Do While Not EOF(1)
    Line Input #1, FileLine
    Counter = Counter + 1
    X = Split(FileLine, ",")
    For N = 0 To UBound(X)
        Cells(Counter, N + 1) = X(N)
    Next N
Loop
Close #1
End Sub

There are various points in the Do While loop where you could do your compare. For example, you could insert an IF test around the lines 'Counter = Counter + 1' to 'Next N' to test the data content before writing the data. Alternatively, you could test the data in a particular column after the For ... Next loop has executed, perhaps deleting the line's contents (and decrementing the counter by 1) if the test fails to meet your criteria.

Cheers

[MS MVP - Word]

 

[Remou]

You can run queries on a text file with ADO.

 

[CautionMP]

hrm1220,
Or combine the two, use an ADO Stream to read the files...

Sub CompareFiles()
  'Requires ADO 2.5 or newer
  Dim strm1 As ADODB.Stream, strm2 As ADODB.Stream
  Dim strBuffer1 As String, strBuffer2 As String
  
  Set strm1 = New ADODB.Stream
  Set strm2 = New ADODB.Stream
  
  strm1.Type = adTypeText
  strm1.Charset = "ascii"
  strm1.LineSeparator = adCRLF
  strm1.Open
  strm1.LoadFromFile "[b]C:\File1.txt[/b]"
  
  strm2.Type = adTypeText
  strm2.Charset = "ascii"
  strm2.LineSeparator = adCRLF
  strm2.LoadFromFile "[b]C:\File2.txt[/b]"
  
  While Not strm1.EOS
    strBuffer1 = strm1.ReadText(adReadLine): strBuffer2 = strm2.ReadText(adReadLine)
    If strBuffer1 <> strBuffer2 Then
      'They don't match so do something with them
      Stop
    End If
  Loop
  
  strm1.Close
  Set strm1 = Nothing
  strm2.Close
  Set strm2 = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)