Help me clean up file

micko_escalade

New Member
Joined
Mar 24, 2007
Messages
15
Hi all!

I need to clean out unnecessary data from a file,
(see this example text file Link),
I'm not sure how to go about this in excel,

basically every file starts with 9 cells that needs to be deleted, then
two cells of real data then one with garbage that needs to be deleted, it goes like that for 40 cells,
then again 10 cells of garbage that needs to be deleted, then
40 of real data and goes like that up to 3000 lines,

I know it sounds confusing but if you take a look at linked file from above it will make more sense, (to save it right click on "clean up.txt" and then save target as)

at the end I need to have
all cells full of data

any help is appreciated!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
micko_escalade

This is a cross posting with OZGRID.
OZGRID is very keen to this behaviour, so you should mention so.

Do you want to change the text file itself or inporting to Excel?
 
Upvote 0
Thanks for the reply,

I've been reading this forum for a while, and while searching solution for my problem I came across OZGRID, didn't know they were linked together,



Do you want to change the text file itself or importing to Excel?
i will be importing text file inside excel
but either way will work I guess, as long as I get at the end rows of data,
see those two screenshots,

before
and
after
 
Upvote 0
Ok
try
Code:
Sub test()
Dim fn As String, ff As Integer, txt As String, i As Long, n As Long, t As Long
fn = "c:\test\test.txt"   '<--- alter to suite
If Dir(fn) = "" Then
     MsgBox "Path or file name is wrong"
     Exit Sub
End If
ff = FreeFile
Open fn For Input As #ff
Do While Not EOF(ff)
     Line Input #ff, txt
     i = i + 1      
     If i > 9 Then
          n = n + 1
          If n <=44 And n Mod 3 <> 0 Then
               t = t + 1
               ReDim Preserve a(1 To t) : a(t) = txt
          End If
          If n = 54 Then n = 0
      End If
Loop
Close #ff
With ThisWorkbook.Sheets(1).Range("a1")
     .Resize(t).Value = Application.Transpose(a)
End With
End Sub
 
Upvote 0
Newbie here :oops:
where do I enter this code?

can you also point me where I can learn more about this code since I recently started using excel.
 
Upvote 0
OK

1) hit Alt + F11 to open vb editor
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 to get back to Excel
4) hit Alt + F8 and select "test" then hit "Run"

Note:

you need to change the lline of

fn = "C:\test\test.txt"
to actual path and file name
 
Upvote 0
Thanks jindon!

I changed the path and followed your instruction,
after hitting run I get "compile error, variable not defined" on
this line "ReDim Preserve a(1 To"
 
Upvote 0
Or if you have multiple .txt files in the same folder and read them all in the same manner then try
Code:
Sub test()
Dim a(), fn As String, ff As Integer, txt As String, 
Dim i As Long, n As Long, t As Long, myDir As String
myDir = "c:\test\"   '<--- alter to suite
If Dir(myDir, vbDirectory) = "" Then
     MsgBox "Path is wrong"
     Exit Sub
End If
ff = FreeFile
fn = Dir(myDir & "*.txt")
Do While fn <> ""
     Open fn For Input As #ff
     Do While Not EOF(ff)
          Line Input #ff, txt
          i = i + 1      
          If i > 9 Then
               n = n + 1
               If n <=44 And n Mod 3 <> 0 Then
                    t = t + 1
                    ReDim Preserve a(1 To t) : a(t) = txt
               End If
               If n = 54 Then n = 0
          End If
     Loop
     Close #ff
     With ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
          .Resize(t).Value = Application.Transpose(a)
     End With
     fn = Dir()
Loop
End Sub
 
Upvote 0

Forum statistics

Threads
1,215,214
Messages
6,123,660
Members
449,114
Latest member
aides

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top