9 Replies Latest reply on Mar 29, 2016 9:36 AM by disabled_morkus

    Specify delimiter for importing text files?

      Perhaps I've missed it, but I don't see a simple way to specify the delimiter I want to use for importing fields from text files.

       

      Since some text file fields could actually contain TABS or commas, I sometimes get files that are pipe-delimited.

       

      So, in FileMaker, is my only way to deal with these non-tab and non-comma delimited files is that I need to import the whole mess into a table and then parse it there, copy the fields to the right places, remove the original imported file, etc?  I can always write code to pre-process the import file too, if required, but I'd rather not do that...(that's why I spent $500 on FMPA).

       

      Hopefully, I just didn't readily find FileMaker's import functionality?

       

      -----

       

      It's interesting that in Navicat, for example, (another multi-hundred dollar tool), you have many more apparent options for importing, including a more generic "text file" import. A text file import includes multiple settable field separators, as well as a custom one you could specify. Additionally, I find both the Record Delimiter, but also the Text Qualifier to be useful.

       

      Thanks,

       

       

      - m

        • 1. Re: Specify delimiter for importing text files?
          BowdenData

          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.

           

          Doug

          • 2. Re: Specify delimiter for importing text files?
            mikebeargie

            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.

            • 3. Re: Specify delimiter for importing text files?
              beverly

              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!

              beverly

              • 4. Re: Specify delimiter for importing text files?

                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.

                 

                Thanks Mike!

                 

                - m

                • 5. Re: Specify delimiter for importing text files?

                  Maybe in 15....

                   

                  Thanks Bev.

                   

                  - m

                  • 6. Re: Specify delimiter for importing text files?
                    vincedubeau

                    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.

                    • 7. Re: Specify delimiter for importing text files?
                      beverly

                      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 --

                      Beverly Voth

                      --

                      • 8. Re: Specify delimiter for importing text files?
                        taylorsharpe

                        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. 

                        • 9. Re: Specify delimiter for importing text files?

                          Hey Taylor,

                           

                          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.

                           

                          Thanks again,

                           

                          - m