8 Replies Latest reply on Jun 22, 2014 6:04 PM by philmodjunk

    CSV export with custom headings

    EvanStein

      Title

      CSV export with custom headings

      Post

           Hello everyone.  I have a unique challenge that I'm hoping you can help me solve.

           I am using FMP and FM Server to host an inventory management database.  Every 30 minutes FM Server runs a script that generates an export CSV file and a CRON job on the server uploads that file to several websites that list our current inventory for sale. The current routine is very script heavy and parses several tables to gather data and dump it all into an Export table before spitting out the CSV file.

           Long story short, that system isn't going to work for us anymore because we're adding some different types of items to our inventory.  I've managed to write a script that exports all of our new data into a perfect CSV file, the only problem is that the first record needs to have specific headings.

           To make matters worse, each website we work with wants these headers to be slightly different (some companies want "items" some say "products").

           I've tried several solutions that haven't really worked.  The trick is that this file gets generated every 30 minutes, so the solution has to be automated and self contained.

           Any help is greatly appreciated, and thank you all in advance!

        • 1. Re: CSV export with custom headings
          Markus Schneider

               You can have a separate record containing the desired data (filled in by script before the export). Using a 'sort-key' field and a sort-command will place that record as first record before export.

               The export script has to make sure that this record is present (if not, the script can create it again)

                

               You can also have a separate 'virtual' table, records to be exported have to be imported first into that table - with a 'header record' as the first record

          • 2. Re: CSV export with custom headings
            EvanStein

                 So the issue here is this:  In our inventory database we track exactly what items we have in stock.  Ie. if we have 10 Item A's and 5 Item B's, than the inventory table would have 15 records.  When we go to export, the export file only has 2 records:

            Product   Qty.
                 Item A     10
                 Item B     5

                 The report that I've been developing has a lot of summary fields that I'm not sure how to dump into an export table without writing a MONSTER script with several layers of loop routines.  I'm dreading that solution.

            • 3. Re: CSV export with custom headings
              philmodjunk

                   When exporting data, there's a "group by" option that allows you to export what is effectively just the summary layout part rows of a summary report--one row for each sorted group of records.

              • 4. Re: CSV export with custom headings
                EvanStein

                     Yes!  And this is my ideal scenario, but the headers that are exported in this scenario won't work.  One of the fields that we're exporting "TotalCountSum" gets changed to "TotalCountSum by Section" when I group the export by section.  The exchanges that we're uploading this inventory to want this column to be labeled "Qty".

                     I'm happy to change the field names so that the headers export properly, but when I use the Group By feature the heading names are uncontrollable.

                • 5. Re: CSV export with custom headings
                  philmodjunk

                       But the same basic method can be used to generate records in an export table and then you only need add a "header" record to make up that initial row of your csv file.

                  • 6. Re: CSV export with custom headings
                    EvanStein

                         Very fair point.  I'll go down that road and see where it leads me.  Thank you for the suggestion!

                    • 7. Re: CSV export with custom headings
                      EvanStein

                           I've gone down that road and seem to be facing an export obstacle.

                           I've created a table called "Export" that my Inventory table dumps into  My script inserts the header rows at the top of that table: so far so good.  The Export table has a field called "Quantity" which is intended to display (and export) a count of the total number of records when sorted by the field "Row". This is where things fall apart.

                           In order for the field Quantity to calculate properly, Quantity needs to be a Summary field, which precludes me from adding the field header in the first record.  And if I allow the export process to use the field name as the column header, the Quantity header gets exported as "Quantity by Row".

                           Any new ideas?  As always, any and all input is greatly appreciated!

                      • 8. Re: CSV export with custom headings
                        philmodjunk

                             Isn't Quantity a different total for each summary group of records? If so, the function GetSummary can use a summary field and a "break" field to compute that sub total to set the value in the field for a single  record.

                             And one rather odd approach to this method is to export the "grouped" data to a text file, then import it back into a FileMaker table in order to combine it with your special header record.