1 2 3 Previous Next 82 Replies Latest reply on Feb 11, 2016 5:34 AM by beverly

    How to change exported files' column headings

    petermontague1

      I want to export field contents as an excel file, save it as a .txt file and then feed it to Amazon and other websites. Some of the field names don't match what some of the websites need as their column headings. So every time I export I have to change the names of each of the columns. This is laborious. Surely there is a better way. Is there a way that I can get filemaker pro to put something other than the field names as column headings into the excel export file?

        • 1. Re: How to change exported files' column headings
          usbc

          Hi,

          Seemingly you do this often enough to warrant some effort.

          You could consider a shadow table where in the fields are calculations. So let's say you have a Amazon table with a field named "Customer First Name" which would be calculated to equal "b_First" in your normal data base table. Etc., etc. Then just create the Excel file from the Amazon table. Naturally, I don't know how many fields you are dealing with and someone else may have another idea. However, I don't know of any way to change the field names during the output step such that you wouldn't have to open and touch the Excel file every time.

           

          HTH

          • 2. Re: How to change exported files' column headings
            comment

            petermontague1 wrote:

             

            I want to export field contents as an excel file, save it as a .txt file and then feed it to Amazon and other websites.

             

            What's the point of going through Excel? And what exactly is "a .txt file"? What format does it use?

            • 3. Re: How to change exported files' column headings
              petermontague1

              Amazon and the other websites to whom I feed these reports to reports stipulate tab delimited text files. I can save it as a tab delimited file from excel. If there way I could do this and avoid losing the integrity of the data I'd love to know because this method is a bit cumbersome.

              • 4. Re: How to change exported files' column headings
                petermontague1

                Good idea. I'll go with that as a last resort. Calculations for so many fields could slow down the database.

                • 5. Re: How to change exported files' column headings
                  comment

                  petermontague1 wrote:

                   

                  Amazon and the other websites to whom I feed these reports to reports stipulate tab delimited text files. I can save it as a tab delimited file from excel.

                   

                  You could do one of the following:

                   

                  1. Export your data directly to a tab delimited file. Then use either OS-scripting or a plugin to insert a header line into the exported file.

                   

                  2. Export as XML, using a custom XSLT stylesheet to build the file you need - see, for example:

                  http://fmforums.com/forum/topic/84008-adding-first-row-with-field-names-to-exported-text-file/

                  • 6. Re: How to change exported files' column headings
                    petermontague1

                    Option 2 looks good. I see the list of column headings in the style sheet. Is that the only thing I'd need to adapt? How does that list of headings match up with the correct columns? Would the script fail if the field export order was changed?

                    • 7. Re: How to change exported files' column headings
                      comment

                      petermontague1 wrote:

                       

                      How does that list of headings match up with the correct columns?

                       

                      You just need to make sure the number of headings matches the number of exported fields.

                       

                       

                      petermontague1 wrote:


                      Would the script fail if the field export order was changed?

                       

                      Not really. It just places a tab after each field in a record, except the last one. But if you change the field export order, you should also change the heading order to match.

                      • 8. Re: How to change exported files' column headings
                        datastride

                        Peter,

                         

                        I do quite a bit of exporting to tab-delimited files... and I take a slightly different approach.

                         

                        I just write a script to collect all the data into a single variable, and the use a plug-in like Troi File to write the contents to a flat file with an extension of ".tab".

                         

                        The script that collects the data first loads all the headings in the variable:

                         

                        Set Variable $Data = "Heading 1" & Char( 9 ) & "Heading 2" & Char( 9 ) & "Heading 4" & Char( 13 ) & Char( 10 )

                         

                        The "Char( 9 )" inserts a tab and the "Char( 13 ) & Char( 10 )" inserts a carriage return and a line feed to signal end of record.

                         

                        Then the script loops thru the data records, appending the appropriate fields from each record:

                         

                        Go To Record/Request First

                        Loop

                          Set Variable $Data = $Data & MyTable::Field1 & Char ( 9 ) & MyTable::Field2 & Char( 9 ) & MyTable::Field3 & Char( 9 ) & MyTable::Field4 & Char( 13 ) & Char( 10 )

                          Go To Next Record; Exit After Last

                        End Loop

                         

                        After collecting all the data, my script calls two Troi File functions to write the file to disk:

                         

                        $FilePath = "C:/MyFile.tab"

                        Set Variable $Result = TrFile_SetDefaultFileSpec( "" , $FilePath )

                        Set Variable $Result = TrFile_SetContents( "" , $Data )

                         

                        Just another option ... The downside is it relies on a plug-in (not so bad, really), but the upside is the process is very simple, very flexible, and very reliable.

                         

                        HTH ...

                        • 9. Re: How to change exported files' column headings
                          beverly

                          This was the alternate method I was thinking about. The advantage is that multiple headers can be created and a case statement used to select the one needed. The OP said that there were multiple exports all to different places with different headers.

                           

                          -- sent from my iPhone4 --

                          Beverly Voth

                          --

                          • 10. Re: How to change exported files' column headings
                            comment

                            Beverly Voth wrote:

                             

                            This was the alternate method I was thinking about. The advantage is that multiple headers can be created and a case statement used to select the one needed.

                             

                            You can do the same thing with XML/XSLT, either by preparing different stylesheets and having the script select one, or by exporting a global field to tell the stylesheet which header to use.

                            • 11. Re: How to change exported files' column headings
                              beverly

                              Absolutely, Michael!

                               

                              -- sent from my iPhone4 --

                              Beverly Voth

                              --

                              • 12. Re: How to change exported files' column headings
                                petermontague1

                                Happy new year. I'm interested in using your converting my exporting as XML. I'm new to this. Will the XML method change it automatically to .txt? If not is it easy to change to .txt? Also is it possible to encode certain fields in ASCII?

                                • 13. Re: How to change exported files' column headings
                                  beverly

                                  Peter, the "method" for xml transformation by XSLT can be to: xml (another schema), text, or hmtl. Each of these gives a slight different export format. You can specify whatever extension you choose (.txt) when you create the XML export (manually or by script).

                                       The "encode in ASCII"? do you mean you need something other than UTF-8 encoding in the file, then the answer is yes. Please let us know what you need. Do you have the fields formatted a certain way?

                                  Beverly

                                  • 14. Re: How to change exported files' column headings
                                    petermontague1

                                    Hi Beverly, thanks for your speedy reply. Some of the fields are sent to web pages and I noticed that apostrophes are translated into their ASCII alternative when they are live on the web pages.

                                    I also downloaded some of my content from the source code of some web pages. This is already ASCII encoded.

                                    1 2 3 Previous Next