7 Replies Latest reply on Mar 31, 2011 11:09 AM by philmodjunk

    Recursive function help, CSV import

    BobSchwenkler

      Title

      Recursive function help, CSV import

      Post

      Hi,

      I have not gotten into recursive functions at all yet and it seems like this is what's needed to do what I want to do, so I'm looking for a bit of help to get started on a problem at hand.

      I have a Quickbooks report that I'm creating an FM import script for. The report needs to be pivoted to get data where it ought to be. Unfortunately, it has about 400 something rows in it, too many for Excel to open, so I'm scripting an import and pivot for the CSV data in FM. Before I get started, if anyone has any other ideas for pivoting this information, I'm open to options, though it would be nice to keep the user's required interaction to a minimum.

      My only hangup right now is that some of the item names have commas in them which is screwing up my delimiting. Basically, I want to substitute any commas in the item names into some other character, or to simply remove them. Here's some sample text:

      "Blow, The - Other","KLP052 -  The Normal Years","KLP086 - Introducing . . .",

      Thanks in advance.

        • 1. Re: Recursive function help, CSV import
          philmodjunk

          I don't see any need for a recursive function here. What role would that play?

          How are the commas causing problems here?

          As I understand your post, you are importing data from QuickBooks into FileMaker inorder to do a "pivot" table report--I'd guess that's what we also call a "cross tab" report where rows of data become columns of data in a grid where the intersection of a column and a row summarizes data matching both the row and column headers. A CSV import where the commas are enclosed in quotes shouldn't result in the data being incorrectly broken up into separate fields as I understand how CSV imports work. It's the commas that are outside the quotes that get used as field delimitters.

          • 2. Re: Recursive function help, CSV import
            BobSchwenkler

            I think this is all correct. Your description of a cross tab report is making me unsure, but I'm also pretty sure that's what I mean. I basically want to turn rows into columns and columns into rows. If it were a visual display in Excel, to rotate all of my information 90 degrees clockwise.

            The thing is here, that I'm pasting the CSV file's text into a global field and then "manually" sorting it from there. Reason for this is, as mentioned, the CSV file has ~450 rows (fields) in it. I could import the file directly into FM using "Import Records", but I'd need to create ~450 fields and then make sure that the field mapping is correct, etc.

            My report has numerous instances of commas within fields. If I'm depending on the commas to separate my information as my script is currently set up, these numerous instances will be acting as false delimiters.

            Creating a function that can go in and, for every instance of a comma enclosed in quotes, replace these commas with another character seems like it would need a looping or recursive function. Am I correct here?

            • 3. Re: Recursive function help, CSV import
              philmodjunk

              In fileMaker, Rows = records and 450+ records is small potatoes for Filemaker to deal with. Perhaps you meant 450 columns? (Columns are vertical, rows are horizontal, each column imports into a separate field, each row is a new record.)

              Yes, a recursive function or looping script could be used for that, but I remain unconvinced that you need to do that here.

              Even 450 fields isn't an insurmountable number of fields to work with, though it is a lot of fields.

              Just rotating the columns to become rows isnt' a cross tab report so scratch that idea. I've always understood pivot tables as doing more than a simple matrix transposition of rows for columns, but that could simply be my misunderstanding here.

              You can definitely set up a script that uses go to next record to step through the records and go to next field to move to the next column of data inorder to transpose the data into a new table. Reporting correct Field Names for each row may be a bit of a challenge, but not much as you can load a secondary table with the field names so that your report can use the data in that table for column headers in order to name the fields.

              • 4. Re: Recursive function help, CSV import
                BobSchwenkler

                I meant columns/fields, sorry.

                Perhaps a pivot table is not what I want to do. Rows to columns and vice versa is.

                The suggestion in the last paragraph is fine, but I first need to get the raw data organized into records and fields.

                I originally thought to use "Import Records" but if the table has any fields outside of the ones used strictly for the incoming data (serial numbers, etc), mapping becomes a problem. Thinking it over more, and with what you're telling me, I think I'll try creating a table who's only data will be the imported data. Letting FM take care of the CSV importing/delimiting makes more sense at this point. I'm not sure why it didn't before.

                I'll work from this angle, I'm sure I can handle it from there.

                For my own edification though, would you be willing to show me what the function previously in question might look like?

                • 5. Re: Recursive function help, CSV import
                  philmodjunk

                  This requires FileMaker Advanced as it is a custom function:

                  //QuotedCommaSub ( Text ; SubChar ; InQuote )
                  // Text : Text containing quotes and commas
                  // SubChar : Character to be substituted in place of the any commas inside quotes.
                  //InQuote : boolean flag used within the recursion to tell if comma is inside a quote or not

                  Let ( [ LeftChar = Substitute ( Left ( Text ; 1 ) ; ["“" ; "\""] ; ["”" ; "\""] ) ; // sub straight quotes for curly ones
                            Remainder = Right ( Text ; Length ( Text ) - 1 ) ] ;
                            case ( IsEmpty ( LeftChar ) ; "" ;
                                     LeftChar = "\"" ; LeftChar & QuotedCommaSub ( Remainder ; SubChar ; Not InQuote ) ;
                                     LeftChar = "," and InQuote ; SubChar & QuotedCommaSub ( Remainder ; SubChar ; InQuote ) ;
                                     /* Else */ LeftChar &  & QuotedCommaSub ( Remainder ; SubChar ; InQuote )
                                   ) // end case
                        ) // end let

                  I haven't tested this, but it should allow you to use QuotedCommaSub ( TextField ; "*" ; False ) to replace all commas inside of quotes with the * character and turn any curly quotes into straight quotes. (a very small change could keep the curly quotes as curly quotes if you really needed to.)

                  • 6. Re: Recursive function help, CSV import
                    BobSchwenkler

                    Thanks. So I did a little looking up on recursive functions and it seems like Case is what they are based off of. If the test calculation is not met it will continue looping through its respective result calculation until it is met. Am I correct here?

                    • 7. Re: Recursive function help, CSV import
                      philmodjunk

                      You need some kind of control to end the recursion so that it can end and return a value just like you need an exit Loop step to exit a loop so that the loop doesn't "loop" forever. Case is the most commonly used function as most recursive functions need multiple tests to handle all possible situations. In theory, you could use an If function, but it would limit you to only two possible outcomes from a single boolean expression.

                      Keep in mind that custom functions are not scripts and you cannot include script steps in them. They must be written as a calculation exprssion combining function calls with various operators to return a single value as its result.