Import excel file starting in row 5

[Nestor]

Hi everybody, I created a script to import an excel file, but how can i make it so that it starts importing in Row 5 rather than from row 1.

Thanks

[Vaughan]

You cannot partially import data files into FMP. It's all or nothing.

However, you could import into a temporary table, delete (or omit) the unnecessary rows, then import the temp records into the main table.

[David Jondreau]

No need for a temp table is there?

[LaRetta]

It shouldn't even be necessary to use a temp table here ... import leaves you with your found set of newly imported records. If you are sure you ALWAYS want to delete the first five records, your script would look like (pseudo-script below, untested, back up first)

Import

If [ Get ( FoundCount ) > 5 ]

Go to Record/request/page [ by calculation, 6 ]

Omit multiple [ Get ( FoundCount ) - 5 ]

... here you will be left with the 5 offending records

... test to be sure

If [ Get ( FoundCount ) = 5 ]

Delete All Records

End If

End If

... there are probably more clever ways. For instance, if you need to preserve your imported found set after you are done, you can, after the import, open a new window and work there. Then close the window to end up with your imported found set.

Update: Added last End If ... ooops. But then - that's why I called it pseudo-script. ;o)

Edited July 18, 2008 by Guest

[LaRetta]

Hi there, DJ! I agree, no need! I was obviously busy typing and didn't see your post.

[comment]

You cannot partially import data files into FMP. It's all or nothing.

I wouldn't say so, at least not without some reservations. You CAN import only part of the data by enforcing validation on some field/s.

With Excel files, you have the additional option of naming the source range and importing by this name.

[LaRetta]

...naming the source range and importing by this name

Wow. I don't see this option! Import File, specify xls worksheet, but named ranges is grey'd out. I've selected 3 worksheets now which have many rows and columns of data. What am I doing wrong Here, Michael?

[comment]

named ranges is grey'd out.

That will be the case when the spreadsheet does not have any named ranges.

[David Jondreau]

LaRetta, great minds think alike!

Hearing comment's mentions about records failing to import because of validation violations has gotten me looking for ways to use it...

You could create a field, importCounter in the "import into" target table. Set it to number, auto enter, serial by 1. Add a validation calc (Always) to it=

import into::import counter < 5 * ≥ 5 //for example.

Then import.

If you wanted to script it, instead of 5 set the validation calc to a $$ variable and set that variable by script before your import script step.

Bit of overkill in this case, but I feel like I learned another way of looking at Filemaker by doing this...

import filter: validation import failure 

#START SCRIPT 

Allow User Abort [ On ] 

# 

Set Variable [ $$importCounter; Value:5 ] 

# 

# 

Go to Layout [ “import into” (import into) ] 

//  Show All Records 

//  Delete All Records 

[ No dialog ] 

Set Next Serial Value [ import into::import counter; 1 ] 

# 

# 

Import Records [ Source: “file:mysandbox.fp7”; Target: “import into”; Method: Add; Character Set: “Mac Roman”; Field 

Mapping: Source field 1 import to import into::imported text ] 

[ No dialog ] 

# 

# 

Set Variable [ $$importCounter; Value:"" ] 

# 

#END SCRIPT 

**there is more (and different) detail in the printed version of the script than I can see on the screen.

Edited July 18, 2008 by Guest
typo noted and corrected, thanks!

[Vaughan]

Wouldn't a temp table be easier?

[comment]

Add a validation calc (Always) to it=

import into::import counter < 5 //for example.

I believe it should be "import counter ≥ 5" in this case, but the point is clear.

Interestingly enough, this doesn't work if you validate by 'In range' instead.

[David Jondreau]

Easier than the solution in my last post? Absolutely. Total overkill. I'm just playing around with import validation filtering.

So...watch out! I've got a hammer and all the questions are looking like nails!

I just had success setting the Validation calc to Evaluate($$variable). That $$variable can be set in the script to a complex expression, which could be handy.

Edited July 18, 2008 by Guest

[Nestor]

GREAT. I'll try those, and see what happens.

THANKS VERY MUCH...