Anyone have a function to convert tab delimited text to comma separated values? Search Brian Dunning but didn't find anything.
David, is the text already a FILE that is external to FileMaker and in the Tab-delimeted format?
You can do this with:
open with excel or other spreadsheet, save as CSV
open with text editor, use find/replace to convert (remember that numbers don't need to be quoted)
IMPORT into FileMaker and export as CSV
probably other options, depending on what you want to really do
What do you want to do with the files after it's converted?
User wants to import contacts from a text file (exported by another program (DOS!)).
I have her pasting the text from the file into a global and clicking a button to run a script that parses the csv, imports the records into a temp table, massage and imports the contacts into the appropriate tables. That part works fine.
Problem is she gets her tab and csv export mixed up and sometimes pastes the text from a tab file into the global which screws stuff up. I've got a check in there now that pops an error if it's not a csv (using ValueCount() and PatternCount() ), but it seems like it should be pretty simple just to drop a script trigger on the field that converts the text silently. Substitute() should handle most of it, but I'm guessing there are gotchas about escaping characters, etc.
So I'm looking for a function that will take tab delimited text and make it comma separated.
Yes! numbers are not always quoted, but depending on your parsing routine, they can be. The double-quote (") needs to be escaped to (\") in any text value before adding the double-quote around the "values". Commas inside quoted text are fine. If a number has commas, of course, this can be inside a quoted number value. The comma separates the text (and number) values.
Does that help?
Think this will do it? I won't have a chance to test it for a couple days...
$$tab is the tab-delimited text
tabbed = $$tab ;
escaped = Substitute ( tabbed ; "\"" ; "\\\"" ) ;
csv = "\"" & Substitute ( escaped ; " " ; "\",\"" ) & "\""
What about embedded returns? How do you want them handled? Or perhaps - it is already doing what you want. I just tried it by operating on the clipboard contents when using the copy all records command, which returns tab and return delimited text.
I don't know enough about tab/comma delimited files to know if/how embedded returns are handled. Would double quotes be enough?
You can see how Filemaker handles special characters when it exports in the help. Of course, other applications may not follow the same rules.
Wouldn't it be easier to parse the tab-delimited text directly, instead of converting it to CSV first?
Well, I had built the script around csv, it seemed easier to change the input than adding additional parsing to the script. I was expecting someone had already solved the problem of converting tab to csv with a custom function.
Substitute ( theText; Char(9); "," )
Retrieving data ...