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.
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 ¶
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?
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, 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!
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.
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.
1 of 1 people found this helpful
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.
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.
Sent from my iPhone
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!
1 of 1 people found this helpful
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.
Thanks. That's what I ended up doing.
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).
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.