6

I encountered a situation tonight where I wanted to parse a text file. I had a very, very long word list that contained English words delimited by lines. I wanted to get rid of every word (or line) that was longer than 7 characters. This would be simple in Linux but I can't seem to find a simple solution in Windows XP. I tried using Notepad++ regular expression search, but that was a huge failure. I tried using the expression .{6,} without finding any matches. I'm really at a loss because I thought this sort of thing would be extremely easy and there would be tons of tools to accomplish a task like this. It seems like Notepad++ supports every other feature in the world except the very basic ones that seem the most obvious.

Another one of my goals was to put some code before and after the word on each line.

aardvark
apple
azolio

would turn into

INSERT INTO Words (word) VALUES ('aardvark');
INSERT INTO Words (word) VALUES ('apple');
INSERT INTO Words (word) VALUES ('azolio');

What suggestions/tools/tips do you have to accomplish tasks similar to this in Windows XP?

13 Answers 13

10

To add the SQL text, you could try this command prompt one liner:

(for /f %i in (words.txt) do @echo INSERT INTO Words ^(word^) VALUES ^('%i'^)) > words.sql

To filter out lines in a text file longer than 7 characters, you could use another command line tool, findstr:

findstr /v /r ^.........*$ words.txt > shorter-words.txt

The /r option specifies that you want to use regex matching, and the /v option tells it to print lines that do not match. (Since it appears that findstr doesn't allow you to specify a character count range, I faked it with the "8 or more" pattern and the "do not match" option.)

2
  • This is actually quite fast and amazing. I never knew you could do this with windows command prompt! Aug 22, 2009 at 16:02
  • It managed to do the findstr command on a 1.66MB in just a few seconds. It then did the SQL portion of it in under 1 minute. Very impressive. Aug 22, 2009 at 16:03
6

Perl for sure, simply paste this script and run it in the same directory as the wordlist. Change your wordlist name to words.txt or alter the name in the script. You can redirect the output to a new file like so:

words.pl > list.txt

without further avail (whipped it together quick, can be chopped down a fair bit):

open FILE, "words.txt" or die $!;

my @words = <FILE>;

foreach $word(@words)
{
    print $word if(length($word) <= 8);
}
4

You can get the GNUWin32 sed for Windows XP.
Similarly AWK and Perl too.
That is if you are used to Unix scripting (if so also consider Cygwin).

Otherwise there is also PowerShell.

4

gVim is a worthy editing tool that has its origins in the venerable vi used on Unix systems. You will want to use the substitute command to do global search/replacements for each word.

AWK and Perl are very powerful tools, but overkill for what you need. You'll enjoy gVim since it is an editor first and foremost. The thing that rocks with gVim is that you are only one keystroke away from giving it a search/substitute/replace command which can be specified with the robust regular expression format.
Good luck.

3

Massively underestimated as a development tool is Microsoft Excel (or OpenOffice Spreadsheets). There is a max number of lines, but you might be able to take advantage of one of these tools.

Then you can just use the left, mid, if, etc. functions in the Spreadsheet in formulas that go to the right of your lines. They will automatically get copied with relative references.

Many times it's a lot easier than coding, unless you're a coder :) From there you can import, export, and do a lot of cool things even with text.

