14 Replies Latest reply on Sep 24, 2013 7:24 AM by thomas_staehli

    FM12 - Copy / Paste multiple values from excel

    thomas_staehli

      Hello,

       

      I've just noticed this morning that copy / paste a column from excel into a single text field does not give the same result in filemaker 12 as in filemaker 11. in fm11, it used to paste just a nice list of values and now in fm12 it adds a "tab" character at the end of each line. I've tried to paste with or without style, it doesn't make any difference.

       

      Is there something I can do to make it work as before? I know I can add an auto-enter to substitute the tab value with nothing, but I was just wondering if there was something else I was missing.

        • 1. Re: FM12 - Copy / Paste multiple values from excel
          keywords

          You would be better advised to substitute the tab character with a pilcrow character (¶) in order to maintain the imported data as a list of values.

          • 2. Re: FM12 - Copy / Paste multiple values from excel
            thomas_staehli

            Not really, as when you paste in FM12 you still get a list, but instead of having values separated with a ¶ it's separated with a tab followed with a ¶

            • 3. Re: FM12 - Copy / Paste multiple values from excel
              BowdenData

              Thomas,

               

              You did not mention what platform you were using, so I just tried this on a Mac running 10.8.5 with FileMaker 12.0v4 and FileMaker 11.0v4. I am using Excel from Mac Office 2011. I get the same results that you do. It seems that this is an issue with FMP12 as when I paste into a text editor like BBEdit, I am not seeing the tab characters. Seems like a bug to me. Did you report it as such?

               

              Doug

              • 4. Re: FM12 - Copy / Paste multiple values from excel
                thomas_staehli

                Hi Doug,

                 

                I've tried with 12.0v4 on both mac (10.8.5) and PC (windows 7), using office 2011 and office 2010. It doesn't seem to be a platform issue or an office issue but really a FMP12 change of behaviour. I noticed the same as you when pasting into textwrangler, no tab characters.

                 

                I will report it. I just wanted to make sure that I wasn't missing something ;-)

                 

                Thomas

                • 5. Re: FM12 - Copy / Paste multiple values from excel
                  keywords

                  Thomas, I think this has something to do with how data is copied from Excel rather than entirely how FM handles it. Just running some quick tests shows that SOMETIMES copying a column of data from a spreadsheet and pasting it into various text programs (I used TextEdit, Textmate and Word) the text pastes as a table; other times it pastes as a carriage-return separated list. When the data pastes as a table, it pastes into an FM text field with the tab character on each line, as you describe. On the other occasions, it pastes normally into FM with no tab character. Hence, it seems to me, this is not an FM bug but rather something to do with how the data is being exported to the clipboard. I haven't been able to work out the specifics yet, but will post again if I do. However, I suspect you may need to get rid of the table to solve the FM problem. One workaround is to paste into TextEdit, make the text plain (whick simple removes the table), copy again and paste into FM. Voila! No tabs!

                  • 6. Re: FM12 - Copy / Paste multiple values from excel
                    thomas_staehli

                    Well, all the test I ran, where behaving the same (on windows and OSX). I don't think that it works "sometimes". And it definitely works all the time with FM11.

                     

                    But you're right, I don't think it's really a bug, but a behaviour change between FM11 and FM12. It's no big deal really, but I just wanted to make sure I wasn't missing something before starting to add auto-enter to remove those tabs. I would definitely just copy paste it into a basic texteditor and then copy it into FM, but I definitely don't want to force my users to do that.

                    • 7. Re: FM12 - Copy / Paste multiple values from excel
                      flybynight

                      Unrelated to FMP, but related to your topic, I have noticed similar behavior when copying from Excel and pasting into InDesign. What I want is just an array of tab-delimited text, with returns separating the records/rows. What I get is a table graphic. I guess they are trying to be helpfull?

                      If I paste into TextWrangler first, then re-copy and paste into InDesign, I get what I want. Just like you said you would do… but I agree that I wouldn't expect my users to go through that effort, much less to know to do that.

                       

                      Laters,

                      -Shawn

                      • 8. Re: FM12 - Copy / Paste multiple values from excel
                        keywords

                        Shawn, you say:  I guess they are trying to be helpfull?

                         

                        I think the reason is that a tab is the standard column separator in converting text to tables and vice versa. If you were copying two columns of data from a spreadsheet it would indeed be VERY helpful for the tab to be added between the columns.

                         

                        Thomas, you say: I definitely don't want to force my users to do that.

                         

                        If you set the field up with an autoenter calc (set to always replace existing value) using the following:

                         

                        Substitute ( Self ; "     " ; "" ) [where the search string is a tab]

                         

                        any tab characters that are in the pasted text will be eliminated.

                        1 of 1 people found this helpful
                        • 9. Re: FM12 - Copy / Paste multiple values from excel
                          flybynight

                          Yes, we are kind of talking about 2 different things. I was talking about pasting into InDesign. In that case, I wanted tabs and returns. What I get is a graphic that looks like a table, but isn't editable text. I want to do my own formatting. In the older versions (not sure if the culprit is excel, InDesign or the OS), I got a nice array: tabs and returns. Now, I get a graphic that looks like it looked on screen in excel.

                           

                          In my case, I'm doing several columns. Thomas is doing just one. In that case, I wouldn't want the "extra" tab at the end of each line.

                           

                          Sorry to take things off on a tangent, but just another example of how program behavior changes over time.

                           

                          Laters,

                          -Shawn

                          Sent from my iPhone

                          • 10. Re: FM12 - Copy / Paste multiple values from excel
                            keywords

                            I've had the same experience with InDesign myself.

                             

                            not sure if the culprit is excel, InDesign or the OS

                             

                            I think it's all three; they are ganging up on us!

                            • 11. Re: FM12 - Copy / Paste multiple values from excel
                              usbc

                              keywords make a good point, as do you, about not making an end user run copied content through a third application.

                              Sometimes I include on a layout a text field I name "Scratch Pad". It gets used to paste from Word or screen scrapings, etc.

                              The ON Validation I run a script (or two) as keyword just pointed out and also trim and remove styles, etc.

                              It becomes pretty specialized for each client. And though never all inclusive, it generally advances their cause.

                              1 of 1 people found this helpful
                              • 12. Re: FM12 - Copy / Paste multiple values from excel
                                thomas_staehli

                                Thanks. That's what I ended up doing.

                                • 13. Re: FM12 - Copy / Paste multiple values from excel
                                  alquimby

                                  Why don't you convert the Excel spreadsheet to a FM database and do your work from that? Whenever I receive an Excel from a client I always convert it first. I find the FM file far easier to work in (but I am not proficient in Excel).

                                   

                                  Al Quimby

                                  • 14. Re: FM12 - Copy / Paste multiple values from excel
                                    thomas_staehli

                                    Hi Allen,

                                     

                                    Simply because it's for a search functionality we give to some users. I can't possibly ask them to convert an excel sheet. I just want them to be able to copy paste the content of a column into a global field and click on a button to lauch a script.