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
ErootsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresidentCommented:
I think the way to do this is to export as XML and use Excel spreadsheet that has pre-defined Styles. Using and XSLT style sheet on import to interpret the XML and apply the correct, pre-defined Style for formatting. I don't have direct experience in doing this with Excel, but I've done similar things with bringing tagged data from FileMaker into QuarkXPress and InDesign.

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
0
ErootsAuthor Commented:
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.
0
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Will LovingPresidentCommented:
Some clarification: and XML export tags each piece of data in the export with a "tag" that labels the data as kind of data it is. The XSLT style sheet is simply a translator that says, "when you see this tag....apply the following formatting".

Further, rather than say "Make this bold, this green, this currency" as individual formats, you can define an Excel "STyle" and give it a name. Such "Styles" (or sometimes called "style sheets") are also found in Word, Quark, InDesign and many other applications. A "style" is a named combination of formatting. For instance, both Excel and Word have a number of default Styles that you can see under the Format menu -> Style (just click on the popup arrow). You can use the existing styles, modify them, or create your own. For example, the "Normal" Style is:

 Normal Style
You can use or alter this STyle definition. When you do EVERY part of the spreadsheet that has that Style applied will change. This is the power and beauty of using Styles. Apply styles to all your text (in Word or Excel) and then if you change style, it changes the text everywhere it is applied.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErootsAuthor Commented:
@Jordannet - thanks, I know all that article has to offer already. It does not address anything about formatting the cells in excel, which is what I'm trying to do.

@willmcn - I'm also not too familiar with excel styles, is there a way I can format a sheet conditionally based on the first cell of each row? If I could do that it would most likely solve my problem.

Similar to how filemaker conditional formatting works IE:
If (_row_::cell1 = "HEADER") - change font size to 18, change font to bold, change cell background to med. blue.
Id (_row_::cell1 = "SUMMARY") - change font size to 14, change font to bold italic, change cell background to lt. blue.
etc.....

Is that sort of conditional formatting (formula based) possible in excel?
0
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
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
0
Will LovingPresidentCommented:
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.
0
ErootsAuthor Commented:
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.
0
Will LovingPresidentCommented:
Excellent solution. I'm impressed. Did you come up with this technique yourself?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.