A READER ASKS: excel spreadsheet importing

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Hope you can help - Thanks for all your blogs - A great help but now I'm stuck !! I've been running a filemaker setup since V3 !! And still find things I want it to do but can't find a simple answer (running V8 Server now, Maxed at 10 logins - Mac OSX 10.4)

I work for a printing company and are regularly sent xls spreadsheets to print sequentially under strict security. We will be told that we need to print for example lines 10001 - 52,2320 from a 100000 line spreadsheet.

I currently keep these stored as the native xls files and extract from them as required, However I want a link to a master database containing pages of all the archived spreadsheets held as a pasted copy of the spreadsheet.

From this page I can store the original spreadsheet as a simple text box. - HOWEVER - Once the lines are all pasted in I cant find a way for it to show me the Line No's as is shown on a excell spreadsheet. When I go back in next time to extract the next set I need to know where to start copying from !

I can set up a layout as a table layout and set all the standard links from the main page and security required but this is useless unless I know which line is which !!

It looks like all your data is showing up in one field, with multiple lines of data and each line separated by a carriage return. If you want to slice the data into a two table file with a simple relationships between them in a FileMaker database, I think you can do what you want. So our end goal is a master record (the one imported into) and a portal with rows that used to be the individual lines.

Basically, you will want to get cuddly with the Text family of functions. I’m going to chat about the LeftValue, RightValue, MiddleValue and ValueCount functions. These come into play because what you showed me was a picture of a one field with a bunch of values separated by a carriage return. In your case, you could say that each line is a value. So with the ValueCount function, I can know the number of lines you have and I can assign a row number to each line.

The next thing is called Parsing. Parsing, as a FileMaker related term, is linked to the process of breaking up a text string into smaller pieces. Say you want to parse the nouns and verbs from a sentence such as "The quick brown fox jumped over the lazy dog." The parse result would be (NOUN: fox - dog) and (VERB - jumped). So parsing is basically getting what you want out of a text string and ignoring the rest. Parsing is a common technique in intermediate and advanced FileMaker design. It is quite common to use text functions for this process.

So what we want to do is mimic a slow, dull, and manual process, only we wan to perform that process with a script. That process will be to cut the first value (line), put it into a new record in another table, add other data to that same record such as the page number and the row number, return back to the field that contains your carriage return separate values (called an array by the way) and repeat all the above steps in a loop until the last value (line) is gone.

Our end result is a fully populated database with all your lines now individually related records. I’ve put a bunch of links below that should help you with this process. If you have trouble, let me know. I can work out an example file for you. However, if you can get it going on your own, it would be great to hear how it went for you.

Here are some links to other posts that might be of interest in regards to this topic...
The Left, Leftvalues And Leftwords Functions
Text Functions
A READER ASKS: Does This Variable Exist?
Parse, Array And Concatenation
The ValueCount Function
The Length Function
The Middle, Middlevalues And Middlewords Functions
The PatternCount Function
The Position Function
The Right, RightValues And RightWords Functions
The SerialIncrement Function
The ValueCount Function
The WordCount Function
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
Please feel free to visit the FileMaker Security Explored Blog at http://fmpsecurity.blogspot.com/.