Headers in Tab-Separated export??

[nycpost]

I need to generate a Tab-Separated file from FileMaker Pro. The file is to be imported by Avid Media Composer, which is a video editing program.

The program expects the tabbed file to be in a very specific format, specifically to have a mix of Header rows followed by a straightforward set of columns and rows (see screenshot below)

The tricky thing is, the last word in each header row is followed by a carriage return. So, if you use your arrow keys on your keyboard to move through the text to find the tabs, after the word "Heading" you're brought down to the beginning of the next row. In a standard Tab-Separated file, you would have tabs after the word "Heading" for every field in the original document.

In FileMaker, I have my records and fields laid out as a List View and in the correct order to generate just what you see in the above example when exported as Tab-Separated (see screenshot below):

The problem is, when I export these records as Tab-Separated text, it inserts tabs for all the empty fields that don't contain any data. So, the header rows end up with extra tabs in them. These extra tabs are NOT accepted by the program intended to open the file, so the import fails.

So, my questions are:

Is there any way when exporting as Tab-Separated text to have FileMaker skip empty fields? (I don't think there is, but just wanted to ask.)

Does anyone have any suggestions for how I could achieve the desired Tab-Separated document from FileMaker? (I know I could insert a new delimiter and then open the exported file in Excel and do a find and replace to turn that delimiter into tabs, but I do NOT want to add an intermediate step and have the end user open another program. I want to achieve the desired format directly out of FileMaker.)

I've tried making a new field that uses a calculation to join all the necessary fields together with tab characters added in between when needed, which gives me exactly what I want - but when I export this calculation field as Tab-Separated text, it replaces all the tab characters I've entered in the calculation with spaces (Which I guess makes sense: exporting as Tab-Separated text seems to remove all the formatting and it considers tab characters to be formatting.)

Could I achieve the derired result using XML and an XSLT? I haven't learned XSLTs yet, but if I need to in order to do this, I will.

If anyone has any ideas on how to make this work, I'd really appreciate it.

Thanks

PS - I've added an attachment which is an example of the kind of file I'm trying to generate so you can see the tab layout in the header.

11114.tab.zip

Edited May 15, 2010 by Guest

[TheTominator]

Could I achieve the derired result using XML and an XSLT?

Yes, XML + XSLT is the right method to achieve exactly the result you want. This is something that is very easy to do assuming you are familiar with XSLT.

An alternative is to write a script to generate the contents into a field (e.g. a global text field) and Export Field Contents. That will generate a text file with UTF16 encoding.

Not all programs understand UTF16 encoding (2-bytes). It would be good idea to copy and paste correctly formatted text into the global text field, export field contents, and see if it works before investing the time to write a script to automatically generate its text.

[comment]

I've added an attachment which is an example of the kind of file I'm trying to generate

How many of those columns do you actually have in your file? Your picture shows only Name, Tracks, Tape, FPS, Start, End and Duration - but the example has data in other columns as well.

[nycpost]

Thanks, Tominator. Yeah, I thought XSLT might be the way to go. I've been dreading learning it, but I think I now need to overcome my fears and tackle it head on. I know I'm going to have other needs to interface with XML in the future, so I might as well get it figured out now. Thanks for pointing me in the right direction!

[nycpost]

How many of those columns do you actually have in your file? Your picture shows only Name, Tracks, Tape, FPS, Start, End and Duration - but the example has data in other columns as well.

I currently have 13 columns, but I may add more. The image above was only of a small subset, just to give an idea of the layout. Does the number of columns have an effect on the approach one would take?

[comment]

Not really, I was just curious if you have data for them. IIUC, you are trying to produce an ALE - and I am under the impression that the format is flexible, so if you don't have Scene and Take, for example, you don't need to create empty columns for them. You really should check the specs to see what's required and what's optional.

[nycpost]

Not really, I was just curious if you have data for them. IIUC, you are trying to produce an ALE - and I am under the impression that the format is flexible, so if you don't have Scene and Take, for example, you don't need to create empty columns for them. You really should check the specs to see what's required and what's optional.

Yes, you are correct, it is an ALE. The empty fields are not in the "columns" section though. For those, I'm including only the fields I need (and those that are required) and I have values for all of them. The empty fields that are giving me a problem are just in the header rows.

[comment]

I wonder if all of those are required too (TRACKER_VERSION?). In any case, XSLT is definitely the way to go here. You can find some examples both in this section and in the XML/XSL sub-forum.