9 Replies Latest reply on Sep 14, 2016 10:35 AM by fmpdude

    Import txt with fields separated by ";"

    pontlem.75

      Hi, I'd like to import a .txt file in Fm, but the fields are separated by the character ; while Fm just import fields separated by , and tabs . I'm looking for a solution inside FileMaker to deal with normalizing the data.

       

      Sorry, I'm quite a newbie, I look for an answer in the forum but I didn't find it. Thank you in advance.

        • 1. Re: Import txt with fields separated by ";"
          keywords

          If you are confident the set of values are consistently separated by semi-colons and that these are not used as normal punctuation, then you could try importing all the text into a single field created for that purpose (call it importTEXT or something similar), then parse it via a calculation or a script from there into the separate fields—all text up to the first semi-colon into one field; between the first and second semi-colon into the next field; and so on.

          1 of 1 people found this helpful
          • 2. Re: Import txt with fields separated by ";"
            pontlem.75

            Thank you very much. Though it seems a little bit too difficult for my actual skills, but I'll study. Any link with tutorials or useful material would be appreciated.

            • 3. Re: Import txt with fields separated by ";"
              Johan Hedman

              Import them into Excel first and Save your new file. Then import your Excel-file into FMP

              1 of 1 people found this helpful
              • 4. Re: Import txt with fields separated by ";"
                fmpdude

                Is it possible to ask the person who created the file to change ";" to "," or "<TAB>"? That would be the best solution since you wouldn't have to implement a workaround.

                 

                (I would then try the Excel recommendation above.)

                 

                -------

                 

                Some database programs (I wish FM were one of them) allow you to specify a field delimiter so you don't have to spend time (hours, ...) working around that sorely missing feature. This missing feature has been discussed before.

                 

                Alternatively, if you don't feel like writing a script to just import your data, you could import it into one of those other programs (like Navicat, for example), and then export it to a text file, then import it into FileMaker. A few steps, but if you only need to do the import once or twice it's not a big deal.

                Since FileMaker's scripts are so painfully slow (and the script workspace in general is, IMHO, painful), I prefer to handle files like this using an external Java program for anything that takes any serious programming and save FileMaker for its strengths - which are plentiful.

                 

                If I were to do this import in FileMaker, (Note: FileMaker also lacks basic low-level functions to read and process external disk files line by line), I would read the file into a FileMaker table where each line of the file is on a separate row (this still gets the job done). Then, in FileMaker, however, I would use the Position function to find the position of the ";" and use that with functions like LEFT and MIDDLE.

                 

                HOPE THIS HELPS.

                • 5. Re: Import txt with fields separated by ";"
                  pontlem.75

                  Thank you, Johan and Fmpdude, but I should import the file at least once per week. I can't ask to modify the file. It is the price list of a big company.

                  As to create the script you advised me seems a little bit complicated (but I'll try to study), I'm trying to import the txt in excel with Automator. Though, I had some problems with reliability. In the past sometimes it didn't work anymore without I could understand why. Maybe it was just my fault.

                  A friend told me about Kettle, but iI wasn't able to install it. Is there any other software that can support automatic data integration?

                   

                  Thanks for your fast answers

                   

                  • 6. Re: Import txt with fields separated by ";"
                    fmpdude

                    Yes, I've had issues with Excel as well. You might also try the open source and free LibreOffice.

                     

                    Hmmmm.....OK, what I would do in your case, is write a small Java program that I could schedule at the OS level (Cron on Mac, Schedule Task on Windows). That program would run, say every day at "the time you picked" or at different times if needed. That program would simply take your ";" file and create a "," delimited file. Then, I would use a Hazel rule (a must-have utility on the mac) to move that file to another folder and possibly rename it once the translation was done. That moved file might also then be a flag to the java program that there is "nothing to do" at present.

                     

                    If you don't need to "automate" the task, so much the simpler. Just run the external program on the file when needed.

                     

                    Then, FileMaker will be happy and can import the file. Hopefully, FM will give the option to specify a delimiter. A sorely missing feature in my view.

                     

                    ---

                     

                    It may sound like a lot of work, or even complicated to an extent, but it's really not difficult. Disk file manipulation is simple with Java or with most real programming languages.

                     

                    ----------------

                    Pseudo code:

                    ----------------

                    Open file      (requires disk file functions found in a real programming language like Java, C, C#, etc.).

                    Read a line of the file into variable

                    For each ";" found in variable in file, change to ","

                    When file done, save file to new file name

                    exit

                    ---

                    (easy!)

                     

                    If you decide to try this route, I'll be glad to help if I can.

                     

                    HOPE THIS HELPS.

                    • 7. Re: Import txt with fields separated by ";"
                      pontlem.75

                      I'd love to learn Java and this seems an easy first step to begin! Actually, I'm managing several files to import in Fm, some of them need me to do small jobs by myself and this makes me waste a lot of time. Furthermore, to learn how to create automatic workflows will improve a lot my company daily activities.

                      I suppose I should do some online course, maybe, or try to begin with some easy script like the one you advised me.

                      • 8. Re: Import txt with fields separated by ";"
                        fmpdude

                        The good news is that in Java 8, this file transformation really only takes one line of code! With Java 8, you can do a lot of programming "declaratively" -- meaning you don't have to implement the "how" the code works but rather "what" it needs to do (similar to how SQL works, for example)

                         

                        Below is a code segment that will write the output file. You still need to create a little boilerplate but since Java is the #1 programming language (has been for years), it's easy to find examples, books, tutorials, etc., everywhere.

                         

                        // define simple PrintWriter file for text output

                        PrintWriter outputFile = new PrintWriter(OUTPUT_FILE_NAME, "UTF-8");

                         

                        // use Java 8 cool stuff!

                        Files.lines(new File(INPUT_FILE_NAME).toPath())

                          .map(s -> s.replace(";", ","))

                          .filter(s -> !s.isEmpty())

                          .forEach(outputFile::println);

                         

                        outputFile.close();

                         

                        ------

                         

                        Input File Is:

                        Text Here ; 123.34; 123.33

                        More text ; 90.31; 34

                         

                        Transformed and written Output File is:

                        Text Here , 123.34, 123.33

                        More text , 90.31, 34

                         

                        --

                         

                        And, best of all, this is F_A_S_T!

                         

                        (You can do pre-Java 8 file stuff too and it's not difficult either. TONS OF EXAMPLES OUT THERE)

                         

                        If the file is on an FTP site where this semi-colon-delimited file resides, for example, you could use a tool called "ExpanDrive" to create a local drive mapping your program can access. Very cool program:

                         

                        ExpanDrive for Mac & Windows | SFTP, Dropbox, Google Drive, S3, OneDrive, Box.com, WebDAV, Swift as Network Drive

                         

                        HOPE THIS HELPS.

                        • 9. Re: Import txt with fields separated by ";"
                          fmpdude

                          Just a quick follow up, I did a test using the Java code one-liner I posted above with 500,000 records in the input text file. Java took 1 second to create the output file!

                           

                          Input: Here's a sample of the input file with random names and meaningless data:

                           

                          "fname";"last";"addr";"City";"State";"zip4";"AreaCode";"PHONEPRFIX";"4digits";"Numbers";"LastContact"

                          Brandee   ;Leavitt   ;50 Tunlaw ;Ramsey    ;NJ        ;26230-4024;       420;       329;         3;       300;5/21/2015

                          Lindy     ;Wisbey    ;241 Scherm;Bonaparte ;SD        ;67073-5343;       211;       392;         6;       300;8/1/2008 

                          Vella     ;Schriber  ;278 Heron ;Haiku     ;IL        ;54426-6322;       201;       811;         4;       500;4/18/2013

                          Alyson    ;McLeoud   ;5 Temple C;Mooreton  ;NJ        ;70542-7774;       101;       862;         8;       400;8/3/2007 

                          Wm        ;Mantey    ;7749 E Pla;Cool      ;MO        ;90720-9084;       238;       830;         7;       500;4/13/2011

                          Margaret  ;Sprong    ;797 N Zube;Phoenix   ;IA        ;52209-9654;       183;       918;         4;       500;1/21/2012

                           

                          -------

                           

                          Output: Here's the same file with the expected comma separated variables:

                           

                          "fname","last","addr","City","State","zip4","AreaCode","PHONEPRFIX","4digits","Numbers","LastContact"

                          Brandee   ,Leavitt   ,50 Tunlaw ,Ramsey    ,NJ        ,26230-4024,       420,       329,         3,       300,5/21/2015

                          Lindy     ,Wisbey    ,241 Scherm,Bonaparte ,SD        ,67073-5343,       211,       392,         6,       300,8/1/2008 

                          Vella     ,Schriber  ,278 Heron ,Haiku     ,IL        ,54426-6322,       201,       811,         4,       500,4/18/2013

                          Alyson    ,McLeoud   ,5 Temple C,Mooreton  ,NJ        ,70542-7774,       101,       862,         8,       400,8/3/2007 

                          Wm        ,Mantey    ,7749 E Pla,Cool      ,MO        ,90720-9084,       238,       830,         7,       500,4/13/2011

                          Margaret  ,Sprong    ,797 N Zube,Phoenix   ,IA        ,52209-9654,       183,       918,         4,       500,1/21/2012

                           

                          ----

                           

                          ONE SECOND TO RUN CODE ON 500,000 LINE TEXT FILE.

                           

                          HOPE THIS HELPS.