Importing select data from Notepad to Excel issue

[CaseyH]

I apologize in advance if this has been covered in a previous post...


I have a couple hundred pages of trend data (in notepad) and I would like to move it into Excel. However, at each page break there is an automated message/header repeated. I do not want this information to come over. I know a few different ways to prevent that from happening, but I wanted to know if there was some sort of macro/automation I could create and it would do this automatically, i.e. delete all non trend data. Something that I could apply to all future trend data files I receive in the future as well.

 

[Trevor_S]

It can be done by macro ... but I can think of two different ways, depending on how the header rows can be identified:

• If the header lines all start with the same text, you could get the macro to ignore lines starting with that text.
• If the header lines are at regular intervals (e.g. lines 1, 51, 101, etc.), you could calculate the line numbers to ignore.

This is the basic code - it displays a File Open window, then writes the contents of the file into the active worksheet, starting in cell A1:

Sub ImportMacro()
fName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
Open fName For Input As #1
wRow = 1
Do While Not EOF(1)
 Line Input #1, TextLine
 ActiveSheet.Cells(wRow,1).Value = TextLine
 wRow = wRow + 1
Loop 
Close #1
End Sub

To skip all lines beginning "Header line", use this code:

Sub ImportMacro()
IgnoreRow = "Header line"
fName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
Open fName For Input As #1
wRow = 1
Do While Not EOF(1)
 Line Input #1, TextLine
 If Left(TextLine, Len(IgnoreRow)) = IgnoreRow Then
  'Skip this row
 Else
  ActiveSheet.Cells(wRow,1).Value = TextLine
  wRow = wRow + 1
 End If
Loop 
Close #1
End Sub

To skip lines 1, 101, 201, etc., use this code:

Sub ImportMacro()
IgnoreRow = 1
IgnoreRept = 100
fName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
Open fName For Input As #1
wRow = 1
rRow = 1
Do While Not EOF(1)
 Line Input #1, TextLine
 If (rRow - IgnoreRow) / IgnoreRept = Int((rRow - IgnoreRow) / IgnoreRept) Then
  'Skip this row
 Else
  ActiveSheet.Cells(wRow,1).Value = TextLine
  wRow = wRow + 1
 End If
 rRow = rRow + 1
Loop 
Close #1
End Sub

 

[CaseyH]

Thank you so much for the macro codes, my data lists are of the same number of lines per "page" so I will try the appropriate macro you listed and let you know of the success of it.

 

[CaseyH]

Thank you so much for the macro codes, my data lists are of the same number of lines per "page" so I will try the appropriate macro you listed and let you know of the success of it.