1 2 Previous Next 22 Replies Latest reply on Apr 2, 2015 12:27 AM by rrrichie

    export comma separated field value

    fmcrossl

      I'm trying to export a field with a calculated value (list of comma separated values) as tab text, and want to import this file in another filemaker table. But all comma separated should appear in new records.

      But this doesn't work ;-(

       

      my fields look like this "value1,value2,value3"

       

      any ideas?

        • 1. Re: export comma separated field value
          siplus

          Do you always have the same number of comma separated values, or does it vary from record to record ?

           

          I ask because I want to drive you towards this importing option, which will do what you want (with a prep phase in between)

          • 2. Re: export comma separated field value
            beverly

            comma-separated should be thus:

                 strings are double-quoted

                 numbers are not quoted:

             

            "value1",2,"value3",5,"value10"

             

            Also if you have more than one "line" your return-in-field will be altered and not the CR necessary for import back as CSV.

             

            As I read your post again, you say you want

            all comma separated should appear in new records

            Then you need the return between each value.

            • 3. Re: export comma separated field value
              fmcrossl

              it varies...

              • 4. Re: export comma separated field value
                fmcrossl

                but it's not a repeat field...

                 

                i calculate a field out of other fields, and this calulated field looks like this "value1,value2,value3".

                now i want to export this field of all selected records - AND all the values should be imported into a new table, each value in a new record...

                • 5. Re: export comma separated field value
                  fmcrossl

                  good idea - but its not working

                  now I get only the first value imported...

                   

                  thats what my exported file looks like ;-)

                  Bildschirmfoto 2015-03-30 um 13.59.47.jpg

                  • 6. Re: export comma separated field value
                    beverly

                    Please specify your calculation.

                     

                    I believe you want to do something like this:

                         = quote(field2) & ","

                         & quote(field10) & ","

                         & quote(field3) & ","

                         & quote(field1) & ","

                         & field 2 // not quoted, as a number

                     

                    Beverly

                     

                     

                     

                    On Mar 30, 2015, at 7:45 AM, fmcross <noreply@filemaker.com> wrote

                     

                     

                    export comma separated field value

                    reply from fmcross in Discussions - View the full discussion

                    but it's not a repeat field...

                     

                    i calculate a field out of other fields, and this calulated field looks like this "value1,value2,value3".

                    now i want to export this field of all selected records - AND all the values should be imported into a new table, each value in a new record...

                     

                    • 7. Re: export comma separated field value
                      beverly

                      Please tell us your calculation.

                      Please tell us how you are "exporting".

                       

                       

                       

                      Beverly

                      On Mar 30, 2015, at 8:09 AM, fmcross

                      • 8. Re: export comma separated field value
                        fmcrossl

                        well thats my german calculation:

                        Wenn(HoleWiederholfeldwert ( kal_select ; 1 )="j";

                        Zitat ( HoleWiederholfeldwert ( kal_date ; 1 ) )   &   ¶  ;

                        "")  & 

                        Wenn(HoleWiederholfeldwert ( kal_select ; 2 )="j";

                        Zitat ( HoleWiederholfeldwert ( kal_date ; 2 ) ) &   ¶  ;

                        "") & 

                        Wenn(HoleWiederholfeldwert ( kal_select ; 3 )="j";

                        Zitat ( HoleWiederholfeldwert ( kal_date ; 3 ))  &   ¶  ;

                        "")

                         

                        (result for one record is)

                        "02.01.2015"

                        "03.01.2015"

                         

                         

                        then I tried exporting as tab or csv I get the same problem,  I just get the first value imported

                        • 9. Re: export comma separated field value
                          thurmes

                          I was interested, so I tried replicating what you'd done, setting up the following Text calculation field:

                          Quote ( Second[1] ) & ¶ & Quote ( Second[2] )

                          [translated into your field names, this would say Quote ( kal_date[1] ) & ¶ & Quote ( kal_date[2] )

                          I ignored the If statements and "simplified' the GetRepetition statements]

                          and here's my result (which I opened with Notepad++, set to View/Show Symbol/Show All Characters)(Windows platform)fmcross result.JPG

                          As you can see, what was exported was the first value, then a vertical tab, then the second value. I'm not sure why FileMaker substituted VT for ¶, but I think that's where your problem lies.

                          --Bill

                          • 10. Re: export comma separated field value
                            beverly

                            Yes, Bill. a return-in-field becomes a vertical tab. The help documents state this very clearly. I wrote an article with more detail and a possible solution:

                             

                            <http://filemakerhacks.com/2012/09/23/export-field-contents-as-utf-8/>

                             

                            OR you can push each "line" into a single field, one record per line and export. the natural export includes a return for each record.

                             

                            Beverly

                             

                             

                             

                            On Mar 30, 2015, at 1:06 PM, thurmes <noreply@filemaker.com> wrote

                            I was interested, so I tried replicating what you'd done, setting up the following Text calculation field:

                            Quote ( Second[1] ) & ¶ & Quote ( Second[2] )

                            [translated into your field names, this would say Quote ( kal_date[1] ) & ¶ & Quote ( kal_date[2] )

                            I ignored the If statements and "simplified' the GetRepetition statements]

                            and here's my result (which I opened with Notepad+, set to View/Show Symbol/Show All Characters)(Windows platform) https://community.filemaker.com/servlet/JiveServlet/downloadImage/2-180741-7704/fmcrossresult.JPG

                            As you can see, what was exported was the first value, then a vertical tab, then the second value. I'm not sure why FileMaker substituted VT for ¶, but I think that's where your problem lies.

                            --Bill

                             

                            • 11. Re: export comma separated field value
                              thurmes

                              Thanks, Beverly

                              It makes perfect sense that FM would replace paragraph markers with something else, so that all information exported from a single record will later import into a single record. I hadn't realized that exporting to an XML file would solve this problem in a case like fmcross's. Learn something new every day!

                              • 12. Re: export comma separated field value
                                coherentkris

                                why not export as XML then do the import then script a routine that parses out the non normalized data structures?

                                • 13. Re: export comma separated field value
                                  rrrichie

                                  the comma is for delimiting columns (fields) to make the import create new records you need to delimit the values with a row delimiter ( carriage return or new line. )   char(13) for example or the ¶

                                  • 14. Re: export comma separated field value
                                    siplus

                                    as I said, there's a prep phase in between, in which you transform the list into an array.

                                     

                                    Let's say that you have a max of 10 values in your comma-separated field; you get the list through replace comma with ¶, then you array it like in the attached pic.

                                     

                                    You do the import like I said, splitting multiple values into records, then you do a find for empties and delete them, that's all.

                                    1 2 Previous Next