9 Replies Latest reply on Feb 12, 2012 8:43 AM by jrenfrew

    Convert tab to csv custom function

    DavidJondreau

      Anyone have a function to convert tab delimited text to comma separated values? Search Brian Dunning but didn't find anything.

       

      Thanks,

      David

        • 1. Re: Convert tab to csv custom function
          beverly

          David, is the text already a FILE that is external to FileMaker and in the Tab-delimeted format?

          What platform?

          You can do this with:

               open with excel or other spreadsheet, save as CSV

          OR

               open with text editor, use find/replace to convert (remember that numbers don't need to be quoted)

          OR

               IMPORT into FileMaker and export as CSV

          OR

               probably other options, depending on what you want to really do

           

          What do you want to do with the files after it's converted?

          Beverly

          • 2. Re: Convert tab to csv custom function
            DavidJondreau

            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.

            • 3. Re: Convert tab to csv custom function
              beverly

              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?

               

              Beverly

              • 4. Re: Convert tab to csv custom function
                DavidJondreau

                Think this will do it? I won't have a chance to test it for a couple days...

                 

                $$tab is the tab-delimited text

                 

                Let([

                tabbed = $$tab ;

                escaped = Substitute ( tabbed ; "\"" ; "\\\"" ) ;

                csv =  "\"" & Substitute ( escaped ; "    " ; "\",\"" ) & "\""

                ];

                csv

                )

                • 5. Re: Convert tab to csv custom function
                  BruceRobertson

                  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.

                  • 6. Re: Convert tab to csv custom function
                    DavidJondreau

                    I don't know enough about tab/comma delimited files to know if/how embedded returns are handled. Would double quotes be enough?

                    • 7. Re: Convert tab to csv custom function
                      comment

                      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?

                      • 8. Re: Convert tab to csv custom function
                        DavidJondreau

                        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.

                        • 9. Re: Convert tab to csv custom function
                          jrenfrew

                          How about

                           

                          Substitute ( theText; Char(9); "," )