Merge Filemaker .mer format into a .xls/xlsx formatted template
Filemaker is limited in the fact that it can not format the cells it exports in a .xls/.xlsx file. We have a need to format data automatically in an excel export.
We are building a virtual table list, rendering a report for X number of columns, then exporting that in a specific format. Each Row has an indicator as to type (IE "Header", "Summary", "date range", "Spacer"), and each row has consistent formatting across all columns. The only thing that changes is the number of "date range" rows, it could be one, could be 20, so the template would need to account for rowType when it runs. It would also need to trim off the serial number column, and the field name row.
Can someone show me how to take the attached .MER merge file from filemaker, and import the data to make it look like the attached .xls file.
Thanks!
Here is the sample merge file
sample.txt
and the sample output file
sampleOutput.xlsx
We are building a virtual table list, rendering a report for X number of columns, then exporting that in a specific format. Each Row has an indicator as to type (IE "Header", "Summary", "date range", "Spacer"), and each row has consistent formatting across all columns. The only thing that changes is the number of "date range" rows, it could be one, could be 20, so the template would need to account for rowType when it runs. It would also need to trim off the serial number column, and the field name row.
Can someone show me how to take the attached .MER merge file from filemaker, and import the data to make it look like the attached .xls file.
Thanks!
Here is the sample merge file
sample.txt
and the sample output file
sampleOutput.xlsx
ASKER
Hmm, that sounds like it's possible, but also sounds beyond my skillset, as I personally have not dealt with XML out of filemaker, or XLST at all.
I understand the process and can see that as a solution, and also see where it states you can attach an XLST style sheet to an XML export from filemaker.
Can someone with experience post or write an XLST based on my sample files? It should be easy (formatting four row types), just not familiar with XLST at all.
I understand the process and can see that as a solution, and also see where it states you can attach an XLST style sheet to an XML export from filemaker.
Can someone with experience post or write an XLST based on my sample files? It should be easy (formatting four row types), just not familiar with XLST at all.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
maybe i missed it , but here i found is pdf talking about everything with formatting and using filemaker with excel .. its excellent :
http://www.karenvagts.com/text/Vagts_techwriting_excerpt.pdf
and this article contain videos maybe helpful :
http://www.skeletonkey.com/blog/filemaker_edi_excel
http://www.karenvagts.com/text/Vagts_techwriting_excerpt.pdf
and this article contain videos maybe helpful :
http://www.skeletonkey.com/blog/filemaker_edi_excel
The PDF is excellent but only includes the first two chapters of the paper and not the critical third chapter on "Moving Data from FileMaker to Excel". I've written the author to ask if the full text is available.
ASKER
I ended up getting what I needed by creating an excel template that conditionally formats based on a (hidden) first column of a "rowType" indicator export from filemaker.
We use this template, and import the CSV from filemaker using the "data" tab in excel. All rows get formatted automatically.
It's easy enough that we can train the users we need to perform the operation, and also it saves a lot of time from a development standpoint trying to do an XML/XSLT solution.
We use this template, and import the CSV from filemaker using the "data" tab in excel. All rows get formatted automatically.
It's easy enough that we can train the users we need to perform the operation, and also it saves a lot of time from a development standpoint trying to do an XML/XSLT solution.
Excellent solution. I'm impressed. Did you come up with this technique yourself?
The two key parts are to define named Styles in the Excel file for each type of formatting and to create the XSLT style sheet that takes the XML export from FileMaker and applies the correct style to each different kind of data. That hard part is creating the XSLT style sheet and unfortunately that's as much guidance as I can give you.
Here's a FileMaker Knowledgebase article that might be helpful.
http://help.filemaker.com/app/answers/detail/a_id/6167/~/exporting-data-from-filemaker-pro