Automating Notepad data before importing to Excel (using vba)

[ElvenvdK]

[h=2]Hi,
I have pieced together some code to import and delimit a .txt file, but it’s so slow because the .txt file has about > 15k rows. What it does is imports the .txt file, then chops off about 75% of the data that I don’t need. Again since it is very slow, does anyone know how to basically automate the deletion of the unwanted rows in Notepad before I import it to excel? Thanks so much in advance![/h]

 

[LockeGarmin]

One idea would be to just create a brand new text file and write to it line by line based on some criteria. Make sure you specify a condition in the If statement and if you want you can just have the program create a brand new file if one doesn't exist with the Create parameter of the FileSystemObject.OpenTextFile method set to 'True'. You'll also want to modify the file paths of the Write and Read Files.

Option Explicit

Sub FilterTextFile()


Dim FSO As Object 'Scripting.FileSystemObject '
Dim ReadFile As Object 'Scripting.TextStream '
Dim WriteFile As Object 'Scripting.TextStream '


Dim TextLine As String


Const fsoForReading As Long = 1
Const fsoForWriting As Long = 2
Const fsoForAppending As Long = 8


  Set FSO = CreateObject("Scripting.FileSystemObject")
  
  Set ReadFile = FSO.OpenTextFile("C:\Folder\Test.txt", fsoForReading)
  Set WriteFile = FSO.OpenTextFile("C:\Folder\Test 2.txt", fsoForWriting, Create:=False)
  
  Do Until ReadFile.AtEndOfStream
    TextLine = ReadFile.ReadLine
    
    If TextLine = "#SOME CONDITION HERE#" Then
      WriteFile.WriteLine TextLine
    End If
    
  Loop
  
  ReadFile.Close
  WriteFile.Close
  
End Sub

 

[ElvenvdK]

[h=2]LockeGarmin<o></o>[/h][h=2]Thanks so much for the help with thecode. Turns out that it’s been decidedthat it isn’t a good idea, doing edits to the .txt file before bringing it intoExcel. One thing I did want to askthough is that, I’m trying to delete rows based on criteria obviously. Everything’s good, but the code needs like 3tries because sometimes it skips rows that need to be deleted. I don’t understand really. I’ll post the code shortly. I’m pretty new to this forum. (fyi - the +Reply to Thread toggle crashes Chrome)<o></o>[/h]

 

[LockeGarmin]

The code I provided wouldn't actually make any changes to the original text file just in case that was your concern. But if you are concerned about the difficulty conditioning the lines I could understand that.

To answer your question you just need to make sure that when you are looping through a range of cells for the purpose of deleting you always have to count backwards. Below are some examples you can run.

Sub Delete_Rows_And_Miss_Some()

Dim i As Long

  'Add Data for the Example
  [A1:A10].Value2 = Excel.WorksheetFunction.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

  For i = 1 To 10
    Rows(i).Delete
  Next i

End Sub

Sub Delete_All_Rows_Correctly()


Dim i As Long

  'Add Data for the Example
  [A1:A10].Value2 = Excel.WorksheetFunction.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
  
  For i = 10 To 1 Step -1
    Rows(i).Delete
  Next i

End Sub