10: Find and Replace

Here is Textpad's 'find and replace' box. 

We open it by using F8.

You'll notice that most of the options are the same as ''find' - see post 7 for an explanation of these. There are some differences: 

No wrapped search. 'Find next' only works downwards to the end of the file.

Selected text. You can highlight some text, and make changes in the highlighted text only. 

What we're interested in here are the Go buttons. Once you've typed what you want to find into the 'Find' box, choose between:

Find next Finds the next occurence of what's in the 'find what' box

Replace Replaces the current selection with what's in the 'replace with' box

Replace next Replaces the next match of the 'find what' box' with what's in the 'replace with' box

Replace all Replaces every single match of the 'find what' box' with what's in the 'replace with' box

You will almost always use Replace all.

9: Using 'Find' - the magic of 'Mark all'

If you scroll back a way, you'll find my unbelievably bad spreadsheet of 'books on my coffee table'. Well, here it is again, this time to show the power of 'Mark all'.

We open the find window with F5. 

So: I've looked for books with the publisher 'Penguin'. (Note that I've used \tPenguin\t, which means 'tab Penguin tab'. Otherwise, I might accidentally mark a book with the title 'I love my penguin', which isn't what I want.) 

And there they are - two books which match \tPenguin\t, marked with a little triangle called a 'bookmark'.

Now I want to do something with these bookmarked lines. Here's what I can do:

Cut the bookmarked lines 

Copy the bookmarked lines 

Delete the bookmarked lines

I want to copy them to a new document to see them more closely. I go here:

I open a new document, and I paste my bookmarked lines:

It turns out that both my Penguin books are by PG Wodehouse. Now I can look over the data, modify it, whatever I want. 

The only thing I can't do is modify the data and put it back in it's original place. Bookmarking only works in one direction. If I wanted to modify the records and then put them back in the original file, I would have to cut them from this document and paste them onto the end of the original.

8: Find

If you're used to excel, or word, or any normal 'find' system, you're used to the idea that 'find' and 'find and replace' are pretty much the same thing. In textpad, they're not. 

F5 opens the 'find' window

F8 opens the 'find and replace' window

First up: Find. The find window looks like this:

Let's look at the options here:

'Text'. 'Hex' means 'Hexadecimal', and is advanced stuff. Always select 'Text'.

'Match whole words' means exactly that - match sets of characters that end with a space, a full-stop (or other punctuation), or a tab. This is very handy - it means that if you search for 'cat' it will find the exact word 'cat' and not 'concatenate'. Leave this box ticked.

'Match case' means exactly that - it will match capital and lower-case letters. This is handy if you want to find March (the month) but not march (walking in formation). It will mess up your results if you leave it on - a search for 'Animal' will not find 'animal' if this box is ticked. Usually, leave this box unticked.

 3. 'Regular expression'. When you check this box, you turn regexes on and off. If it's ticked, \t means 'tab'. If it's not, \t literally means \t. If your regex doesn't work, it is often because this box is not ticked. Always make sure this box matches what you want to do.

4. 'Wrap searches'. Textpad starts finding from the cursor mark and stops at the bottom of the document, which can be annoying. If you tick this box, it will go to the bottom, then back to the top and start again - which means the whole document gets searched. Leave this box ticked. 

Direction: Up and Down. If you really only want to search in one direction from the cursor point (i.e. not a wrap seach), you can choose the direction here. These are used infrequently.

Extend selection and In all documents. You can search is selected text, or in more than one open document, using these buttons. These are used infrequently.

...and now, the 'Go' buttons:

Find next means 'show me the next match after the cursor' (easy)

Mark all means 'put a marker next to all the matches in the file.' 

Mark all is super handy. Super, super, super handy - I cannot stress enought how handy it is. Read all about it in the next post.

7: \n and \t

Here are two codes that you will use all the time:

\t means tab

\n means new line

Textpad is at its best when using tab seperated values (TSV), which is exactly what we find in excel spreadsheets. Below, you'll see data represented in a spreadsheet...

 

...and in textpad...

...and, finally, here is the first line of data as a 'find' line in textpad:

david nicholls\ttheunderstudy\tHodder & Stoughton\t1\n

So, wherever there is a tab, we represent it as \t. 

