Exporting as to Excel, removal of spacers

[Alan Henschke]

I am exporting a Emergency Dispatch Database (filemaker) to an Excel workbook in order to do response time calculations, graphs, etc.. Here is my problem: I can export the fields to a csv file and open them in Excel without a problem. My problem is the small squares that show up in the csv file. They are separators for multiple time entries. I can not "Replace" them in Excel (can paste them in to the replace menu). I have tried cloning the FM databse and importing the data into the clone and removing multiple entries. Still no help.

Any help would be appreciated. I work for a small non-profit Ambulance and need to get this response data.

[Fenton]

What do you mean exactly by "multiple time entries"? It's probably either: 1. Repeating field, or 2. Paragraph returns.

The latter is NOT going to translate well to .csv; which uses carriage returns (& line feed on Windows) as line endings.

A multi-line field is separated internally with ASCII 11, the repeating field separator is ASCII 29.

If it's a repeating field then you could create a clone of your file. Then import the records. It will ask if you want to split repeating fields into separate records. Say yes. You end up with a lot of redundant data, but get rid of ASCII 29.

If it's multi-line fields, then you've got more trouble. It can be done with XML/XSL. But one has to wonder what's the point of putting any of this into Excel? It can't calculate with multiple values in a field. (I don't know what Excel uses internally for this.)

So, the closing question is the same as the first. What do the multiple values mean, and how would you think to use them for calculations? Perhaps they should be moved to separate fields? (a FileMaker process)

[comment]

You can use the Substitute() function in Excel. Copy the offending character into a cell, and use that as the reference for the "old_text" parameter.

[Alan Henschke]

the multiple times occur from mutliple fields for a given itme. For instance: "timeenroute" has fileds for upto 5 differnet units(responding abulances, paramedics, etc.)

I tried the clone process and separated the fileds. I still end up with the characters.

Once exported to excel, I then edit the data so only one time per cell exists. The clone feature does help me with this and I will be using it; but, I still need to get rid of the extra characters that show up.

Than you for all your help. We appreciate it.

[Alan Henschke]

I tried your suggestion. When I try to copy the character(s) into the substitute formula all I get is a space. The characters don't cut and paste.I can manualy delete the characters.

[comment]

Do not copy the character into the formula. Copy it into a cell.

Once you have a cell, e.g. D1, that contains this character, and only this character, construct the formula

=SUBSTITUTE(text,$D$1,"").

[Fenton]

You have to understand that FileMaker (and Excel) have to INTERNALLY store a paragraph return WITHIN a field as something other than a paragraph return. Similar for repeating fields. Otherwise it would not be a "record" or a "row," which are separated by returns; you could not export as tab-separated or comma-separated (.csv).

When you manually type, or when you Copy, you get returns, translated transparently for you. But when you export you don't. It is possible, using a text editor to insert one of these characters. Or you can use XML/XSL to strip or change them.

But that's not the direction to go. Basically, you have too many values. Since you're deleting some anyway, once you get into Excel, why don't you figure out which you want in FileMaker first? Then export only what you need. If you don't export these multiple values then you won't get the weird characters.

"the multiple times occur from mutliple fields for a given itme. For instance: "timeenroute" has fileds for upto 5 differnet units(responding abulances, paramedics, etc.)"

This is not enough for us to go on. You'll need to explain in detail just what you mean, in English and in FileMaker. We can't see your file; we rely on you to convey the structure. It is going to be a little tricky, so we have to know exactly what we've got. We also have to know what the intended output would be. It's not apparent to me how you could chart this (not that I'm a chart expert :-).

It sounds like the typical "flat" structure, where you have several distinct fields for each "possible" entry; but only 1 has a value. In which case a FileMaker calculation could pick up the value.