Hendon Publishing - Article Archive Details

Print Article Rate Comment Reprint Information

Converting Lists

Written by Tim Dees

Law enforcement agencies, like every other business organization, compile a great many lists. Phone numbers, addresses, statutes, bail schedules, etc., make for a lot of data. Having this data in computer-readable form makes it more accessible and easier to distribute, but it’s not alsways in the format that we would prefer. This month, we’re going to use a couple of office applications to convert lists into forms that can be modified to suit individual purposes.

The LAPD has an official Web site that includes contact lists for its many offices and divisions. However, these are displayed on Web pages that are segregated by operational unit, such as training, traffic coordination and so on. Suppose I wanted to create one big directory of LAPD phone numbers without rekeying all of the information from these lists?

The LAPD phone lists are formatted in Web tables, which make our job simpler. If the information was in some other form, we could still handle it, providing that each data element (office name, phone number, etc.) and each record was formatted consistently. In this case, we start by opening each Web page that contains a phone number table, selecting that table, and right-clicking to get the Copy option. It’s easier if we restrict the selection to just the data we want, although it won’t be a catastrophe if some stray code gets in there.

Now open a blank Excel workbook, click on the first cell (A1) to select it, and click on the Paste icon. The data we copied pastes itself into Excel, already segregated into columns for office name and phone number. Because the list had some headings that separated the groups of numbers, some of the data is inconsistently formatted, but it’s easy enough to delete those rows. Repeat the operation for each one of the contact list Web pages, pasting each copied group into the cell after the final entry on the previous page. Delete the stray label rows, and adjust the column widths so that each entry just takes up a single line (or not, depending on your preference). Now, select the entire list and use the Format options to get the font, colors, column widths and row heights, etc., into the format you want. Sort it on the office name, the phone number or any other element in the list. You have a complete list of data you can distribute or keep on file for future use.

Most Web lists are in table format, but some folks still use tabs to separate data elements and a hard return at the end of a line. This is called tab delimited format. A similar and widely used format is comma separated values (frequently abbreviated “csv”), where each data element is enclosed in quotes, and there is a comma between each value, e.g. “Lastname1”, “Firstname1”, “Title1,” “PhoneNumber1” “Lastname2”, “Firstname2”, “Title2,” “PhoneNumber2”

Many data-management programs and address book managers export data in this format so that it can be used in other programs. The files that result from these exports usually have the file extension of *.txt, although they may use *.csv, *.dat or *.tab. One usually needs to open the file in a text editor other than Word (Notepad will do if the file is smaller than 64K— otherwise, use WordPad, which is included in every Windows installation) to see which format is used. Another less popular format is fixed width, where each field takes up exactly the same number of spaces, regardless of how many characters are in the data field. Excel recognizes most of these and will import the file with a little work. If you have a choice of how to save and name the file that contains this semi-formatted data, choose the file extension of *.txt and if you are using a word processor (including WordPad), make sure that the Save as type option is set to *.txt.

To get these files into Excel, open a blank workbook or worksheet, and click on Data||Import External Data in the Menu Bar. The program will open a dialog box and ask you for the name and location of the file that contains your data. Navigate to the folder that contains it, locate it (you may have to change the Files of type option in the lowermost drop-down box to show All Files), and double-click on it to open it. Excel will now start an Import Data Wizard and ask you to tell it whether the file is delimited (data elements separated by tabs, commas or some other distinctive character) or fixed width (each data element takes up the same number of spaces). A portion of your data file will be displayed in the lowermost window of the wizard dialog box. Press the Next button when you have made the choices. In the second Text Import Wizard dialog box, you get to designate which character is used to separate your data elements. Clicking on the various options in the dialog box will cause the data sample displayed to reformat, so you’ll get an idea if you are using the right one.

One potential hang-up here is that if a comma is used to separate data elements, and any of the data fields themselves contain commas (e.g. “Smith, John”), the columns may break where you don’t want them to. One workaround is to import the data file first into a word processor, use the Search and Replace function to locate every instance of a quote mark followed by a comma followed by a quote mark (e.g. “,”), and replace it with a character that doesn’t appear anywhere else in the list, such as an asterisk. Then, use Search and Replace again to find just the commas (the only ones left should be in the data elements themselves), and change them to another unique character (maybe a carat? “^”). Finally, do one more Search and Replace and convert the asterisks or whatever you used back to the quote-comma-quote string, and your data should be manageable. When the entire import is done, you can convert the carats or whatever back to commas— Excel has the same Search and Replace function.

The last step allows you to format each column to reflect the type of data it contains— text, date, numbers, etc. This is a less critical step, as you can always reformat the columns after the import. Clicking on finish brings the text into the workbook, where you can sort, reformat, export or do whatever you want with it.

For what started as an accountant’s tool, Excel is an extremely versatile piece of software that most users avoid because they find it intimidating. There are 329 functions available in Excel, but you need not use them all. Use the ones that you understand, and consult the online help feature when you want to stretch your wings a bit. In the end, don’t be afraid to use the tools at your disposal.

Tim Dees is a former officer who writes and consults about applications of technology in law enforcement. He can be reached at (509) 585-6704 or by e-mail at tim@timdees.com.

Published in Law and Order, Sep 2003

Rating : Not Yet Rated


Comments

Comment on This Article

No Comments


Related Companies

Hendon Publishing
 

Related Products

Converting ListsExcel (computer software)Spreadsheets (computer software)
 

Events and Tradeshows: LAOPFMTRPSIT
Latest News: LAOPFMTRPSIT
 
Close ...