Jump to content

Double quotes in the data


Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

doublequotes.png.295c7096cd725b360c19a49ed0ed4e01.png

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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/.

Link to comment
Share on other sites

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..

Quotes.png.4a6b753b4daf110c08a3c46fba743052.png

Edited by MHardee
New Info
Link to comment
Share on other sites

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.

example.png.ea78cb412038dd2bdfb9cf3d7c1fb05c.png

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

examplecsv.png.7dbd1109343c2e25faba43ad150f76b1.png

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

textqual.png.ae0d73ba1ff61d71e307ca58c4fe9bee.png

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 8 years later...
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.

Link to comment
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...