1 of 1 people found this helpful
Export to XML with an XSLT file to convert to your desired format.
This requires technical knowledge on your part to get it working, but it can do exactly what you need.
John, what delimiters are you going to use then? If there are tabs in fields, then the tab cannot be the delimiter.
Tom and Beverly both give good suggestions. I've had this exact same issue before, but in my case, it turned out to be irrelevant, because the target system couldn't do anything with the embedded tab characters anyway. Might be something to check - do they even care at the destination end?
I assume by "migrating to another system"… You mean a system you don't have any control over. I have had to deal with a lot of these issues, from both ends… Importing is generally a bigger problem for me as FileMaker will treat imported text files as comma separated even if that option is not chosen. Better text control on import / export would have been a welcome improvement in FM12 ( but I can dream. ) Some developers use plugins to assist in this area… I tend to avoid plugins as I am more of a box product developer.
Your problem may be a bit harder to solve If you have no control at the receiving end. In my opinion Tab and Comma separated formats were conceived by an idiot… These are extremely common characters in most data… The fact that a protocol would choose to use these characters as delimiters baffles me… I suspect the concept dates back to when we stored data on punch cards. I have also run into situations where using the dbase import/export as an option has solved some issues ( I "think" the option is only available on Windows FM. )
It may be wise to find out what other protocols the target system supports. Many systems have had to deal with this issue for a long time… And thus they have developed their own protocol. When dealing with text files as an interface exchange there are obviously no "fields" in the text files. Typically they use uncommon characters as line delimiters, field-delimiters, sub-field delimiters and data separators in the text to be parsed later by the receiving application. Also there are usually segment headers that define the purpose of a given string and determine hierarchy.
Keep in mind some of these protocols are very simple and some are very complex… Unfortunately some are downright stupid… The more complex protocols can often take months of reading and researching to understand. Some have hundreds or even thousands of pages of instruction material ( often poorly written. )
Best of luck…
Well, thank you all, Tom, Beverly, Mike & Mike, for your prompt responses. I guess Tom's answer is the one I was dreading - the one that will work but will take the most time to implement!
Mike 2, yes - they are migrating to a product over which I have no control and the format is STRICT (in capital letters in their documentation) - tab delimited. I've built a file ('migr8') to assist in the export, many fields of which require calculations to convert, so migr8 outputs a string which I then paste into a global in my source file and evaluate one 'export field' per record. This is why the tab characters are internal...
So, while I could (probably) build the XML->XSLT system, it seems the quickest way is going to be to export to a 'csv' file - which will maintain the tab chars and so the field order, then use TextWrangler to strip the single leading and trailing double-quotes!
It was worth a try, though, just in case I was missing the obvious!
You might consider your having your migr8 process populate a set of text fields in a temporary table and then export as a regular tab delimited file. That way all your processing would be in FileMaker and it can be a 1 click process.
Jst a thought.
Or open in Excel and let it export to desired format?
Good idea Beverly... But in high volume situations Excel has a record limitation... I think 65,000 or so... This is a problem for high volume users with data well into the millions.
If I export to a 'tab delimited' file, FileMaker converts the 'internal' Tab characters to spaces.
They may look like spaces but they are not spaces are they? Don't they get converted to something like ascii 11?