Help Weird Filemaker .csv problem on import

I have a file that is in comma quote delimted format. Each record
looks like this:

"Joe","Smith","123 Main Street","Anytown","FL","80044"

The problem is that there is some sort of weird quote that Filemaker
isn't recognizing as a quote. I've pasted both below. I don't know
if it will translate on the web. But it looks like an upside-down
comma vs. a quote.

This is what Filemaker understands "
This is what it doesn't "

The problem is that other than the look, I don't really understand the
difference between " and "

-S

Re: Help Weird Filemaker .csv problem on import

In article
,
squeed2000@yahoo.com wrote:

> I have a file that is in comma quote delimted format. Each record
> looks like this:
>
> "Joe","Smith","123 Main Street","Anytown","FL","80044"
>
> The problem is that there is some sort of weird quote that Filemaker
> isn't recognizing as a quote. I've pasted both below. I don't know
> if it will translate on the web. But it looks like an upside-down
> comma vs. a quote.
>
> This is what Filemaker understands "
> This is what it doesn't "
>
> The problem is that other than the look, I don't really understand the
> difference between " and "
>
> -S

Those didn't come through the newsgroup server mangling - they both
look the same.

In a CSV file there should only be one type of quote to delimit data
values. It is the ordinary straight quote: "

Depending on the application that exported the data to the CSV file
originally, there may also be "curly quotes", also called
"typographer's quotes" or "smart quotes". These are "curled" so that
the start quote looks like small 66 and the end looks like a small 99
(depending on the font used of course). These are technically the same
character in the sense of reading, but computers store them as totally
different characters, and therefore different ASCII values.

BUT, because of this, they aren't seen as data delimiters either, so
could well simply be there as part of a Text field's data.

If you need to replace the "curly quotes" with standard straight quotes
before importing into FileMaker, then this can easily be done on the
Mac using any text editor or word processor (remembering to save as a
Text or CSV file afterwards) since you can type the "curly quotes"
directly into the Find / Replace dialog window:

start 66 quote press Option [
end 99 quote press Shift Option [

On some Mac keyboards Option is called Alt.

You can of course do the same thing with a FileMaker intermediate file
/ Table.

You say it looks like an "upside-down comma", so it could also be
"curly apostrophes" that need replacing - although again these do not
delimit data values either. Some text data may of course contain an
apostrophe: '
These "curly apostrophes" are single version of the above 66 and 99
"curly quotes":

start 6 apostrophe press Option ]
end 9 apostophe press Shift Option ]

The 9 apostrophe is the "normal" apostrophe, just the "curled" version.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Help Weird Filemaker .csv problem on import

They are definitely the "curly quotes". Filemaker created them. I
have a field that I have created that is a global field and it stores
a quote that I typed using the quote key on the keyboard. I use that
quote in other calculations. That's why I think it is odd that there
would be a curly quote problem.

Any thoughts on why Filemaker would turn a normally typed quote into a
curly quote.

-S

Re: Help Weird Filemaker .csv problem on import

In article
,
squeed2000@yahoo.com wrote:

> They are definitely the "curly quotes". Filemaker created them. I
> have a field that I have created that is a global field and it stores
> a quote that I typed using the quote key on the keyboard. I use that
> quote in other calculations. That's why I think it is odd that there
> would be a curly quote problem.
>
> Any thoughts on why Filemaker would turn a normally typed quote into a
> curly quote.

There's an option in the Preferences -> Document. In the General
section you'll find you can use "smart quotes" within that database
file. With the option turned on, FileMaker automaticaly changes typed
quote characters to the appropriate curly version (any a space before
means the 66 quotes and any other character before means the 99
quotes), and apostrophes to the curly version.

You can trun that option off, but it will NOT affect already stored
quote characters. You'll need to re-type a normal quote into your
Global field - that will ripple through unstored Calculations
automatically, but for other fields you will have to do a bit more work
to replace them.

It might be easiet to simply create some new Calculation fields that
replace the "curly" quotes with normal ones, and then use these
Calculation fields for your export CSV file.
ie.
Export_MyField Calculation, Text Result, Unstored
= Substitute (
Substitute (MyField,
g_CurlyOpenQuote, g_NormalQuote),
g_CurlyCloseQuote, g_NormalQuote
)

Because the "Smart quotes" Preferences option also includes curly
apostrophes, so you'll probably need to Substitute those as well.

You'll need a few extra Global fields into which you type the
appropriate character, remembering to turn on and off the Preference
option:

g_CurlyOpenQuote stores the 66 curly quote

g_CurlyCloseQuote stores the 99 curly quote

g_CurlyOpenApostrophe stores the 6 curly apostrophe

g_CurlyCloseApostrophe stores the 9 curly apostrophe

g_NormalQuote stores the normal " character

g_NormalApostrophe stores the normal ' apostrophe

This is what I do in one of my databases.


Depending on what the CSV file is being used for, there might be an
extra hiccup here if you're using a Mac. In my Mac database I export a
"CSV" file, but this then has to be opened into Excel and resaved as a
"Windows CSV" file before uploading to a web database system ...
otherwise it creates a massive mess of the web database's data. This is
because Macs seprate lines / records with a Carriage Return character,
while some Windows applications insist on Carriage Retun AND EOL.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)