Csv file issue

[genious]

I am trying to do a straight import of a file into filemaker from a locked Oracle database. I am able to export data but it exports with a semi colon rather than a comma. When I try to import to Filemaker it only sees one big field not many. Is there a work around. I haven't worked with filemaker properly since version 5 so I might not realise the obvious.

[Lee Smith]

Use a Text Editor that can do RegEx and Grep Pattern Find and Replace.

or

Open it in Excel and choose the semi colon as the delimiter.

HTH

Lee

[genious]

Thanks Lee. Is there any way to get Filemaker to do this? I would like to be able to do this on a regular basis, it would be great if I could script it.

Edited November 27, 2009 by Guest

[Lee Smith]

Can you attach a copy of the Raw data file so that we can see what it looks like. If there is confidential data in it, you could work it over in a word processor and replace it with something generic as long as you don't change the basic structure of the raw data.

Lee

[TheTominator]

Thanks Lee. Is there any way to get Filemaker to do this? I would like to be able to do this on a regular basis, it would be great if I could script it.

Yes, you can script it in FileMaker. Write a script that does the following.

Import the data into one field.

Replace field contents to replace with a calculated result substituting the semicolons for commas.

Export the single field into a file using tab-delimited format.

Now the file should be in a suitable format to be read in using CSV.

There are many other ways of accomplishing this, and many are a lot more efficient, but this is pretty direct.

If the data in the CSV file isn't properly quoted, you may have trouble with commas present in the data being mistaken as a field delimiters.

[comment]

Or import the data into a temp table (as one field), use calculation fields to split the line into individual fields, and import the result directly into the "real" table.