Exporting fields' headers

[Auch info]

Hello,

We have a table with more than 15000 rows (museum inventory application). We used to export large amount of rows (some times like 900) to modify several fields in every rows then import it back (with version 8.5 of fmp). Now that we migrated on FMS14, it appears that it is not possible to export the fields' names in the tab file as first line. Is there a way to get the headers in the exported file ? if not please consider adding the possibility in next release.

Thank you

Thomas.

[bcooney]

http://www.filemaker.com/help/14/fmp/en/html/import_export.18.21.html#1046089

merge file format seems to meet your requirements.

gotta wonder why you're exporting at all.

Edited September 3, 2015 by bcooney

[Lee Smith]

We have a table with more than 15000 rows (museum inventory application).

Please tell me that you mean 15,000 Records and not Rows?

[Auch info]

Thank you for your answer. This is effectively 15,000 records for the moment. Should go to 23,000 when the base will be complete. Each record has around a hundred fields. This is a museum database where operators will extract a whole collection for inventory to add the same data in some fields using excel, then import it back to update the database. They used to proceed like that because they have big collections, avoiding a long update process.

Excel does not open the .mer file correctly because the data can have ';' inside. and " is replaced with "".

Thomas.

[comment]

Now that we migrated on FMS14, it appears that it is not possible to export the fields' names in the tab file as first line.

I don't know of any changes in the supported export formats. Field names were never exported as part of a tab-delimited file.

If you want a custom export format, you could built one using an XSLT stylesheet and exporting as XML - however this would not work in WebDirect.

[Auch info]

With FMP 8.5, they were exporting to Excel format. But WebDirect does not export to Excel.

[comment]

With FMP 8.5, they were exporting to Excel format. But WebDirect does not export to Excel.

Right. The problem is not upgrading to v.14, but downgrading to WebDirect.

If you cannot find any other way to solve this (e.g. through a script performed on the server), you could hack it by importing the records to a dummy table that has a dummy record for the field titles, then exporting from there.

Of course the best solution would be to improve the performance, so that updates can be made in situ.

Edited September 4, 2015 by comment

[eos]

Please tell me that you mean 15,000 Records and not Rows?

Excel::row = FileMaker:.record
Excel::column = FileMaker::field

[Auch info]

Right. The problem is not upgrading to v.14, but downgrading to WebDirect.

If you cannot find any other way to solve this (e.g. through a script performed on the server), you could hack it by importing the records to a dummy table that has a dummy record for the field titles, then exporting from there.

Of course the best solution would be to improve the performance, so that updates can be made in situ.

I will write a tool to convert .mer files to excel. It is the easiest way for me. Thank you.

[Josh Ormond]

I'd be curious to see a solution where the data entry is easier in Excel than it is in FM. What are you doing with the inventory in FM? ( not discouraging it's use, just wondering what added functionality you are adding to the vanilla spreadsheet that helps you manage the inventory )

Especially if you have people working on the database between the export and the import, it can open a large loophole for errors and overwritten data.

[xochi]

I will write a tool to convert .mer files to excel. It is the easiest way for me. Thank you.

I think you can just export your .mer file, rename to .csv and Excel will open it right up.

[Auch info]

I'd be curious to see a solution where the data entry is easier in Excel than it is in FM. What are you doing with the inventory in FM? ( not discouraging it's use, just wondering what added functionality you are adding to the vanilla spreadsheet that helps you manage the inventory )

Especially if you have people working on the database between the export and the import, it can open a large loophole for errors and overwritten data.

Well, this is a museum inventory with more than 15000 records and 100 fields in one table. They currently proceed to update data to collections. Each item has an inventory number.  Some collections are small so they update directly in WebDirect. Some are big (like the Mochica collection with 112 items), so they extract items they need in an excel file, then they update a field with the same value to all records (by dragging the mouse in the excel column), then they import back the file. The employees are not computer's friends... I has to be simple. And I have to find a solution to avoid them to enter the same value 112 times (or more). We use extraction as well to send data to the national museum agency or to print the annual mandatory paper version of the inventory.

