Excel Import creates blank records

[brian rich]

I've a frequent requirement to import an Excel spreadsheet containing name and address information into an FMP9 database. The import works entirely successfully, but usually creates one or more blank records with no data in them which were not apparently in the original data.

After some head-scratching, I discovered that Filemaker seems to use the 'last cell' information on the worksheet to determine how many rows are actually in the dataset. Depending on the way the user has set up the file (e.g. used the original template, cleared a previous file and reused that, entered the data by hand or from cutting and pasting) depends on where the 'last cell' is on the spreadsheet. Getting the 'last cell' to be the last column and last row in your dataset can be quite fun.

Is there any way of getting Filemaker to ignore the empty rows, or else to limit the number of records imported in the import dialog? Otherwise it looks like I will have to run a post-import script to remove the blank records.

[bcooney]

I'm hoping for a meaningful answer as well, as I've encountered this many times. I usually copy/paste the data range into a new worksheet before importing.

[sbg2]

You can make it skip records by Validation. Example attached.

MyFile.zip

[brian rich]

Really neat solution!

I'll put that into the database now.

Thanks

Brian

[Emma in England]

5 years on... and thanks from me! For years I've been writing import scripts with extra steps to find and delete the empty rows. Do I feel stupid.

 

Funny how you ignore parts of Filemaker that you don't realise you need!

 

THANK YOU!