You would think, right? A bit of missing functionality that should have been added long ago in my opinion.
No options other than CSV and TAB delimited. Having said this, CSV should be okay in your case where you said that field data can contain comma's and tab's. It is certainly common for fields to contain comma's in my experience. A proper CSV file should have all text data surrounded by a pair of double quotes so any comma's within that string are ignored. I have not tested this with the text string containing tab chars though.
You are correct, there is no native option to specify a delimiter. FileMaker supports comma and tab delimited files by default.
There is an easier process for dealing with delimited files though rather than ripping them apart manually:
-Insert your file into a text field.
-Use a replace function to replace your old delimiter with a compatible one.
-Export to temporary csv or tab file.
-Import modified file.
Navicat is an unfair comparison, it's a program strictly for dealing with data. FileMaker is much heavier on the UI side than it is on the database side. That's like walking into a pizza place and asking for a loaf of bread. Incidentally, you CAN use tools like navicat to process your data into a better format for importing to filemaker.
And you have nailed one of the reasons there are Ideas on this forum for revising the FM Import/Export functionality.
My work-around: if I have a file with alternate delimiters, I will import into ONE record, ONE field and text parse (using FM built-in text functions). If you happen to have a return (Character ascii 13) at the end of each "line", then you can use this to import into multiple records, one field. These can also be parsed with the added bonus of having your "records" separated already.
Typically, I will use a "temp" table for this kind of work and push data (as parsed) into the "real" table(s). I've been parsing text into FM for as long as I've been working with FM. We now have $variables and this greatly helps the process!
You're funny, and yes, I was mainly using Navicat for an example of missing functionality.
But, OTOH, importing data is a very basic data thing that should be flexible so on that point, I thought the comparison was appropriate.
Workarounds were not the issue, but thanks for that information too. Since Java is so much faster on File I/O, I would pre-process the file in Java if needed before importing.
Maybe in 15....
I've run into this situation before. I had a file that used a "~" as a field separator. Since this would be ongoing, I created a script to load the fee into a temporary table and then use a custom function from Brian Dunning's website called nthField (FileMaker Custom Function:nthField ( field ; delimiter; occurrence )) to parse the fields and load into the right table.
Preprocessing is not in everyone's wheelhouse. It's a very valuable method. I just assumed it an alternative for you already.
My answer is for the higher percentages of FM developers out there with only one option: in-FM post-processing.
-- sent from myPhone --
I agree FM could do a better job. If you're good with programming and know CURL, the MBS plugin has the CURL functions which are great text manipulation controls including substituting characters and many other things. MBS can also read and change text files, rename, copy, etc. While a pain to set up the first time, you can set up a script that will make appropriate text manipulations prior to import or just read it into a variable and mash it up with FM like you talked about. You could use the virtual report technique to handle different separator characters too. And while it involve a little work, there is nothing wrong with importing and then manipulating.
But as Mike Beargie said, if it is a one time import and you just need simple substitutions, I just open it in a text editor, do the substitutions and then import it.
Thanks for your reply. Yep, I've been doing Java since 2002 so programming web apps is 99% what I do.
I can code the workarounds, I was merely posting to make sure I hadn't missed (even more) basic functionality that seems like it should have been in FM many moons ago.