6 Replies Latest reply on Jun 9, 2017 1:49 PM by fmpdude

    How to parse text into fields and records

    pcand01

      Hello,

      I am new here and I need some pointers as where can I find examples to parse and format this type of text into fields and records.

      In need to extract the Date, D0, D1, Value column to build a table. The number of records can vary, the columns are fixed.
      Thanks in advance for ideas and or links to similar examples.

       

      "CubeId";"devkum"

       

      "PublishingDate";"2017-06-01 14:30"

       

      "Date";"D0";"D1";"Value"

       

      "2017-04";"M0";"EUR1";"1.0723"

      "2017-04";"M0";"GBP1";"1.2637"

      "2017-04";"M0";"DKK100";"14.4175"

      "2017-04";"M0";"NOK100";"11.67"

      "2017-04";"M0";"CZK100";"3.9959"

      "2017-04";"M1";"CZK100";"3.9801"

        • 1. Re: How to parse text into fields and records
          erolst

          pcand01 wrote:

          "Date";"D0";"D1";"Value"

           

          "2017-04";"M0";"EUR1";"1.0723"

          "2017-04";"M0";"GBP1";"1.2637"

          "2017-04";"M0";"DKK100";"14.4175"

          "2017-04";"M0";"NOK100";"11.67"

          "2017-04";"M0";"CZK100";"3.9959"

          "2017-04";"M1";"CZK100";"3.9801"

          Is that the data to be parsed?

           

          Here's a rough parsing example:

           

          Set Variable [ $theList ; Table::yourField ]

          Set Variable [ $theCount ; ValueCount ( $theList ) ]

          Loop

            Exit Loop if [ Let ( $i = $i + 1 ; $i > $theCount ) ]

            Set Variable [ $theRecord ; GetValue ( theList ; $i ) ]

            Set Variable [ $recordAsList ; Substitute ( $theRecord ; [ "\";\"" ; Char(13) ] ; [ "\"" ; "" ] ) ]

            New Record/Request

            Set Field [ Table::theDate ; GetValue ( $recordAsList ; 1 ) ]

            Set Field [ Table::D0 ; GetValue ( $recordAsList ; 2 ) ]

            ...

          End Loop

           

          Of course, "2017-04" is not a valid date, so that sub-string would need to be parsed again to set a date.

           

          OTOH, this looks almost like CSV, so substituting the semicolons for commas, exporting the field as text and importing it into new records might be a faster way ... then taking care of the date issue en bloc ...)

          • 2. Re: How to parse text into fields and records
            fmpdude

            Very similar to what erolst said, if this is a text file, why not (FMP way): import it, change the ";" to commas (substitute function), export it to another file, then re-import that exported file into a table into respective fields?

             

            ---------

             

            Or, (non-FMP way: pre-process the file) you could write an external script in Python (or just about any language) to change semi-colons to commas, THEN import that file into FMP. (that's how I would do it). If this were a regularly occurring task you could even schedule that transformation.

             

            Here's a Java 8 way, just a few lines of code, to read your file, transforming it (; -> ,) and then write out a new file:

             

            List<String> lines = Files.readAllLines(inputFilePath).stream().

                            parallel().

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

                            .collect(Collectors.toList());

             

            Files.write(outFilePath, Arrays.toString(lines.toArray()).getBytes());

             

            ["Date","D0","D1","Value", "2017-04","M0","EUR1","1.0723", "2017-04","M0","GBP1","1.2637", "2017-04","M0","DKK100","14.4175", "2017-04","M0","NOK100","11.67", "2017-04","M0","CZK100","3.9959", "2017-04","M1","CZK100","3.9801"]

             

            (I can give you more details on this if you're interested.)

             

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

             

            Note that the Java 8 way is "declarative" - like "SQL" you don't "write (imperative) code". Rather, you specify what (not "how") to do what you want (declare) and let Java figure out "how" (behind the scenes) to do it. .NET and other modern development languages/environments have similar constructs.

             

            Note: This Java code uses "parallel" so the code will be extremely fast as it will use all the available processor cores. Caution: make sure that's OK with your current configuration!

            • 3. Re: How to parse text into fields and records
              pcand01

              Guys, thanks for the great answers. I import this data from the Swiss National Bank API using Insert from URL. I format my URL string to get the data I need and I am supposed to get a CSV Output.

              This thing will need to work within a runtime solution. I do not want to use the file system at all, I will parse this within FileMaker. I will use a variable I think and since this process takes place once a month to get the D0 average rate and D1 closing rate, I am not concerned about performance.

              The idea of transforming the data is something I like but again I would constrain myself in a Runtime self contained mode.

              • 4. Re: How to parse text into fields and records
                fmpdude

                Sounds good, but even if you're not concerned about performance, I would caution you that loops in FIleMaker can be absolutely pig-slow if you have very much data at all.

                 

                As a test, I duplicated your CSV file so it had 200,000 rows and I ran the Java code posted above.

                 

                How long did it take to:

                 

                1. Read the file

                2. Replace all ";" with ","

                3. Write the brand new updated file?

                 

                2 seconds.

                 

                So remember if your FMP script loops are super slow, as I would expect them to be with very much data, you have other options before the import.

                 

                Additionally, with a little more code, you could automate reading the CSV file AND populating your FMP database fields with INSERT SQL statements.

                 

                Good luck!

                • 5. Re: How to parse text into fields and records
                  pcand01

                  I am definitely a SQL/ETL guy. I guess I will spend the time to make it nice.
                  It goes against my grain to rebuild that table each time. I want an SCD type 2 working fast of course

                  • 6. Re: How to parse text into fields and records
                    fmpdude

                    I would just automated it and forget it, if possible.