11 Replies Latest reply on Nov 28, 2009 1:43 PM by Sorbsbuster

    Giving the user a scipt that exports data to an Excel sheet

    ralvy

      Title

      Giving the user a scipt that exports data to an Excel sheet

      Post

      I have a client that would like to be able to export some data from his runtime solution to an Excel file. It looks like to do this I need to create a special table with field names he'll understand, and then have the script import the required data into that table, and then export it to an Excel sheet. Otherwise, I don't see how I can get the script to export field names that are user friendly. Is this the way it's usually done?

       

      What I was hoping to be able to do was actually have a pre-designed Excel sheet, complete with formulas, sitting in the solution's folder, and have the solution import the data into that sheet, but I don't see that I can even come close to that.

       

        • 2. Re: Giving the user a scipt that exports data to an Excel sheet
          ralvy
             Looks very promising. I'll report back on how it worked out. Thanks, as usual.
          • 3. Re: Giving the user a scipt that exports data to an Excel sheet
            ralvy
               Later ... looks like a lot of detailed work to make this happen with the XML approach I see in the suggested file. I think a utility table (suggested in my original post) will be a lot easier.
            • 4. Re: Giving the user a scipt that exports data to an Excel sheet
              comment_1
                

              Well, finger-counting is also a lot easier than learning the multiplication table... :smileywink:

              A utility table cannot provide formatting or formulas, and it's not easy to make it safe for use in a multi-user environment.

              Another option you might look into is automating things on the Excel side.

              • 5. Re: Giving the user a scipt that exports data to an Excel sheet
                ralvy
                   As it turns out, the client may just want the raw data in Excel format, and not care about formulas, etc. If that's not the case, I may just bite the bullet and tackle XML, or perhaps easier, look into Excel macros, which I also know absolutely nothing about.
                • 6. Re: Giving the user a scipt that exports data to an Excel sheet
                  TKnTexas
                     You can create a script that will EXPORT to a file type of Excel.  You select the fields you want to export in the order you want.  If you mark to open on Export it will open Excel and open the file for your client.  I do this routinely from an XP runtime.  From the child-table I select the records I want, but each line includes fields from the parent-table.  It will work very smoothly and it is easy to set up. 
                  • 7. Re: Giving the user a scipt that exports data to an Excel sheet
                    FentonJones
                       There is also the Save As Excel script step/command. We should be clear that it is not an "export" per se. It does not let you select the fields; it should saves out what is on the current layout, in the order it is on the layout (if a field is not lined up, it may not end up where you expect). If they only want the data, then it may be sufficient.
                    • 8. Re: Giving the user a scipt that exports data to an Excel sheet
                      ralvy
                        

                      TKnTexas wrote:
                      You can create a script that will EXPORT to a file type of Excel.  You select the fields you want to export in the order you want.  If you mark to open on Export it will open Excel and open the file for your client.  I do this routinely from an XP runtime.  From the child-table I select the records I want, but each line includes fields from the parent-table.  It will work very smoothly and it is easy to set up. 

                      This is essentially what I was figuring I had to do, but also figured I'd need to use a special table for this because the field names that would be exported will come from the database definition, and those currently are not user friendly names.


                      • 9. Re: Giving the user a scipt that exports data to an Excel sheet
                           For one of my customers I've added an extra table to the database in which i used userfriendlynames. This table is filled with the data in the background and is exported when my customer needs it. It is not the fastest method but my customer is happy with it ;-)
                        • 10. Re: Giving the user a scipt that exports data to an Excel sheet
                          wigz
                            

                          This may be a little simplistic (I am fairly new to FM and have not worked with runtime solutions yet) but I have found a workaround for a similar problem that I was faced with.

                           

                          I needed to use Excel to create graphs from information contained within my database - I use a script to export the data into an excel file (FM Export.xls) I have a separate spreadsheet file (FM Graphing.xls) which is linked to the FM Export file.  Every time I open the graphing file it looks at the latest FM Export file and gets the required data - it doesn't matter what the exported field names are because the graphing spreadsheet only looks for the data and enters it into the pre-defined graphing fields/columns.

                           

                          I am fortunate because the order and number of exported fields doesn't change hence this simplistic solution works well for me.

                           

                          Regards,

                           

                          Wigz

                           

                          MacBook Pro

                          FM Pro10 Advanced

                          Office Mac 2008

                          • 11. Re: Giving the user a scipt that exports data to an Excel sheet
                            Sorbsbuster
                              

                            Why not just:

                             

                            - create an Excel sheet with user-friendly column names as its first row.

                            - create a text export script from the FM file to export the fields you want, in the order you want.

                            - Specify the name and destination

                            - open the Excel file and select 'Import Data from Text File' (they keep moving around where that menu option is)

                            - find the text file you created.  Select cell A2 (say, - I mean start the import on the second row.)

                            - If you update the data, just click into the excel sheet data anywhere and select 'Refresh'

                             

                            -All of those steps can be made fully automatic:

                            - set FM to export the text file silently

                            - set the Excel sheet to refresh the data upon opening

                            - include as the last step in the FM file to open that Excel sheet.

                             

                            All data refreshed seamlessly, all column headers the user-friendly way you want.

                             

                            You can then create another worksheet in the excel file that re-presents that raw data in the way the customer wants to view the data - Pivot Table, Sub-summary, whatever.