(Strictly speaking, there is enough detail here that the \n is uneccesary - it's just there to demonstrate. Another more accurate way to represent this is to use $ at the end of the line - it means 'end of the line'. More on this later.)

Who's reading the spreadsheet?

There are two classes of 'thing' that use spreadsheets:

1. People

2. Computers

Once there is data in a spreadsheet, there are some jobs that only the person can do (e.g. type in new data), and some that only the computer can do (e.g. apply a formula automatically). The things that both can do are the ones to pay attention to when designing data structures. That's where the person's care for the computer's limitations have the most impact.

Here are some examples of things both can do. The computer can autosum a string of numbers. The person, if they really want to, can add up the numbers on a piece of paper (or in their head if they're amazing). Similarly, the computer can sort by a designated column. The person, if they really want to, can do that too - it involves a lot of cutting and pasting, but it can be done.

Here's what the computer can't do:

Arrange things so they're easier for the person. 

Here's what the person can do:

Arrange things so they're easier for the computer. 

At the most fundamental level, the single most helpful thing you can do for the computer is:

Arrange your data so the computer can sort by any column.

And by implication:

Keep your data in the smallest nodes possible.

The computer isn't as smart as the person. It's not smart at all, it has no intelligence. It can only deal with thing it recognises, and that's not actually very much. Similarly, it cannot read for meaning. As far as it's concerned, it only deals with random strings of symbols. So, to make life easier for the computer, here are some tips:

1. Colours are meaningless to the computer, and you can't sort by them. Avoid colour whenever possible, or supplement it (even an extra column with the words 'red' and 'green' in it will improve things).

2. Seperate, seperate, seperate. Never have a column with 'John Smith' in it. 'John' and 'Smith are semantic chunks - one is the chunk 'first name' and the other is the chunk 'Last name'. So seperate them.

3. Use unique ids wherever you can. If all else fails and the data gets messy, you can always sort by this column to get back to the original shape.

4. Never, ever, ever write 'same as above'. Once you sort, that little piece of information becomes a) false and b) misleading. If it's the same, then copy and paste from that cell.

5. Don't merge cells, even if it looks nicer. It's a little bit nicer for people, infinitely worse for computers. Merged cells prevent sort from working.

6. The less formatting, the better. The simpler your spreadsheet is, the better the computer will be able to deal with it. 

6: Escaping the metacharacter

So: the obvious question. The question is 'but if I write a regular expression for some text, and there's a question mark in it, and it really means 'this is the end of the sentence and the sentence is a question', what do I do?'

Easy peasy. If the symbol has a special meaning that you don't want to use, you need to escape the special meaning. To do this, you use a metacharacter. 

Every time you mean to use a symbol as a character (i.e. not a metacharacter), you use this: \

So if you had this piece of text:

What is wrong with this damn computer?

And you wanted the ? to mean (the way it usually does) 'this is the end of the sentence and the sentence is a question', you would start your regex like this: 

find: What is wrong with this damn computer\?

The \ means 'literally'. It means 'this is not a metacharacter, it's just a nice normal character. I use \ at least once in every regex I ever write.

