Excel Data and the Instant Database

        Pathagoras can use Excel files as a 'primary' data source, in (almost) the identical way that regular Instant Database files are used. The setup and the processes are explained at this link.

Importing Data to the Instant Database screen:

•If you want to use data that currently exists in an Excel spreadsheet (perhaps one that contains all relevant information regarding a client or customer, you can easily import that data directly in an Instant Database record. First, display the Instant Database screen (typically <Alt-D>) and click the red Power Tools button. Click the Import button on the resulting screen.

Direct transfer from Excel into document. If you know that the data in the spreadsheet is correct and you don't want to review it in the Instant Database screen, or save it as an IDB record, check the small, unlabeled box just to the upper left of the Import button before you click the Import button. This tells Pathagoras to make replacements directly, without interposing the Instant Database screen. (If variables not in the spreadsheet still remain in the document, you can always scan again for them.)

After you click 'Import,' Pathagoras will offer you the following choices:

Click the third item down 'From Excel Worksheet (single record).' A second menu will appear.

•Import from a simple (two column or two row) Excel spreadsheet directly into Instant Database.

Pathagoras can easily scrape (read) a simple 'plain text' two column or two row spreadsheet. If 'two row', the variable name is in the top row and the client's data in the second. If 'two column, the variable name is in column 1 and client's data in column 2.  Following the prompts, navigate to the target spreadsheet. When scraped, the the variable-to-values information is transferred onto your Instant Database screen.

(To learn how to create that simple Excel spreadsheet that you can mail to your clients/customers, see below section titled "Exporting Data from Instant Database Screen.")

•Import from a 'fancy' spreadsheet, i.e., from 'named' cells.

If you have a 'fancy spreadsheet containing data in 'named' cells, Pathagoras can quickly find each named cell and return its name (the variable name, left column in the IDB form) and value (right column).

Naming (and converting) Notes: You cannot use spaces or other special characters (such as the '@' sign as part of an Excel cell name. If your document variables don't match your cell names because of this requirement, use underscores as 'holding characters' in Excel. During the import process, Pathagoras can convert the 'illegal' characters back to match the variable.

oConvert an '_#' pattern to a '@#'. E.g., your document variables are "Child@1_Name", "Child@2_Name", "Child@3_Name", etc., but your Excel named cells are "Child_1_Name", "Child_2_Name", "Child_3_Name". No problem. Pathagoras can perform that conversion. Select the appropriate checkbox in the Pathagoras Features | All Settings | Excel tab (Converting Underscores block).

oConvert underscores to spaces. E.g., your document variables contain spaces ("Spouse First Name") but your Excel cell name has underscores to comply with naming rules ("Spouse_First_Name"). Pathagoras can perform that conversion. Select the appropriate checkbox in Pathagoras Features | All Settings | Excel tab (Convertimg Underscores block).

oIn case you were wondering, you can use both conversion tools and Pathagoras is 'order aware.' So, Pathagoras can properly convert "Child_1_Name" to "Child@1 Name"

(new 2018.1) If the data you want to import is a named range (i.e., more than one cell in height and/or width), Pathagoras can still import the data into your document. If detected, the range name will appear as a bracketed variable with the addition of "(RNG)" to the right of the name e.g., [Properties(RNG)]" and the variable value (right column) will start with "RNG:". The in-document variable should simply be [Properties].

Create a simple intake form. You can create a simple intake form that you can mail to clients, customers or patients for completion. When returned, you can scrape them for the data provided with the above tools. Here are the steps to create the form. Make sure that the Instant Database screen has variables in the left column (by displaying an existing record, an existing mask or scanning an underlying document).

   The following screen is produced:

Select the 'To single column or row Excel spreadsheet'. Save the resulting spreadsheet in an easy to find location. Email the spreadsheet as an attachemnt to the client, instruct the client to fill out the column to the immediate right (or row beneath) and sent it back. Then you can quickly import the client provided data. Saves lots of typing..