Import XLSX to Child Table

[John Kostenbader]

It has been covered here but I really can't find a sufficient answer. When I do an automated import of an xlsx file that has no blank lines at the top into a portal that is part of a child relationship I keep getting blank lines. I've confirmed that there are no blank rows in the excel spreadsheet. For the particular import I'm talking about i get one blank line at the top (portal is not sorted in any way). I also get two blank lines at the bottom. I can almost understand the blank lines at the bottom (how does FM know when to stop?) but I cannot wrap my head around why I have one at the beginning

[comment]

Could we see an example of the source file?

[John Kostenbader]

Sure ...this is one of the files (they all contain blank lines)

91-92 ground truck checklist.xlsx

To make things even clearer....here is the results in table view. the top image is, of course, the first blank record imported and the second image is the bottom blank records

[comment]

It's true that there is no blank row at the top of the worksheet - but the first row has data only in the first column. In fact, any row that has data in the first column has no data in the other columns - so if you're importing only the second and the third column, you will get a blank record for every such row (there are 28 of these in the provided example).

In addition, the worksheet has a named range "_xlnm.Print_Area_1" defined as B1:C241. I suspect this causes Filemaker to import the top 241 rows, regardless of whether they contain data or not. This would account for the last two blank records.

Two ways to solve this:

1. Have your importing script find the empty records and delete them;
2. Validate one of the target fields as Not empty, Validate always. This will cause rows that do not meet the validation to be skipped during import.

 

Edited December 30, 2015 by comment

[John Kostenbader]

Excellent observations. Can you briefly expand on option #2. I'm not familiar with Validation as you are describing it

 

 

[comment]