Warning: sometimes \ reverses, and indicates that the next character is, in fact, special (I know, it's confusing) The most frequently used examples of this are \n and \t. \n means 'new line', and \t means 'tab'. More on these two in lesson seven.

 

5. Two types of symbols (but they're the same symbols)

So, in the last entry, I introduced the idea of metacharacters - symbols that mean something. There's a list of them on the right there ('know your metacharacters').

Text is always comprised of symbols. Letters are symbols. Punctuation marks are symbols. When using regexes, we break down symbols into two types: characters and metacharacters, which can be thought of as 'normal' and 'special'.

Here's the tricky bit: When we use regexes, we are invariably using characters and metacharacters in the same piece of text.

So there are a bunch of symbols that can mean either what they normally mean in English (or any other language) or a special pattern matching instruction. (btw - yes, there is an easy way to tell the difference. I'll get to that.)

Here's an example: The symbols . and ?

AS CHARACTERS

. means 'this is the end of the sentence'

? means 'this is the end of the sentence, and this sentence is a question'

AS METACHARACTERS

. means 'any character'

? means 'zero or one of the character before the ?'

Totally different. The best thing to do here is ignore your life-long understanding of what . and ? mean as characters - the relationship between the character meaning and the metacharacter meaning is completely arbitrary.

This still leaves us with the question: If they're the same, how do you tell them apart?

4. Regular expressions: introduction

'Regular expressions' is a difficult name for a useful tool. Basically, they're very small computer programs. A single regular expression (or 'regex') is a little sequence of special characters that match a pattern in a file.

What we're dealing with here is 'find' and 'replace'.

Consider this little piece of text:

I have a cat because I like cats. I can never catch my cat.

If you wanted to change that to 'dog', in Excel, or Word, you could. You find 'cat' and replace it with 'dog'.

Find: cat

Replace: dog

Output: I have a dog because I like cats. I can never catch my dog.

Excel and Word will skip the word 'cats'. These programs know that you're unlikely to want to change every sequence of c-a-t, so they assume you mean "find 'cat', but only when it's definitely one word and not part of another word, like 'catch' or 'replicate' or 'subcategory'". Fair enough. Easy, too - after all, you can just do a second find-replace for 'cats'.

Textpad isn't that generous. It assumes that you mean exactly what you say, every time, with no exceptions.

In textpad, this would happen:

Find: cat

Replace: dog

Output: I have a dog because I like dogs. I can never dogch my dog.

The obvious response to that example is: That's really stupid.

Absolutely it's stupid. But here's the thing: Finding 'cat' and replacing it with 'dog' is not a regex. (As a general rule, the less text there is in a regex, the better). Regexes are not about text, they are about patterns, and the patterns are expressed in metacharacters.

A metacharacter is a single keyboard-stroke that refers to a type of character, not the character itself.

Here are some examples:

. means 'any character'

? means 'zero or one of the character before the ?

Don't bother trying to understand the next bit. The point of this example is not to show how it's done, only that it can be done. (The red is just to show which bits are doing the work).

Find: cat\(s?\)\>

Replace: dog\1

Output: I have a dog because I like dogs. I can never catch my dog.

And there we go. All the changes have been made accurately and cleanly in one move.

3. Tab separated values

Metadata is usually stored in Tab Seperated Value (tsv) format.

Don't be worried by that - you've already been using it for years. You may never have known it, but MS Excel is in tsv format.

All tsv means is that the fields of information are seperated by tabs. If you're in an excel spreadsheet, and you press 'tab', you move across by one cell - the software knows that 'tab' means 'the next field'.

There are other ways to seperate units of information: commas, semi-colons, colons, all sorts of things. The handy thing about tabs is that, unlike commas, semi-colons, and colons, they tend not to be used in natural language. You might have a book title like 'My life: the story of my life', but you'll never have 'My life (TAB) the story of my life'. That makes tsv very handy.

('Natural language' is any language that isn't a computer language. English, Spanish, Sign language... anything that people use to understand other people. Computers are terrible with natural language, which causes big headaches for the regex user. We'll cover that in more detail later.)

Look at this excel spreadsheet:

...and this textpad document:

The textpad document looks unintelligible, but it isn't. It is exactly the same document as the excel spreadsheet. In fact, all I did was copy & paste between the two - there are no changes at all. This means two things:

1. The tsv format is easy

2. If you ever get lost in textpad, you can pop your data back into excel to see what's going on.

(Sharp eyes will have spotted that this is a terrible, terrible piece of metadata. All I've done is pull some books off my bookshelf and pop them in a list. It's not standardised, the first name and surname are in the same column, the capitals are all over the shop, and my id column is based on the order I grabbed them. There's a reason for this - we're going to fix it.)

2. Textpad is magic

First things first: computers need software.

We tend to think that complex processes require complex software. That's our general experience: Photoshop will do a better job of manipulating your pictures than the free little Paint application that comes with Windows will.

The rule, however, doesn't always hold true. To use regular expressions, you do not need a whizz-bang piece of software. In fact, the less whizzing and the fewer bangs the better. What you need is something stripped down to bare bones.

The reason is this: control. If you are using regexes, you never, ever want the computer to second guess you. This is an environment where you want hte computer to do exactly as it's told, no more, no less. Enter the text editor.

Text editors do just that: they edit text. They don't format, they don't (usually) do fonts. MS Word is not a text editor. (Anyone who has been annoyed by Word trying to predict their formatting or telling them their grammar is bad will already understand the 'control' advantage of slimmed-down software.) As a rule, programmers don't use fancy software - most of them use text editors.

There are a lot of text editors out there, all of which are good for different things (usually programming related). For metadata manipulation, however, there's only one even worth looking at: Textpad. It's cheap, it's fast, it's reliable, and it handles metadata impeccably. And as you can see, it doesn't even look scary - the layout is just like a slimmer version of Word.

www.textpad.com

Unfortunately, Textpad is only made for PCs - I don't know any viable alternative for Macs, but if you do, please let me know!

This blog is written specifically for textpad users. You'll find following it very difficult if you don't have a copy.

1. What Regular Expressions do

These tutorials are intended as a guide for people who wrangle metadata.

More specifically, they're aimed at people who wrangle tab-separated metadata and have noticed that excel has it's limits.

The name 'regular expressions' (or 'regex') isn't particularly intuitive, and at first sight, regular expressions themselves look a lot like goobledegook.

They do make sense though, and with a little practice, can be very, very powerful.