Sorting a text file

[hrm1220]

I need to sort a text file before I can loop through the information.

1.Can some one tell me how to do this?


Here's what I have so far:

Sub TXTQryTbl()

Dim ConnString As String

Dim qt As QueryTable
Dim strtemp As String
Dim intFN As Long
Dim varFields As Variant
Dim i As Long
Dim lrow As Long
Dim varstrip As Variant
Dim test As String
Dim endstate As String, startstate As String
Const sDelim = vbTab
z = 2
t = 2
test = ActiveWorkbook.Worksheets("Utility").Range(Cells(z, 1), Cells(z, 1)).Value
endstate = ActiveWorkbook.Worksheets("Frm").Range("RRFrm_endstate").Value
startstate = ActiveWorkbook.Worksheets("frm").Range("RRFrm_startstate").Value
varstrip = Array(vbLf, vbTab)
        strfilename = "C:\documents and settings\test\desktop\" 
        sfilename = "test_8_20-8_27_2006.txt"
ConnString = "TEXT;" & strfilename
intFN = FreeFile
Open (strfilename & sfilename) For Input As #intFN
Do While Not EOF(intFN)

'remove text not needed
 Line Input #intFN, strtemp
 For i = LBound(varstrip) To UBound(varstrip)
    If strtemp <> "Cascade Report Data" Then
    'strtemp = Replace(strtemp, " ", "")
    varDelim = Split(strtemp, vbTab)
    End If

    If varDelim(0) = CEID Then
        If varDelim(5) = UCase(startstate) Then

 
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 1), Cells(t, 1)).Value = varDelim(0)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 2), Cells(t, 2)).Value = varDelim(1)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 3), Cells(t, 3)).Value = varDelim(2)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 4), Cells(t, 4)).Value = varDelim(3)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 5), Cells(t, 5)).Value = varDelim(4)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 6), Cells(t, 6)).Value = varDelim(5)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 7), Cells(t, 7)).Value = varDelim(6)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 8), Cells(t, 8)).Value = varDelim(7)
        ActiveWorkbook.Worksheets("report").Range(Cells(t, 9), Cells(t, 9)).Value = varDelim(8)
        t = t + 1
        End If
        
    End If
       
 Next i



Loop


End Sub

 

[xlbo]

why not query into excel using MS Query. Then you can just set the orderby clause to sort your data as it is imported into excel...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[hrm1220]

I was having issues with this, it wasn't separating the columns correctly and not giving me the right information. I also need to loop through the file get get certain data based off of user inputs.

 

[hrm1220]

sorry I hit the submit before I was done.

The text file can have as many as 1Million rows.

so I've been trying to do the data query in Excel (I've already set up an ODBC connection for txt,csv, etc.)
The issue that I've been having with this is that the first row is the name of the report and the 2nd row is the header, so I don't know how to have the query look at the 2nd row.

This seemed the best solution. but if not then please let me know how to do the MSQuery w/o pulling the data into excel (in case the results are more than 65K rows) and being able to loop through the query results.

thanks again for your input and insight.

 

[misterstick]

you could try the dos sort utility.
in a dos window:

sort < inputfile.txt > outputfile.txt

mr s. <

 

[xlbo]

>1 million records - probably not one for excel then !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question