13 Replies Latest reply on Nov 22, 2013 12:39 AM by disabled_winfried

    Exporting records question

    bartprins

      Hi there (again),

       

      I've got another question about exporting, i need to export fields from 2 tabels into a .txt file with no comma's or tabs so a fixed fileformat.

      Last week i got the question answered where i should collect all the fields in one other field and then export this.

      This works great!!

       

      However i'm running into the next problem which is when i do this there should be a end of line or CR/LRF command or something like that after fixed number of fields, is this possible ?

       

      For example i've got the field "Name" "First Name" "Adres" "Zipcode" and "City" in one table and ProductWeek1,2,3, and 4 in another table,i want to export that as

       

      NameFirstName (next line in the txtfile)

      AdresZipcodeCity

      ProductWeek1

      ProductWeek2

      ProductWeek3

      ProductWeek4

      NameFirstName (next line in the txtfile)

      AdresZipcodeCity

      ProductWeek1

      ProductWeek2

      ProductWeek3

      ProductWeek4

       

      Is this possible ?

       

      Much oblidged and thanks for reading !

       

      Bart Prins

      The Netherlands

        • 1. Re: Exporting records question
          LSNOVER

          Bart.

           

          Completely possible, just use the Char() function to apped the control characters you need.   char(13) is a return I believe, char(9) is a tab.  These are the ASCII values, so you can look them up and plug in what you need.

           

          Cheers!

          Lee Snover

          1 of 1 people found this helpful
          • 2. Re: Exporting records question
            bartprins

            Hoi Lee,

             

            Worked great used char (13 ) for CR and Char ( 10 ) for line feed thanks

            However what i mentioned above only works with a field within a record, not with say export all records, do you have any idea ?

             

            Much oblidged !!

             

             

            Bart Prins

            • 3. Re: Exporting records question
              LSNOVER

              Bart:

               

              OK, you can try a couple differnt things, I'm not quite sure how your doing your export.

               

              Are you using a standard export format and exporting all your records?  Or are you exporting a single field?

               

              How are you creating your fixed length record data?

               

              Typically I would assemble all my data into a single $variable then put that into a utility record into a single field and then export that single field. 

               

              You would build your data out in the $variable by looping through your records, and concatenating your rows into the variable, ending each line with the appropriate control codes.

               

              If your using the standard record import, it's a little trickier, because Filemaker is going to attempt to append a standard CR a the end of each line.

               

              One way of handling this is to put a calculated field into your table with a unused character or string of charachers (like a tilda, or a dot).  Then after you export the file, use an editor or a utility to find the character and the regular end line, and replace with what you want. 

               

              Last, if you need more flexibiliyt, take a look at Troi's text file utlity plugin which will give you many options not built into Filemaker.

               

              Regards,
              Lee

              • 4. Re: Exporting records question
                bartprins

                Hi Lee,

                 

                Thanks for the reply, was away for a couple of days but didn't got the problem solved, i looked at Troi's text file which looks great, but i don't think it will help me out at this point it's probablly something really simple but i'm fairly new to this. I made a sample database so i can explain the problem more visually, perhaps better. Perhaps it is the relationships ?

                 

                I have a db with 4 tables, Basic, Mandatory, Common en Product. In the Commonlayout i collect all the fields from the other three tables where Product is a portal with multiple records.

                 

                I've added two records for Basic and Mandatory and 7 records for the Productportal (3 for the first customer and 4 for the second customer).

                I want to export all the data for the two customers with al productrecords also in a specific order: it would look like this:

                 

                LastnameFirstname          (Customer1)

                StreetNumberCity

                Product

                Product

                Prodcut

                LastnameFirstname          (Customer2)

                StreetNumberCity

                Product

                Product

                Product

                Product

                 

                It would be exported to a txt file with no delimeter such as a tab or comma but pure plain text with fixed lenght wide.

                 

                I attached the sample file perhaps this makes it more clear

                 

                I would be very much oblidged if you or anyone else would have to time to look at it !!

                 

                Bart Prins

                The Netherlands

                • 5. Re: Exporting records question
                  LyndsayHowarth

                  There are some customfunctions on dunning.com which might suit this purpose...

                   

                  - Lyndsay

                  • 6. Re: Exporting records question
                    beverly
                    1 of 1 people found this helpful
                    • 7. Re: Exporting records question
                      bartprins

                      Hi Lyndsay and Beverly thanks for your quick replies, much oblidged!

                       

                      I looked on Dunnig but didn't really found what i was looking for but will take another look, thanks

                      @Beverly Thanks these will help out ! This was what i was looking for formatting wise

                       

                      However besides the formatting which i will work out thanks to Beverly, i don't seem the get the data from the Product table pulled together with the other three tables, i think i'm overlooking something but as said i'm a newbie on this.

                       

                      Could you take a look at the sample perhaps ? Thanks!!

                      • 8. Re: Exporting records question
                        christiank

                        Here is my solution. I hope its that what you search for

                        • 9. Re: Exporting records question
                          RubenVanDenBoogaard

                          Hi Bart,

                           

                          Herewith a solution to export the data;

                           

                          I made a script that loops through the records and fills a variable with the data you need.  Then puts the complete text in a global field

                          to export it to a text file.

                           

                          Hope that helps,

                           

                          Best regards,

                           

                          Ruben van den Boogaard

                          Infomatics Software

                          ruben@infomatics.nl

                          • 10. Re: Exporting records question
                            bartprins

                            Hi Ruben en Christank,

                             

                            SUPER that's it, @Christiank sorry couldn't give another correct answer

                            @ Ruben are you dutch ?

                             

                            Thanks !!!

                             

                            Bart

                            • 11. Re: Exporting records question
                              bartprins

                              Hi Ruben and ChristianK,

                               

                              Both your solutions work great on a Mac but on PC Windows 8 with FM pro 12 i don't get an enter after the lines, is this because of Char ( 13 ) ?

                               

                               

                              Thanks for your reply

                              • 12. Re: Exporting records question
                                RubenVanDenBoogaard

                                Hi Bart,

                                 

                                You could also use the ¶  instead of the Char(13) ,  so you have to replace the Char(13) with   "¶"

                                 

                                FileMaker translates the ¶ correctly on Windows, where you need a return+linefeed

                                 

                                And yes, I am Dutch.

                                 

                                Best regards,

                                 

                                Ruben van den Boogaard

                                Infomatics Software

                                ruben@infomatics.nl

                                • 13. Re: Exporting records question

                                  Please read <http://fmdiff.com/fm/cleantext.html> (about half way down) on silent FileMaker conversion of characters on export.

                                   

                                  Winfried