Choosing another delimiter for import

[GarrettC]

First off, have not been here in a while, amazing category list. AJAX and Flash were the last things I would expect on the Filemaker forums, we have come a long way...

To my problem. I have not done this in a while and I am sure (maybe I am wrong) that you can change delimiters for an import but I cannot locate where do to this option.

I have been sent a CSV file with the 'pipe' as a delimiter. I am helping an academic researcher with a project and she sent me this file.

Thanks so much for any guidance

[Fenton]

No, FileMaker won't let you specify pipes. Excel will let you do this. If you have Excel, you could import into there first, then into FileMaker; that would work. Or you could open it in a text editor first, and do a Find/Replace for the pipe. TextWrangler from http://www.barebones.com is an awesome free text editor, AppleScriptable, and recordable. There's other ways to use AppleScript to do this, if you have to do it often, and it needs to run without user intervention.

[GarrettC]

Fenton, thanks. I could replace the 'pipe' but what will Filemaker allow? Am I stuck with only tabs and commas?

The reason they chose a 'pipe' is am sure there are commas in the text as some of the data is essay text.

Edited March 4, 2008 by Guest

[normanicus]

Once the data is in Excel Filemaker seems to import any characters in the cells. I just did a test and it imported this into a field:

!"£$%^&* " " ,;()_}{~|

[GarrettC]

Fenton and Normanicus, thanks for your help. I may not be asking quite the right question because answers are not providing enough for me to go ahead.

First off I do not own a copy of Excel, one of the reasons I moved to Filemaker years ago is so that I would not have have to own Excel. But I do own Apple's Numbers application which imports and exports everything Excel does and I own a number of text editing applications and have executed the kind of hunt and replace, per character, you are suggesting.

Fenton you answered my question directly saying I could not use the pipe but what you did not say and what I do not know is, can I change the delimiter in Filemaker? And of coarse how?

Normanicus, are you saying that if I can pull off the switch to another delimiter in another application prior to import to Filemaker that could get the fields separated that way? How would I go about that? Do I need to save it as a particular type of file, such as CSV? It would seem that if I brought the data in using one of those characters you list as a tab delimited text file or CSV, Filemaker is going to blow past the delimited character and give me one monster field of data.

Please illuminate and thanks again.

[Fenton]

You have to (or should) change the delimiter before importing, with a text editor or with another application. I know Excel (optionally) lets you specify what the delimiter is, so | is fine. Once it's in Excel you can just import into FileMaker, as Excel is one of the supported Import formats.

So, the question is, "Does Numbers support the option to choose the delimiter?" If so, then you could import into Numbers, then export as Excel. You wouldn't have to open it afterwards, just import the Excel file into FileMaker.

If there are no Tabs in your data, I don't think you need to use a spreadsheet go-between. I'd just do a quick Find/Replace for |, replace with Tab (t) in TextWrangler (or equivalent, even plain AppleScript can do this). This could be run via AppleScript to be automatic.

Edited March 4, 2008 by Guest

[GarrettC]

Fenton, your the tops! Thanks.

Asked and answered, recipe in hand. I will come back and tell the tale of Apple's Numbers here so that archive will have a record for someone in the future in a similar situation.

Thanks again.

[normanicus]

You don't have to buy Excel. Just get Open Office and use the Calc application. I just tried it and it can save in Excel format. It is free,

Norman