Only one employee works at a time on one collection, so there will (should) be no problem with overwritten data. In any case we have a daily backup.

Finally as a solution I wrote a little tool to convert from '.mer' to Excel.

I found another strange thing : when I export data to '.mer' format from WebDirect menu, I get it in UTF-8. That's fine. But if I want to export data to '.mer' file from within a script, I have only the ANSI or Windows encoding format !

Is it possible from a script to export data locally to the server, then run the tool to convert the file to finally download it to the user ? This would be a very good solution for me.

Thank you

 

I think you can just export your .mer file, rename to .csv and Excel will open it right up.

I tried but there are problems within the ';' character that can be found in the data itself, letting excel thinking that it is a column separator.

[Josh Ormond]

From FileMaker if you truly need that value in every record, there are a lot of options to automate that process. It's FileMaker's specialty.  That would be a lot less fragile than export, update, import ( hopefully no errors or partial imports ).

[Auch info]

From FileMaker if you truly need that value in every record, there are a lot of options to automate that process. It's FileMaker's specialty.  That would be a lot less fragile than export, update, import ( hopefully no errors or partial imports ).

Excellent, I will have a look. Thank you.

[comment]

they extract items they need in an excel file, then they update a field with the same value to all records (by dragging the mouse in the excel column), then they import back the file.

To me, this sounds like your real issue is with the structure, and all of this is just an elaborate workaround. While it's certainly possible to script such operation in Filemaker itself, it should not be necessary. Instead, there should be a parent record where this value would be updated once and for all (literally).

 

100 fields in one table.

This also raises a flag. There are very few objects that require 100 fields to describe them. I suspect you are using fields where records in a related table are called for.

[Auch info]

Excellent, I will have a look. Thank you.

The feature seems not present in WebDirect.

To me, this sounds like your real issue is with the structure, and all of this is just an elaborate workaround. While it's certainly possible to script such operation in Filemaker itself, it should not be necessary. Instead, there should be a parent record where this value would be updated once and for all (literally).

 

This also raises a flag. There are very few objects that require 100 fields to describe them. I suspect you are using fields where records in a related table are called for.

I had to work with what has been designed 10 years ago... And I don't have the possibility to re-engineer it. For the fields, there is a lot because some are common, some are specifics following the item type (painting, sculpture, tools...), and the designer putted everything in one table...

[comment]

there are a lot of options to automate that process.

The feature seems not present in WebDirect.

Which feature are you referring to? Scripting?

[Auch info]

Which feature are you referring to? Scripting?

This one : http://www.filemaker.com/help/12/fmp/html/add_view_data.4.48.html

[Records menu > Replace Field Contents] does not exist in WebDirect 14

[comment]

This one : http://www.filemaker.com/help/12/fmp/html/add_view_data.4.48.html

[Records menu > Replace Field Contents] does not exist in WebDirect 14

I believe the suggestion was to script this for the users. If your users are not, as you put it, "computer's friends", then you certainly don't want them to be able to use Replace Field Contents on their own.

[Josh Ormond]

I believe the suggestion was to script this for the users. If your users are not, as you put it, "computer's friends", then you certainly don't want them to be able to use Replace Field Contents on their own.

+10

@Auch info - Lookup script steps for Loop and Set Field.

Edited September 16, 2015 by Josh Ormond

[Auch info]

+10

@Auch info - Lookup script steps for Loop and Set Field.

I have a look. Thank you to all.

[comment]

Lookup script steps for Loop and Set Field.

That's not really necessary: Replace Field Contents is available as a script step.

[Josh Ormond]

Thanks Michael. Are there any limitations to a besides a forced "Perform without dialog"? I've only used WD a limited amount and haven't tested that script step yet.

[comment]

Are there any limitations to a besides a forced "Perform without dialog"?

I don't know - I am relying on the help file that mentions this as the only one.