2
  • 2
    Yep, the main issue is that it can only hold ~65500 lines though :( Aug 22, 2009 at 18:07
  • been there, it's a bummer. have you tried odesk? :) Aug 23, 2009 at 22:36
2

Maybe this is better suited for StackOverflow, because the best advice I can give you is to learn one of the scripting languages to make such tasks easier. It's much better to know one powerful tool than dozens of little ones, IMHO, and it's an investment that pays off.

Downloading Python and going through the tutorial will take a few hours, but afterwards such tasks will seem very easy to you. Better yet, you will learn to recognize tasks "looking for some programming" in other fields as well, and it will increase your productivity tenfold.

3
  • 3
    I know plenty of scripting/programming but I don't really think it's necessary. This is one of those times I'm trying to get used to something that isn't a programming solution. Aug 22, 2009 at 5:46
  • why? wouldn't it be easier to just program it? you also get to keep a script that can be just reused later Aug 22, 2009 at 6:34
  • 2
    This is somewhat of a theoretical question for future reference. I'd much rather have the option of programming OR using a tool Aug 22, 2009 at 15:51
2

I would use TextPad for this.

I've used it extensively for regular expressions in the past.

I'd try finding something like:

  ^[[:alpha:]]{7,}\n

And replacing with nothing.

1

Your expression is wrong. You want this:

^.{0,6}$

1

I know this answer is late, but I think it is worth adding nonetheless. For what it's worth, you can also parse this text file using Notepad++ and regular expressions quite simply.

You said that you were using the following regular expresion, but it did not work:

.{6,}

This expression looks fine and should match all lines which contain 6 or more characters.

How large is the text file? Notepad++ can take a while to execute regular expressions on very large files, so it's possible it was not finished processing.

Note: You state your goal was to remove lines longer than 7 characters - to do this you should use the expression .{8,}

If you are sure this expression did not work, are you sure that your search mode was set to Regular Expression and that ". matches newline" was not set?

To solve your problem I would perform the following steps in notepad++:

Remove all lines longer than 7 characters Perform a find and replace in Notepad++ with the following expression:

Find what:      .{8,}\r?\n?
Replace with:

This will remove any line longer than 7 characters, and also remove the newline and carriage return characters if present.

Replace words with code

Assuming each line simply has a word on it (and no leading/trailing spaces or undesired characters etc.) this can also be done with a simple find and replace:

Find What: (.*)                                         //Match the whole line, store in capture group
Replace with: INSERT INTO Words (word) VALUES ('\1');   //reference value of capure group with \1
0

I second using Excel for this.

Put all your words in column A.

Put this formula in column B:

=IF(LEN(A1)>7,"",CONCATENATE("INSERT INTO Words (word) VALUES ('",A1,"')"))

Copy the formula to all rows.

Each row in column B will have your sql insert command when the length of the word is less than 7. Otherwise it will be blank.

If you want to remove the blank lines, copy and paste as values column B to another column, then just sort the column. The blank lines will be pushed to the bottom.

3
  • This file has +150k words. I do not think Excel will even open it. Aug 22, 2009 at 15:56
  • Yes, you're right, Excel will only do 65536 rows.
    – David Steven
    Aug 22, 2009 at 16:21
  • Excel 2003 and before has these limitations, but if you have it available to you, Excel 2007 has greatly increased these limits. See office.microsoft.com/en-us/excel/HP100738491033.aspx .
    – bobbymcr
    Aug 22, 2009 at 20:28
0

This can be done with a Perl one-liner (getting rid of every word longer than 7 characters):

perl -nle "print if length($_) <= 7" "D:\temp2\input.txt" > ShortWords.txt

Put this in a BAT file or execute directly from a command line window (Run/cmd).

Perl is required to be installed. I use ActivePerl - it is very easy to install as it has a normal Windows installer. Direct download URL.

For the second part of your question (generating the SQL commands): it is just an extension of the first Perl one-liner:

perl -nle "print 'INSERT INTO Words (word) VALUES (\'' . $_ . '\');' if length($_) <= 7" "D:\temp2\input.txt" > SQLcommands.txt

If it gets more complicated then it probably better with a normal Perl script, as suggested by John T.

0

Believe it or not but Microsoft Word in fact has regular expressions too. CTR+H > More > Wild card. The search expression will probably be something like [.]{8+} - press F1 while the Search/Replace dialog is shown to see a description of Word's regexps.

0

You can solve that without any extra tool to download by using a little vbScript or an Excel VBA macro. This is indeed, more a question for stackoverflow.com. The code for that script would run in Excel VBA as well with nearly no change.

A sample VBA (not tested) could be:

Sub filterRows()  
     Dim InputData  
     Open "c:\test.txt" For Input As #1    ' Open file for input.  
     Open "c:\out.txt" For Output As #2  
     Do While Not EOF(1)             ' Check for end of file.  
        Line Input #1, InputData    ' Read line of data.  
        If Len(InputData) <= 7 Then  
            Print #2, InputData  
        End If  
     Loop  
     Close #1    ' Close file.  
     Close #2  

End Sub 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .