Filemaker to Tab-Delimited File with Headers

[jwnacnud]

Filemaker pro 7 (winxp and osx)

I have products in a database that I need to upload to my oscommerce shopping cart (easypopulate)

The shopping cart requires a tab delimited file with header rows.

My question: How to export the products AND a header row. I have tried making a record with the header information in it but I have some fields that are populated by a calculation and it always exports the calculated info instead of the header information in those fields.

I am currently exporting, running an applescript and then uploading. While this works fine under osx, it does not under winxp.

Please help anyone.

Jeffrey Duncan

801.224.2260

[email protected]

[Fenton]

XML Export, with XSL stylesheet.

<?xml version='1.0' encoding='utf-8'?>

exclude-result-prefixes="fmp" version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

[Wim Decorte]

Fenton's method is the best way the go: it's completely Xplat.

If you don't want to go that way, you'll need a VBscript on Windows to do what your AppleScript does on the Mac.

[Fenton]

Actually, the one I posted was not the best, just the simplest. It added an extra tab onto each row, as well as an extra return at the end. The one below tests for the ends, and produces clean output:

<?xml version="1.0" encoding="utf-8"?>

xmlns:fm="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fm" >

[Breezer]

Hi Fenton,

I'd like to learn more about XML, could you point me in the right direction,ie, which book(s)would you recommend, CDs/DVDs, etc.

[Fenton]

The essential book, for FileMakers, is:

FileMaker Pro 6 Developer's Guide to XML/XS, by Beverly Voth

http://www.amazon.com/exec/obidos/tg/detail/-/155622043X/qid=1130111239/sr=1-1/ref=sr_1_1/104-3145338-8351144?v=glance&s=books

It also helps to have some basic XML/XSL knowledge (especially the later, though you need some XML to understand XSL, which is kind of a subset). A book that I used was:

Sams Teach Yourself XSLT in 21 Days (Paperback) by Michiel van Otegem

http://www.amazon.com/exec/obidos/tg/detail/-/0672323184/qid=1130111604/sr=1-2/ref=sr_1_2/104-3145338-8351144?v=glance&s=books

It is well-written for a beginner. It goes pretty slow and explains the basic mechanisms very clearly, with lots of examples. It helps you to not only use xsl, but actually know why to use one form instead of another; such as the difference between using templates and "for-each" (which is somewhat mind-boggling otherwise). It is a little old however.

You could spend some time at a local bookstore, if you have one that has a good computer section, and try and read their XML books. See if the author can explain clearly, without losing you or putting you to sleep; it isn't easy.

There are also XML/XSL resources at FileMaker, with recommended books:

http://www.filemaker.com/developers/resources/xml_xslt.html

Look at some of the example files at their xml library area. Most of them are a little specialized and complex, but there are some more basic examples also.

I have a few examples scattered here and there on the forums here, mostly to do with translating. There is a lot of info online, on some of the xml sites.

W3: http://www.w3.org/TR/xslt

TopXML: http://www.topxml.com/xsl/xsltref.asp

Jeni (who also has a book) http://www.jenitennison.com/

XML.com: http://www.xml.com

[Vaughan]

"The shopping cart requires a tab delimited file with header rows."

Err, isn't this merge file format? (.mer)

No need for XML.

[Greek]

Does your solution assume that a web browser that supports XML will recieve the file?

Or does this work(produce a tab separated file with headers) no matter what application opens the file?

[Fenton]

Vaughn, Merge files are comma-separated. jwnacnud specified a tab-separated with headers, which FileMaker can't natively do (maybe should, but doesn't).

Greek, it just produces a text file. You'd have to upload it in a separate process, not just native FileMaker (I don't think). You can do xml with FileMaker 6, though it's more of a pain to deal with file paths (can't see 'em). But otherwise the xml support is similar.

The xml export/import dialogs are a little unstable, especially when you decide to "Replace" an existing path. You can get stuck in a situation where everything is grayed out. In that case switch to another window/app then back to fix it. I haven't seen this in 8, at least not yet; but others have. What I'm saying here is: don't have your important files open while you're experimenting with xml export/import, as you may have to Force Quit.

[sbg2]

I have tried making a record with the header information in it but I have some fields that are populated by a calculation and it always exports the calculated info instead of the header information in those fields.

The calculations obviously need to be revised, ex.

MyCalcField = If(ID = ; Header Text; perform normal calculation)

I use this for exporting tab seperated files for use with Amazon.com and it has worked well. FileMaker is long overdue, IMHO, in giving programmers the option to export to:

1) a straight text file.

2) an appendable file.

Edited October 28, 2005 by Guest

[jimkent]

I just solved this issue today with one of those "Duh!" hit your head thoughts. Here it is: (this will only work on PCs)

1. Create a file called something Like Headers.tab 2. Export your file and Choose the TAB format

3. in your script, execute an event that runs an external program called something like Merge.cmd. 4. Open Notepad and create that file (Merge.cmd) an put this one line of code in it "copy Headers.tab+MyFMExport.tab FinalExport.tab"

5. Save the file and run your script in Filemaker. GUESS WHAT your FinalExport.tab file has in it. Whala (sp?)

[hfinger]

I've tried this on a Mac and FM6 and get an XML parsing error. Is there something else I should be doing?

By "this", I mean the XML stylesheet thing. Both choices.

Edited November 15, 2005 by Guest

[Fenton]

I just tried copy/pasting my code into a new xsl file, and also got an error. The error was (paraphrased) that the xml content must begin at the 1st character. So I looked at in in BBEdit and sure enough, the web browser/php whatever had added a space to each line. After I removed them it worked fine. This is a problem with sharing code via web browsers, email, etc.. They can mess with it.

I'd recommend getting a real text editor if you don't have one, so you can see "invisibles." TextWrangler is free, at http://www.barebones.com.

Using "grep" search, to find/replace the 1st space:

^

(there's a space after the ^, which means "beginning of line")

(P.S. Actually you only need to remove the 1st space. It's only the

<?xml version="1.0" encoding="utf-8"?>

which can't have a space in front.)

Edited November 15, 2005 by Guest

[Breezer]

Thanks for the recommendations.