Manipulating Text File

[Guest]

I have a text file (*.txt) which contains 130,000 rows of records. It’s
impossible for Excel to import in full. Even if I use MS Query to extract, it
is still not possible because its content is very abstract (combination of
table & words).

I understand Excel VBA can manipulate text files but I have no idea of how
to achieve the below.

Every 130 rows of record makes up 1 page (if I import to Excel). This means,
there’s 1,000 pages in the entire 130,000 rows of records contained in this
text file. For each of the page, I only need its first upper half that is row
#1 to row row # 65. The VBA should delete row # 66 to row # 130 for every
page.

For example: my text file
row 1 ~ 65 (preserve)
row 66 ~ 130 (delete)
row 131 ~ 195 (preserve)
row 196 ~ 260 (delete)
row 261 ~ 325 (preserve)
row 326 ~ 390 (delete)
…..and so on.

My file is located in C:\Database\Scrambled.txt
It's so scrambled I can't tell if delimeter is Tab or Space. But nevermind,
all I need is to delete by rows, as mentioned above.

Thanks a lot

 

[Dave Peterson]

If you're running xl2003 or below, then you won't be able to import all 130,000
rows.

Excel only has 65536 rows per worksheet.

If you could split up the data and import it multiple times, I'd add an extra
column and use a formula like:

=IF(MOD(INT((ROW()-1)/65),2)=1,"Delete","keep")

To mark each row.

====
Or you could use a macro to help clean up the data first.

Option Explicit
Sub testme01()

Dim TextLine As String
Dim recCtr As Long

recCtr = 0

'my test files
'Open "c:\my documents\excel\book4.txt" For Input As #1
'Open "c:\my documents\excel\book4.txt.out" For Output As #2

Open "C:\Database\Scrambled.txt" For Input As #1
Open "C:\Database\Scrambled.txt.out" For Output As #2

Do While Not EOF(1)
Line Input #1, TextLine
recCtr = recCtr + 1
If recCtr < 66 Then
Print #2, TextLine
Else
If recCtr > 130 Then
recCtr = 0
End If
End If
Loop

Close #1
Close #2

End Sub

And import the data in scrambled.txt.out.

 

[PY & Associates]

We like the way recCtr is used.

OP carefully limited entries to 130,000 and only wanted half of them
(65,000)
We would import direct into Excel thus

Sub M()
Dim txt As String
dim f as long
Dim i As Long
Dim j As Integer
i = 1
j = 1
f = FreeFile()
Open "C:\Data\file.txt" For Input As #f
Do While Not EOF
Line Input #f, txt
If (i - Int(i / 130) * 130) < 66 Then
Cells(j, 1) = txt
j = j + 1
End If
i = i + 1
Loop
close #f
End Sub

 

[Dave Peterson]

But you'll still have to parse that data in column A.

And I think I'd stay away from "Dim j as Integer". I'd use "as Long".

We like the way recCtr is used.

OP carefully limited entries to 130,000 and only wanted half of them
(65,000)
We would import direct into Excel thus

Sub M()
Dim txt As String
dim f as long
Dim i As Long
Dim j As Integer
i = 1
j = 1
f = FreeFile()
Open "C:\Data\file.txt" For Input As #f
Do While Not EOF
Line Input #f, txt
If (i - Int(i / 130) * 130) < 66 Then
Cells(j, 1) = txt
j = j + 1
End If
i = i + 1
Loop
close #f
End Sub