If you're faced with importing a file with poorly structured data, you may have to separate parts of a single field into smaller parts. For example, if your text file has a single field for first and last names or for city, state, and Zip code but your database has them as separate elements, you need to separate the data into its constituent parts. The process of doing this is called "parsing," and it can be complex because each set of text combinations can present its own problems. No one script can be used for all parsing situations, but the concept we show here is adaptable to a wide range of parsing needs. These steps show how to parse a Full Name field such as "Name = John J. Jones" into first, middle, and last names, like this: "First Name = John MI = J. Last Name = Jones."
To parse imported data
1 .In the original database, choose File > Define > Database (Control+Shift+D/Command+Shift+D). Select the Fields tab.2 .You need to create a field into which you will temporarily import the name data from the import file, so you can then parse it into separate fields. Type a name for the field (we use Import Name), with Text (Control+T/Command+T) as its Type. Click Create, then OK.3 .Open the database clone. Follow the steps in "To import text data files" in Chapter 12, matching the names (or the name field) in the import file to the Import Name field (Figure 14.31).
Figure 14.31. Match the Import Name field with the full-name data.
4 .Choose Scripts > ScriptMaker (Control+Shift+S/Command+Shift+S).5 .When the Define Scripts dialog box appears, click New. When the Edit Script dialog box appears, type a name for the script (we use Parse Name).6 .In the step list on the left, double-click Go to Record/Request/Page. Leave the option set to First (Figure 14.32).
Figure 14.32. The Go to Record/Request/Page step starts the script in the first record in the database.
7 .In the step list on the left, double-click Loop.8 .In the step list on the left, double-click If to insert it between Loop and End Loop (Figure 14.33). Under Script Step Options, click Specify to bring up the Specify Calculation dialog box.
Figure 14.33. If and End If are inserted between Loop and End Loop.
9 .In the function list on the right, scroll down to WordCount and double-click it.The WordCount function counts the number of words in the field you choose.10 .In the field list on the left, double-click the Import Name field (Figure 14.34).
Figure 14.34. The WordCount function will count words in Import Name.
11 .Click to the right of the parentheses and double-click the equals operator. Enter 3 (Figure 14.35). Click OK.
Figure 14.35. The script will act if the Import Name field has three words in it.
This calculation tells the script to look at the Import Name field to see if it has three words in it.12 .In the step list, scroll down to the Fields section and double-click Set Field. Under Script Step Options, click the "Specify target field" check box. When the Specify Field dialog box appears, double- click MI.13 .Under Script Step Options, click the Calculated result: Specify button to bring up the Specify Calculation dialog box.14 .In the function list on the right, scroll down to MiddleWords and double-click.15 .In the formula box, highlight "text" inside the parentheses. In the field list, double-click Import Name.16 .In the formula box, highlight "startingWord" and type 2. Highlight "numberOfWords" and type 1 (Figure 14.36). Click OK.
Figure 14.36. The MiddleWords function extracts the second word in the Import Name field.
The script now specifies: If there are three words in Import Name, find word number 2 and place it into the MI field.17 .In the script-assembly list, click the End If step. In the step list, double-click Set Field (Figure 14.37).
Figure 14.37. Highlight End If in the script-assembly list to insert the Set Field step below it.
18 .Under Script Step Options, click the "Specify target field" check box. When the Specify Field dialog box appears, double-click FirstName.19 .Under Script Step Options, click the Calculated result: Specify button to bring up the Specify Calculation dialog box.20 .In the function list on the right, scroll down to LeftWords and double-click.The LeftWords function looks at the field you choose and counts from the left to find the number of words you specify.21 .In the formula box, highlight "text" inside the parentheses. In the field list, double-click Import Name.22 .In the formula box, highlight "numberOfWords" and type 1 (Figure 14.38). Click OK.
Figure 14.38. The LeftWords function will return the first word in the Import Name field.
23 .In the script-assembly list, click the Duplicate button to duplicate the last Set Field script step (Figure 14.39). Double-click the new Set Field step.
Figure 14.39. It's easier to duplicate and edit a script step with a calculation.
24 .When the Specify Field dialog box appears, double-click LastName.25 .Under Script Step Options, click the Calculated result: Specify button to bring up the Specify Calculation dialog box.26 .In the formula box, highlight "Left" in LeftWords. Type Right to change the function to RightWords (Figure 14.40). Click OK.
Figure 14.40. Change LeftWords to RightWords.
RightWords is exactly like LeftWords, except that it counts from the right, not the left.27 .In the step list on the left, scroll to the Navigation section and double-click Go to Record/Request/Page. Under Script Step Options, choose Next from the Specify drop-down menu (Figure 14.41). Click the "Exit after last" check box.
Figure 14.41. The script will go to the next record in the file until it's changed them all.
This step tells the script to go to the next record it finds, then stop after the last one.28 .Click OK twice to finish. When you run this script, it marches through the clone database putting the first word of Import Name into the FirstName field, the last word into the LastName Field, and, if there are three words in Import Name, the middle word into the MI field. TipsIf any of the records have more than three words (like James J. Jones Jr.), some data won't parse correctly. To double-check your work before you import the clone data into the main database, create a column layout with the fields Import Name, FirstName, MI, and LastName. You'll quickly see which names weren't parsed correctly and can edit the records (Figure 14.42).
Figure 14.42. A columnar layout of the name fields makes it easy to correct any problems that the Parse Name script couldn't handle.
You can adapt the script-parsing strategy for times when the data structure of the import file and main database are different. For example, many handheld devices have separate phone fields for home, work, fax, and so on, but your database may have a single phone field with several repetitions. Create several Import Phone fields (one for each phone repetition). Keep track of which number imports into which field. Then create a script loop with a Set Field (Phone) to Import Phone 1 (the home number), Set Field (Phone/repetition 2) to Import Phone 2 (the work number), and so on (Figure 14.43).
Figure 14.43. The number following the name of a field indicates which repetition of the field each phone number should be placed in.
لطفا منتظر باشید ...
TipsIf any of the records have more than three words (like James J. Jones Jr.), some data won't parse correctly. To double-check your work before you import the clone data into the main database, create a column layout with the fields Import Name, FirstName, MI, and LastName. You'll quickly see which names weren't parsed correctly and can edit the records (Figure 14.42).
