Open vs Import

[Jack Rodgers]

Couldn't find a suitable category for this hint so I chose Developer.

 

I am working on exporting a super large file to Excel spreadsheets, pre-designed, for import into a different database.

 

I ran into the old pain of the fields being named f1, f2, f3 when I did an import of the spreadsheet in order to begin working on the export.

 

After renaming 100 fields and looking at another dozen spreadsheets I remembered that Filemaker will use the field names from the first row...but I couldn't remember where that option was.

 

Now, I did the wide developer thing and instead of hoping on to a list and crying "Help" I searched through Filemaker's help files and there is was.

 

The trick to naming imported file structures to the first field is to NOT import the Excel file but to OPEN it. Then the option to name using the first field pops up.

 

Hopefully someone else just imported and renamed a few thousand fields and when they read this they will....ARGGGGHHH!

 

Yeah

[AHunter3]

You can do it in a straightforward Import from Excel dialog, too. It's at the lower left of the screen where you match the Excel fields with the FmPro fields, when you've opted to create a new table in FmPro.

[Maarten Witberg]

thanks for the hint, but.... "hopefully" ?????

[LingoJango]

kjoe, it's called Schadenfreude.

 

Allan, how's your German coming along? smiley-wink

[Jack Rodgers]

You can do it in a straightforward Import from Excel dialog, too. It's at the lower left of the screen where you match the Excel fields with the FmPro fields, when you've opted to create a new table in FmPro.

 

Nope.

 

If you import an Excel file, the fields are named f1, f2 and so on. As I described and it takes a long time to manually rename the fields.

 

If you OPEN the Excel file, there is the option to name the fields after the contents of the first row: Name, Address, City, State and so on.

 

If you want the file to become a table in another file, now you can import this new file and have the field names.

[David Head]

Jack. You are wrong. Alan is right.

 

If you want to import an Excel file into an existing database and create a new table with field names as per the first row of the spreadsheet it can be done.

 

1. File > Import Records > File...

2. Select the Excel file and click Open

3. In the Import field Mapping dialog, choose New Table as the Target (top right)

4. Check the checkbox at the bottom left "Don't import first record (contains field names)"

5. Click Import

 

Now you have a new table in your existing file with field names specified rather than f1, f2, etc.

 

The disadvantage (or perhaps sometimes the advantage) of opening an Excel file in FileMaker, is that you end up with a separate file with a single table.

[Jack Rodgers]

Alan was only partly right since his instructions were less than clear and did not mention the checkbox...

 

I was 100% right but overlooked what you've pointed out since the description is really bad and leads one to think that is a method only for eliminating the import of the first record. It should say:

 

Use the first record to name the fields!

 

Oh, well, the jobs over now....ARGGGGHHHH!

 

Yeah

[David Head]

I agree that Alan's description was a little brief and unclear. Alan, please be more precise with your descriptions of processes. But it still deserved investigation. And that doesn't make him wrong. And not understanding what a checkbox does, doesn't make you 100% right. True that it does not adequately describe all that it does. But that is not the only instance of that!

 

As I say to my students - "if something is taking you a long time, you are probably doing it the wrong way". You could have asked for help after renaming about 20 fields.

[Jack Rodgers]

I did ask for help. I looked it up in the Filemaker Help File.

 

"Converting a data file to a new FileMaker Pro file"

This help topic uses the Open method I described having read this topic. This is the only instance I could find in the Help file that mentioned Filemaker renaming fields.

 

"About creating a new table for imported data"

 

Does not mention using the checkbox for naming the fields from the first row. In fact, it says you can manually modify the fields afterwards.

 

I did find a reference to that checkbox:

 

4. If the first record of the data you're importing contains field names (column headings) instead of data, select Don't import first record (contains field names).

Selecting Don't import first record (contains field names) skips the first record during import.

 

Note that I inferred that not importing the field names meant I would have to open the Excel file to find out what the names are in order to rename f1, f2, etc.

 

Notice that here the information is incomplete. It says it skips the first record but it does NOT say that it will rename the fields. The only reference to naming the fields is when you use Open. My original post conformed to the information in the Filemaker help file.

 

Note that it is quite logical to NOT check that box and to import the field names if you expect to have to rename the fields having imported an Excel file in the past. Thus you can see the names on the Filemaker form layout and rename the fields with ease.

 

Had the checkbox been named a bit better such as "Use the First Row as Field Names" and had the Filemaker Help file added one sentence about renaming the fields, this thread would not exist.

 

Which is why I began the thread. And as a thread should, it eventually produced the truth of the matter in the midst of confusion.

 

Yeah

[AHunter3]

I agree that Alan's description was a little brief and unclear. Alan, please be more precise with your descriptions of processes.

 

I could've sworn I specifically mentioned the checkbox in the lower left. Sorry 'bout that!

[David Head]

I did ask for help. I looked it up in the Filemaker Help File.

What you did is perfectly reasonable. So what now? Have you notified FileMaker Inc., that their documentation is lacking in detail? If/when you do, tell them how much time you wasted reading the Help files, finding no solution, doing it manually and then after all that finding a solution. That will carry more weight. It is a bug report but in this case, the feature exists - it is just not well documented (or not documented at all). smiley-frown

[Jack Rodgers]

'Vaguely' to quote DH...

 

I wonder if Filemaker changed the wording of the checkbox in the newer versions from a clearer message in older versions.

 

If anyone wants to take a few minutes to reference how this was named/handled in 6, 5, 4 and so on I am sure you will rewarded richly for this.

[David Head]

Except that in earlier versions there was no checkbox and there was no ability to import into an existing file while creating a new table. So it is not possible to compare this poorly worded option in version 7+ to what didn't exist in version 6-.

 

When opening an Excel file directly in version 6 or version 9, the resulting dialog boxes are very similar.

[AHunter3]

As someone recently pointed out, FileMaker's documentation is thin in other places as well, the existence and use of multikeys being one glaring example.

[Jack Rodgers]

Except that in earlier versions there was no checkbox and there was no ability to import into an existing file while creating a new table. So it is not possible to compare this poorly worded option in version 7+ to what didn't exist in version 6-.

 

Technicalities. There are always technicalities... smiley-tongue-out