Creating an FM db from an Excel binder

Today I was sent an Excel spreadsheet that had been the primary tool for a small business. We're converting it to an FMP application. The owner explained that the spreadsheet was always very slow to work with because it contained thousands of rows. ( A little bit under 4000 rows in one sheet ) and many sheets ( twenty sheets, most with only a few rows of data ). They were right. It took quite a few minutes to open and was really sluggish.

My plan was to convert it to FMP so I opened it from FileMaker and starting importing the sheets. This took seconds for most sheets, until I came to the main sheet, the inventory. It took FMP about thirty minutes to open it. As it only contained a few thousand records I was interested to see what was going on. I opened the database manager dialog and flipped to the fields tab. A spinner displayed and after a minute or so I began writing this post. It has finally displayed the fields and there are 16383 of them!

The rainbow wheel opens with every movement I make in this table. I've just created a new table using the first 52 fields, which are the only ones that contain data, then closed the database manager. Now I'm waiting for the rainbow wheel to stop so that I can import data into them and drop the other table. It's been a few minutes.

Most likely caused by the Excel users inadvertently expanding the spreadsheet area to Excel maximum capacity, either through a formula, a drag, applying a format with the row handle selected or the whole sheet selected, erasing empty cells beyond where necessary, pasting or deleting a full sheet selection into A1 selection, selecting the sheet by the top left corner selector and then applying a table format or setting the print area....

To resolve this, before creating the FM db from the excel binder, select in each sheet all cells containing data and then click “Set print area” then save the binder.

I didn’t check it in 19 but previous versions used to ask whether you wanted to import from the whole binder selected sheet/area. Of course if the spreadsheet has been expanded, the problem needs to be fixed in Excel first.

2 Likes

Why not consider a free API like Apache POI? It's fast and, in your REST service, can not only work with FileMaker but other apps as well. There are TONS of online code examples for Excel.

Normally caused by clicking on a row or column header and formatting the entire row/column.

1 Like

Why not consider a free API like Apache POI?

Because FMP opens an Excel spreadsheet and converts it to a FMP database in one action. Its simple to do and extremely convenient, especially as in the end result I want it to be an FMP database.

Normally caused by clicking on a row or column header and formatting the entire row/column

Really? That's something I do from time to time and I don't remember seeing that occur. This is the first time I've seen this happen on conversion.

Malcolm

I don’t believe the latest versions do, but certainly that is the way it used to work. I don’t know when they fixed this.

All the best

Andy

I thought the OP was having problems importing multiple sheets from a workbook using FMP only. Apache is a way around that issue.

we got a customer with about 6'000 fields in one table. FileMaker 11 (was started with FM11) and 15 did not have any problems with opening 'define detabase/fields', FM16 had about 30 seconds to open (really no delay in 11 and 15)

I sent an issue report, but no help (aside that so many fields are a design mistake). No of the newer versions are much better - there must be something changed, butat least I do not have any information

So, if there are many more fields, it sounds logical...

That said, the customer is on FM18 in the meanwhile and during work, there are no issues..

Just to clarify: The micro-service approach would work with any version of FileMaker 12 or later. And, not just from FileMaker, but with other apps, too. :slight_smile:

I had a few other people who were unsurprised by this and told me the same as you had. It shows how well I've managed to insulate myself from Excel doesn't it?

6000 is a big number. Do you have plans to modify the structure or is it fixed in stone?

unfortunately, can't be changed quickly. The developer tried to build overviwes like in Excel, a field for every month for diverse factors, a couple of years ahead and back..

I find that using an API like Apache POI, I've never needed to worry about large numbers of anything. It just works. And fast. And free. And with all my apps (not "just" FMP).

I'd be happy to share more info on this technique if it sounds like something you'd consider implementing.

Happy Computing :slight_smile:

That sounds "helpful".