tobarstep Posted May 13, 2009 Share Posted May 13, 2009 Using FP 6.0P1e desktop/WinXP and server/2003 (happening on both) I'm composing from a tab-delimited text file, but when I attempt to compose a record with double quotation marks included in the data, FP seems to consider those to be further text delimiters within a field. I have my data source definition set up as a tab delimited file, and I have tried changing the option "Treat field values as tagged text". Neither way made any difference. This isn't the actual data, but for example a field containing: "A"bc is only being read into FP as A, ignoring both the quotes and the text outside the quotes. This is strictly for that field however, as the rest of the record shows fine. Is there a way around this? I don't always have control over the data that I'm sent, and though I have asked them to refrain from using double quotes in the future I'd rather find a way to make it work. Quote Link to comment Share on other sites More sharing options...
Alex Marshall Posted May 13, 2009 Share Posted May 13, 2009 For the field name that is affected by this, write a simple rule to substitute for the field name like so: return NormalizeEntities("FieldName"); In the variable frame where this information will appear, insert the rule name instead. Quote Link to comment Share on other sites More sharing options...
tobarstep Posted May 13, 2009 Author Share Posted May 13, 2009 Thank you for your reply, Alex. Unfortunately the data is not being read into FP to begin with as far as I can tell. The specific record in question is for a sale sign for batteries. In the text file, the field reads "AAA", 8-ct package but when looking at the record in either the preview record selector or the Fields tab in the Show Building Blocks dialogue, all that appears for that field is AAA In my rules I am already using the normalize entities function. Quote Link to comment Share on other sites More sharing options...
Alex Marshall Posted May 13, 2009 Share Posted May 13, 2009 Try the following: create an empty rule and paste this logic. Check the box for "Treat return strings.." Paste this logic: return NormalizeEntities('"AAA", 8-ct package'); Does it return the data correctly on preview? Quote Link to comment Share on other sites More sharing options...
tobarstep Posted May 13, 2009 Author Share Posted May 13, 2009 Try the following: create an empty rule and paste this logic. Check the box for "Treat return strings.." Paste this logic: return NormalizeEntities('"AAA", 8-ct package'); Does it return the data correctly on preview? Yes, that displays correctly. Quote Link to comment Share on other sites More sharing options...
tobarstep Posted May 13, 2009 Author Share Posted May 13, 2009 I'll see if I can attach a screenshot of what I'm talking about. This is a shot of the preview record selector, which I don't believe is impacted any particular formatting rules. In the tab delimited text file, that field has the full text the double quotes around the AAA but it is not being read for some reason. Quote Link to comment Share on other sites More sharing options...
MHardee Posted May 13, 2009 Share Posted May 13, 2009 I'll see if I can attach a screenshot of what I'm talking about. This is a shot of the preview record selector, which I don't believe is impacted any particular formatting rules. In the tab delimited text file, that field has the full text the double quotes around the AAA but it is not being read for some reason. Any chance the data is originating from Excel (or has been in it?).. Excel whacks a whole lot of things.. If the data is not sensitive, email it to me and I'll take a look.. Mark M_Hardee1 at yahoo dot com... Quote Link to comment Share on other sites More sharing options...
tobarstep Posted May 13, 2009 Author Share Posted May 13, 2009 Thanks Mark. Unfortunately it is a bit sensitive still. I did do some experimenting though and found that if a field has a double quote in the middle, it is fine. It's only when the first character of the field is a double quote does it cause a problem. I have a date field in the data which is 5/7/2009 If I put quotes around the middle 5/"7/"2009 then that is exactly what displays. If instead I put quotes at the front "5/"7/2009 then all I get is the 5/. Quote Link to comment Share on other sites More sharing options...
MHardee Posted May 13, 2009 Share Posted May 13, 2009 (edited) Thanks Mark. Unfortunately it is a bit sensitive still. I did do some experimenting though and found that if a field has a double quote in the middle, it is fine. It's only when the first character of the field is a double quote does it cause a problem. I have a date field in the data which is 5/7/2009 If I put quotes around the middle 5/"7/"2009 then that is exactly what displays. If instead I put quotes at the front "5/"7/2009 then all I get is the 5/. But, back to the original issue - has it been in excel? I was going to send back a small chunk that has not touched Excel to see if it makes a difference for you.. Understand the data is sensitive.. Can you make a small (5 record) chunk and strip any sensitive info? Mark Update: I made a small file with quotes in the beginning, middle and end and exported as csv. If I can attach screenshot here, you should see it does work.. Edited May 13, 2009 by MHardee New Info Quote Link to comment Share on other sites More sharing options...
tobarstep Posted May 14, 2009 Author Share Posted May 14, 2009 But, back to the original issue - has it been in excel? I was going to send back a small chunk that has not touched Excel to see if it makes a difference for you.. Understand the data is sensitive.. Can you make a small (5 record) chunk and strip any sensitive info? Mark Update: I made a small file with quotes in the beginning, middle and end and exported as csv. If I can attach screenshot here, you should see it does work.. No, the data has never been in Excel. Here is a screenshot of a test I just did. I typed this directly in Notepad, hitting tab between fields. I had to chop it up some to get in the file size limits, but it should all be visible. Quote Link to comment Share on other sites More sharing options...
MHardee Posted May 14, 2009 Share Posted May 14, 2009 No, the data has never been in Excel. Here is a screenshot of a test I just did. I typed this directly in Notepad, hitting tab between fields. I had to chop it up some to get in the file size limits, but it should all be visible. Did you see my screenshot? I worked fine.. I took a Filemaker file, exported as merge file (a csv file) placed it treating fields as formatted data and it works fine.. Tab delimited and a quote as the first character are particularly evil.. Excel routinely sprinkles quotes and single quotes at the beginning of fields. That being said, did you get yours to work? If not, try .csv.. Mark Quote Link to comment Share on other sites More sharing options...
tobarstep Posted May 15, 2009 Author Share Posted May 15, 2009 Did you see my screenshot? I worked fine.. I took a Filemaker file, exported as merge file (a csv file) placed it treating fields as formatted data and it works fine.. Tab delimited and a quote as the first character are particularly evil.. Excel routinely sprinkles quotes and single quotes at the beginning of fields. That being said, did you get yours to work? If not, try .csv.. Mark Thanks for looking at it, but CSV is not an ideal format for my purposes. Since my data frequently contains commas, CSV would necessitate the use of a text qualifier, which is typically double quotes. I suspect that is what is happening here. Even though I'm using tab delimited and not CSV (which is really just comma delimited) FP is still seeing the double quotes as text qualifiers. That sort of defeats the purpose of using a tab delimited format. Though, just out of curiosity I tried a CSV file, but got the same results anyway. Quote Link to comment Share on other sites More sharing options...
MHardee Posted May 15, 2009 Share Posted May 15, 2009 Thanks for looking at it, but CSV is not an ideal format for my purposes. Since my data frequently contains commas, CSV would necessitate the use of a text qualifier, which is typically double quotes. I suspect that is what is happening here. Even though I'm using tab delimited and not CSV (which is really just comma delimited) FP is still seeing the double quotes as text qualifiers. That sort of defeats the purpose of using a tab delimited format. Though, just out of curiosity I tried a CSV file, but got the same results anyway. Don't get me wrong, I avoid .csv myself. It just seemed odd is that is what works in this (my) case. That's the dilemma to me.. It works on my system and not on yours.. I am attaching the data file I used for mine. Plug it into yours and let's see..quote test.txt Quote Link to comment Share on other sites More sharing options...
tobarstep Posted May 15, 2009 Author Share Posted May 15, 2009 Don't get me wrong, I avoid .csv myself. It just seemed odd is that is what works in this (my) case. That's the dilemma to me.. It works on my system and not on yours.. I am attaching the data file I used for mine. Plug it into yours and let's see.. Your file works for me as well. Looking at your data though, I can see that whatever application you exported it from added the double-quote text qualifiers by default. This actually furthers my suspicions that FP is seeing the quotes as text qualifiers rather than simply characters in the data if the quotes appear at the start of the string. For a CSV that's fine. For a tab delimited file I don't think that is desired behavior. I guess my only recourse at this point is to add an extra logic layer prior to FP composition to reformat fields containing double quotes. It also looks like the quotes that are inside the string got converted to a "fancy" quote of some sort as the character didn't transfer well from the Mac to the PC, but that's not really an issue. Quote Link to comment Share on other sites More sharing options...
MHardee Posted May 15, 2009 Share Posted May 15, 2009 Your file works for me as well. Looking at your data though, I can see that whatever application you exported it from added the double-quote text qualifiers by default. This actually furthers my suspicions that FP is seeing the quotes as text qualifiers rather than simply characters in the data if the quotes appear at the start of the string. For a CSV that's fine. For a tab delimited file I don't think that is desired behavior. I guess my only recourse at this point is to add an extra logic layer prior to FP composition to reformat fields containing double quotes. It also looks like the quotes that are inside the string got converted to a "fancy" quote of some sort as the character didn't transfer well from the Mac to the PC, but that's not really an issue. I believe that is the case as well... For kicks, start fresh, load the data new.. I have had it happen before when I instructed FP it was tab delimited and it was actually csv, it never really recovered.. So, open the data wizard, relocate the file, make sure you're selecting the proper format and be sure to check the "treat field values as tagged text" and see what's up.. It could be the old "can't see the forest for the trees" syndrome.. Mark Quote Link to comment Share on other sites More sharing options...
Ryan_B Posted July 19, 2017 Share Posted July 19, 2017 I realize this post is almost 10 years old, but did you ever find a resolution to this? Quote Link to comment Share on other sites More sharing options...
tobarstep Posted July 19, 2017 Author Share Posted July 19, 2017 I realize this post is almost 10 years old, but did you ever find a resolution to this? What's really strange is that I haven't visited this site in many months until today, and I come across this thread. Sadly, I don't remember what became of this. We've upgraded FP quite a few times since then. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.