Importing large file from Excel

[FM Chick]

I have a large Excel file that I have decided to put into FileMaker for work. I believe this would be a good way to put into use what I have read about and learnt through a book I was reading (Filemaker 8: The Missing Manual).

What I want is a Filemaker layout interface where my boss (or me) can filter the list by date and by company and have a report printed which will summarize a company with a sub-total.

The Excel spreadsheet uses calculations as well, please let me know how you think the best way for me to import this file would be.

I want each company on a new page as the companies have to be sent the report as a form of invoicing.

Does anyone have any tips, ideas or suggestions before I start?

One thing to keep in mind is that one of the columns uses VLOOKUP to look up values in another worksheet, do you guys have any idea if this can be integrated into the new Filemaker database or will that be too hard?

Anyway thanks for any advice that you might give me, this will be a great challenge for this week.

[Fenton]

If the 1st row of the Excel file is names of the columns, you can just use File, Open, to open it with FileMaker. This will create a FileMaker file, with the column names as the fields. I've opened pretty large spreadsheets, 30,000 rows this way.

If the 1st row is not the column names, the a line a little further down is, you can select all the rows starting with that one, and define it as a Range in Excel, then open the range by name instead; it's one of the options FileMaker offers.

The calculations will not come in, just the value. So you'd need to recreate those. FileMaker calculations are similar to Excel, though not as many (better names though, IMHO). It probably has what you need.

VLOOKUP is, I believe, the same as Lookup. Which is a function you could use in a calculation, but we normally use it more directly, as a Lookup, which is one of the auto-enter options. It has the normal options, next lower, next higher, etc., as well as others (what to do if it's empty). You would use this via a relationship.

This brings up a fundamental difference between a FileMaker database and a spreadsheet like Excel. Each cell in a spreadsheet has a "location on the screen". FileMaker fields are not limited in that way. The ties between things are not by some fixed location, they are by logical relationships. A lookup uses one of these logical relationships, which you create on the Relationship Graph, to match and bring in the data.