Editing Text in Notepad

Question

I need help editing text in a Notepad .csv file. I have a large file that has too many rows for Excel. I need to open the file in Notepad, remove the first 15 rows of the file in Notepad, and save it as a .txt file. If possible, I would like to be able to select a folder that has subfolders which contain multiple .csv that I need to run this Macro on. The first 15 lines do not always contain the same text. Can someone help me out with this?

Thanks

What does this have to do with excel? At which point does Excel get involved in this? — David G, Jul 12 '16 at 14:58
If it's ok to do this using a batch script, please refer to http://stackoverflow.com/questions/11428692/batch-file-to-delete-first-3-lines-of-a-text-file You can also enhance it to process multiple files inside subfolders — , Jul 12 '16 at 15:02
Notepad has no macro capability: please state the problem without assumptions about tools and then describe what you have tried. — Richard, Jul 12 '16 at 15:03
@Richard - By macro, I guess he's referring to the procedure that removes first 15 lines — , Jul 12 '16 at 15:04
@JaganathanNanthakumar That is my guess also; but too many terms thrown together to be sure. — Richard, Jul 12 '16 at 15:06
The [Workbooks.OpenText method](https://msdn.microsoft.com/en-us/library/office/ff837097.aspx) has a **StartRow** parameter that you can set to **16**. I do find it odd that some external process consistently exports CSV files with 1,048,591 rows though. — , Jul 12 '16 at 15:10
if it's really to large for excel, try a perl script, if you know that language, or python or something of the sorts — Scott Holtzman, Jul 12 '16 at 15:17
Sorry for the confusion. The only use for Excel is to open the .csv files in order to edit them. I'm not too familiar with any other coding languages. I thought that there would be a way to edit Notepad files through running an Excel VBA Macro. So far I have just been able to open a .csv file in Notepad and that's where my knowledge of this ends. @JaganathanNanthakumar, what software would I run a batch script in? Can I use the StartRow functionality for a text file? I hope this makes things a little clearer. — Dan, Jul 12 '16 at 15:31
There's no such thing as a "Notepad file". Notepad will open just about any type of file :) but if your data is CSV (or any standard delimited type) you can open it in Excel either directly with the `OpenText` method of `Workbooks` class, or you can use I/O built-in functions like @LoganReed answers, below. — David Zemens, Jul 12 '16 at 15:39

score 4 · Answer 1 · edited May 23 '17 at 11:51

Here is an example of a procedure that removes top 15 lines from any text file (regardless of the contents of those lines). It should be able to handle arbitrarily large files as it works on one line at a time.

Sub removeTopLines()

    Dim srcFile As String, nSrc As Integer  ' source file (read from)
    Dim dstFile As String, nDst As Integer  ' destination file (write to)
    Dim textLine As String

    Const LINES_TO_SKIP = 15
    Dim lineCounter As Long

    srcFile = "c:\opt\src.txt"
    dstFile = "c:\opt\dst.txt"

    nSrc = FreeFile
    Open srcFile For Input As #nSrc

    nDst = FreeFile
    Open dstFile For Output As #nDst

    lineCounter = 1
    Do Until EOF(nSrc)
        Line Input #nSrc, textLine
        If lineCounter > LINES_TO_SKIP Then
            Print #nDst, textLine
        End If
        lineCounter = lineCounter + 1
    Loop

    Close #nDst
    Close #nSrc

End Sub

You can see an example of how to traverse a directory tree here, or alternatively you can just get a list of all those file path names and call this procedure from another loop giving it one file at a time.

Update: Here is another version that instead of a line count looks for a string that contains "time" and copies only the lines after that.

Sub removeTopLinesAfter()

    Dim srcFile As String, nSrc As Integer  ' source file (read from)
    Dim dstFile As String, nDst As Integer  ' destination file (write to)
    Dim textLine As String, strAfter As String

    strAfter = "time"
    Dim copyLines As Boolean

    srcFile = "c:\opt\src.txt"
    dstFile = "c:\opt\dst.txt"

    nSrc = FreeFile
    Open srcFile For Input As #nSrc

    nDst = FreeFile
    Open dstFile For Output As #nDst

    copyLines = False
    Do Until EOF(nSrc)
        Line Input #nSrc, textLine
        If Not copyLines Then
            copyLines = InStr(textLine, strAfter) > 0
        Else
            Print #nDst, textLine
        End If
    Loop

    Close #nDst
    Close #nSrc

End Sub

The only problem with this is that his csv file is too big for Excel to open, so this is going to take a very long time to process. @[Jaganathan Nanthakumar](http://stackoverflow.com/users/6077173/jaganathan-nanthakumar) suggestion of using a batch file to skip the lines will save him a ton of time. If it really has to be in VBA, it would still be quicker to create a wscript shell for running the batch commands. — Tim, Jul 12 '16 at 16:06
How would I adjust this so that instead of a constant 15 lines, the line that includes the word "time" would be my value for LINES_TO_SKIP? The word "Time" only appears once in the file — Dan, Jul 12 '16 at 16:07
Dan, I've updated the example with the version that looks for a "trigger string" to start the copy. — Logan Reed, Jul 12 '16 at 16:22
Thanks Logan, @Tim how would I run a batch file through a wscript shell? I have no experience using batch files at all? — Dan, Jul 12 '16 at 16:41
@Dan See this post: http://stackoverflow.com/questions/37822050/cant-run-dir-from-wscript-shell-in-vba Its a function I frequently include in my remote systems management workbooks so I can run WINRS commands against lists of remote PCs. — Tim, Jul 12 '16 at 16:49

Editing Text in Notepad

1 Answers1