1 2 Previous Next 17 Replies Latest reply on Jun 29, 2017 6:21 AM by beverly

    Export to non-native file type or .txt file

    grimofdoom

      How can I export records to a non-native file type (besides the normal Tab, Excel...). I need to export records to my own filetype/format, because the already existing formats will not work for my needs. Is there a way to at least export to a plain .txt file, with some scripting to apply my format, and then rename the file after (or during) creation?

       

      I want custom file format of my own design (which has been problem solved and perfected with manual tests) are to make files for NFC chips- which the other file formats will not work well with (due to, including, extra characters and/or unnecessary formatting). I do have filemaker pro 16 advanced, but could not find anything helpful within the custom functions.

        • 1. Re: Export to non-native file type or .txt file
          beverly

          See if this article (and it's predecessor) helps:

           

          I do many "custom text" exports and these methods help a great deal.

           

          Export Field Contents is script step for one record, one field. But there may be clues in the other methods to help you as well.

          Beverly

           

          Sent from miPhone

          2 of 2 people found this helpful
          • 2. Re: Export to non-native file type or .txt file
            breezer

            There are several ways you could do this in addition to what Beverly has clearly explained in her article, for me, I have found that if I need to include the "headers", ie, field names in the first row, I would export as ".mer" then rename the export file to whichever format I want, such as, ".csv" format. If you want text, you'd rename the file to ".txt" and so forth.

             

            In cases where the fields have to have a fixed length format, I'd create a calculation field and pad the field contents with the string length then use the function "Right ()". For example, one of the fields for export could be a calculation as follows (result needs to be of type text):

             

            Let ( [
            myPadding = "00000000";
            myField = inputField ];

            Case (
            not IsEmpty(myField); Right (myPadding & myField;8) ;
            myPadding))

             

            //This would return a text field that will always have eight characters.

            //You can use spaces in place of the 0

            • 3. Re: Export to non-native file type or .txt file
              philmodjunk

              To state the obvious, text file exports are certainly possible both for export records and export field contents.

              • 4. Re: Export to non-native file type or .txt file
                grimofdoom

                Thanks, these will be very helpful.

                 

                Phil, I did not see .txt/text file for exporting records (although I understood exporting field enough for creating my own file endings)

                • 5. Re: Export to non-native file type or .txt file
                  philmodjunk

                  You'd need to change the file extension after the export if using Export Records. This can be done natively by inserting and exporting into/out of a container field or you can use a system script, or a plug in to rename the file.

                  • 6. Re: Export to non-native file type or .txt file
                    beverly

                    Why after? If the file name is a path in a variable, it can have an extension of your choosing. Are you considering any particular platform or version or FM version with a need to rename after export? 

                     

                    Sent from miPhone

                    • 7. Re: Export to non-native file type or .txt file
                      fmpdude

                      If you know a little Java (or don't mind looking up the plentiful examples online), you can use the included (Free) JDBC driver and read the data you need from the FMP database and write any kind of file you want. Even binary. If you can do what you want in FileMaker so much the better, but other options exist with utterly endless flexibility.

                       

                      Also, Java is fast. Java can write a 1,000,000 line file text file in about 1 second! Just a few lines of code...

                       

                      If you define/state your file format (again, assuming the FMP methods either don't work well, are clumsy, or are too slow), I'll be glad to post a little code for you for testing.

                       

                      HOPE THIS HELPS.

                      • 8. Re: Export to non-native file type or .txt file
                        philmodjunk

                        You are correct, if you are scripting the process as should be the case here. A manual export doesn't list .txt as an option, but that's neither here nor there.

                        • 9. Re: Export to non-native file type or .txt file
                          breezer

                          fmdude, can you post a sample file demo to illustrate how "easy" it would be using JAVA via ODBC? I'm interested in learning how to do it that way since I have a lot of situations where I have to customize my exports for various needs.

                          • 10. Re: Export to non-native file type or .txt file
                            beverly

                            And MacOS often will not let me change an extension afterwards if manually exported file. I just wondered if you'd run into that as well. Might be just some file types.

                             

                            Sent from miPhone

                            • 11. Re: Export to non-native file type or .txt file
                              fmpdude

                              Actually, with Java, it's a totally different driver, also free, also included with FMP: JDBC.

                               

                              • Search for "Java JDBC Example" and find lots of sites.
                              • Search for: "Writing text files with Java" and find lots of sites.

                              -------------

                               

                              Basically though (step zero) you need to get an IDE (Java dev environment). Many are free, like Eclipse, NetBeans, or the community edition of Intellij IDEA. I think Eclipse would be fine.

                               

                              Option 1:

                              Then write a small "Java application" (something you run by itself), using the examples you find to:

                               

                              1. Load the JDBC driver.

                              2. Create a connection

                              3. Create a statement

                              4. Execute a SQL statement to get a ResultSet (similar to a FoundSet in FMP).

                              .

                              .

                              .

                              5. Then in a loop you would read the fields you want from the ResultSet and write them to a text file with any format you can dream up. You could, for example, create a data structure to hold your data and even output that custom data structure to a file. What you can do with File I/O is nearly boundless.

                               

                              -------------------

                               

                              Option 2:. If you want to POST your data to a small "REST" service, and use FMP's "INSERT FROM URL" to that service, that service (Java, in my case) could write the text file right away with no SQL, no ResultSets and such. I use this technique all the time and it works well.

                               

                              --------------------

                               

                              I'll be more than happy to help you if I can.

                               

                              Post your file format rules, post your code and any questions, OK?

                               

                              Feel free to send me a PM if you would prefer that.

                              1 of 1 people found this helpful
                              • 12. Re: Export to non-native file type or .txt file
                                philmodjunk

                                No, I don't use Mac, but I would think that inserting the file into a container field and then using export field contents to export with a new file name would still work. It'd just be a really silly way to do it when you can specify the file name and extension in a $pat variable as part of a scripted export.

                                • 13. Re: Export to non-native file type or .txt file
                                  ChrisJohnston

                                  grimofdoom wrote:

                                  I do have filemaker pro 16 advanced

                                  If the file is a text based file, you can export it from a script in FileMaker 16 Advanced with out any extra characters or formatting like so:

                                  Set Variable [$path; Value: "filewin:/D:/Sandbox/FileMaker/goodPlay.m3u"]   
                                  Set Field [GLOBAL::TextField; TextEncode ( GLOBAL::WriteText ; "utf-8" ; 1 )]  
                                  Export Field Contents [GLOBAL::TextField; “$path”]                         
                                                                                                       

                                  Note that WriteText would be the field that your FileMaker generated text would populate before the export. This can kind of give you a view of what your output file will give you. If you are on Mac OS make sure to change the filewin to filemac for the path variable.

                                  • 14. Re: Export to non-native file type or .txt file
                                    gofmp15

                                    One other suggestion to to use a calculated field to create a field containing the data in the format you need. Then you export this field as a text file and you should be able to set the suffix  .xxxyrr

                                     

                                    Select the set of records and then export only this field and get:

                                     

                                    Formatted data record 1

                                    Formatted data record 2

                                    ....

                                    Formatted data last record

                                     

                                    You could also loop through your records parsing each as needed and setting a global field to

                                    parsed data & PP & global field

                                    and go from there.

                                    1 2 Previous Next