Excel Spreadsheets as Datasource

   An Excel spreadsheet can be a data source for Instant Database data. Indeed, Excel can also be a primary data source. If you wish, you can 'ignore' the Instant Database altogether (although we are not recommending it).

  Location of Files: The 'default' location for Excel files as an IDB datasource is a folder called 'Excel' directly beneath IDB records folder. (To determine the current IDB Files Folder, click link.)

However, you can place your Excel files folder in any convenient location of your choosing. To set that location, follow these steps:

navigate to the Excel tab in the All Settings window (Pathagoras Features | Utilities & Settings | All Settings). Here is the screen you will see:

Click to enlarge.


    When the Instant Database Screen is displayed, the 'regular' records are gathered and displayed in the Existing Records dropdown. But click the Show .xlsx button and the section is transformed . . . .

Click to enlarge.

. . .  the Existing records box turns green (Excel's 'brand' color) and

Click to enlarge.

. . .  only the records in the Excel folder selected in step 1 are show. Select it and Pathagoras accomplishes two more steps. (1) Pathagoras determines whether the record is a simple 2 column record or a more elaborate spreadsheet with named fields. If the later, Pathagoras presents the names of the Named Fields in the left column and the values that reside in those named fields in the right column.

    Any new variables in the document that did not exist in the .xlsx file will be appended to the bottom of the new record (just like with 'regular' records).


There is no 'write back' capability to the source Excel file. Any permanent corrections to your data must be made via Excel (You can, of course, make a change to the Excel data on the face of the Instant Database screen for the current display, and you save the data to a new IDB record. But the next time you recall the Excel record, it will contain the values currently in the spreadsheet.)


If the style of the spreadsheet you are reading is simple (that is, two columns, with the variable name in column A and the variable value in Column B, or two rows, row 1 being variables and row 2 their corresponding values), just about an value can be used in either column. (There may be exceptions, but none that we know about at this writing.) You don't even need the brackets indicating that column A (or row one) is a [variable]. Pathagoras will add brackets automatically.

However, if your spreadsheet contains named cells, a few special rules apply. Named cells allow you greater flexibility in the layout of the spreadsheet, but Microsoft imposes some rules that Pathagoras -- and you - must observe.

Named Cells issues:

If a spreadsheet has even one named cell, Pathagoras will see it as a 'named cell spreadsheet.' That means if you intended a 'simple' (two column) structure of data and have 100 separate rows of data, but accidentally have created, or left in, a single named cell, that single cell will trump the 100 rows of data. So, if you are getting strange results, check the spreadsheet for named cells and delete them. (In later versions of Pathagoras, you will be able to tell Pathagoras to ignore named cells.)

The 'name' of a named cell in Excel can contain only letter (a to z) and numbers, and an underscore. No spaces or special characters. If your Named cells contain the '!' mark signifying a group name or the '@' sign used by Pathagoras for incrementing variables (e.g., [Child@1 Name]; [Child@1 Name DOB], you will discover that Excel (not Pathagoras) replaces those symbols with an underscore. (Spaces will be replaced with underscores as well).

Ancillary to the above bullet, you should not try to use mimic in Excel Pathagoras' 'fancy' Instant Database tools (e.g., multiple choice variables and !groups!) in your spreadsheets. They won't work there, and the conversion back to Word may not be pretty. Use Excel tools to perform Excel calculations, but feed the results back to Word/Pathagoras using simple variable names.

EXCEPTION: As part of the Excel to IDB conversion process, Pathagoras will look for a pattern of 'text underscore #" (e.g., "Child_1_Name"). If detected, Pathagoras will replace the underscore preceding the number with a '@' sign. So while the name of the Excel cell might have to be [Child_1_Name], Pathagoras will convert it to [Child@1 Name]. So don't hesitate to assign your incrementing variables to named Excel cells.

Pathagoras will, by default, alphabetize the results when it reads a spreadsheet of named cells.


Here is what a simple spreadsheet might look like:


 Click to enlarge.

Click to enlarge.

The above spreadsheet (with additional instructions on how to create selectable lists) is available at this link.