10 Replies Latest reply on May 15, 2012 11:39 PM by beverly

    Export calculated tabs...

    johnbuckingham

      As a result of a client migrating to another system, I am tasked with exporting data to a text file from a FileMaker 11 based system where Tab characters are included within a field.

       

      If I export to a 'tab delimited' file, FileMaker converts the 'internal' Tab characters to spaces. Selecting other types of file (csv, merge) includes the Tab characters, but also adds other characters meaning I then have to post-process the exported data before importing it into another system.

       

      As there are quite a lot of files to export and the task, once started, will need to be completed quickly, I would like to find a method where the file doesn't require any further processing. But I can't determine a way to do it.

       

      Can anyone suggest a method?

       

      With thanks in advance

      John Buckingham

        • 1. Re: Export calculated tabs...
          TomHays

          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.

          1 of 1 people found this helpful
          • 2. Re: Export calculated tabs...
            beverly

            John, what delimiters are you going to use then? If there are tabs in fields, then the tab cannot be the delimiter.

             

            Beverly

            • 3. Re: Export calculated tabs...
              Mike_Mitchell

              John -

               

              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?

               

              Mike

              • 4. Re: Export calculated tabs...
                wsvp

                Hi John

                 

                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…

                 

                Mike

                • 5. Re: Export calculated tabs...
                  johnbuckingham

                  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!

                   

                  Thanks again
                  JB

                  • 6. Re: Export calculated tabs...
                    Paul Jansen

                    John,

                     

                    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.

                     

                    Paul Jansen

                    • 7. Re: Export calculated tabs...
                      beverly

                      Or open in Excel and let it export to desired format?

                       

                      Beverly

                      • 8. Re: Export calculated tabs...
                        wsvp

                        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.

                        • 9. Re: Export calculated tabs...
                          Malcolm

                          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?

                           

                          Malcolm

                          • 10. Re: Export calculated tabs...
                            beverly

                            They are spaces. See this

                             

                            http://www.filemaker.com/11help/html/import_export.16.31.html#1029588

                             

                            The same applies to FM12.

                             

                            -- sent from my iPhone4 --

                            Beverly Voth

                            --