1 2 Previous Next 23 Replies Latest reply on Jul 11, 2017 5:26 AM by beverly

    Export question

    washutech

      Please be kind! I have an export button that will export out data from a search to a CSV file. When it opens in Excel, it puts all of the data in one column. How do I fix it to where it will open up with the data in separate columns? Here is the script

       

      Set Variable [$path; Value Get ( DesktopPath ) & "AgentSearchResult.csv"

      Export Field Contents [Main::gAgentList; "$path"; Automatically open]

       

      Exit Script [Text Result: ]

        • 1. Re: Export question
          Jens Teich

          Use mergemail (.mer) instead of csv

          • 2. Re: Export question
            TomHays

            Use Export Records instead of Export Field Contents.

             

            Export Field Contents will export to a file only one field on one record.

            Export Records will export any number of fields from all records in the Found Set.

             

            When you export using Merge format (.mer), export to a filename ending with ".csv" to avoid confusing Excel.

             

            -Tom

            • 3. Re: Export question
              washutech

              So, I need to correct Export Field Contents to Export Contents? Correct. I have not worked on our FileMaker in a few years.

              • 4. Re: Export question
                beverly

                Your single field with Export Field Contents may not be the proper format for .CSV. And it may be but we cannot see your script/calcs to get the content.

                Also FM version with OS platform may have different answers.

                 

                See if this article (and the prior one linked) helps:

                 

                Beverly

                Sent from miPhone

                1 of 1 people found this helpful
                • 5. Re: Export question
                  rouelf

                  This is confusing !!!

                  To Export Field Contents , or Exports Records, depends on whether the data is all in the field and properly formatted for csv; or the data is in a table with columns and rows.

                   

                  If the data in the field looks like e.g.:

                   

                  10,37.433128,-122.097406,0,0,2.64,1

                  25,37.433117,-122.097399,0,0,3.62,2

                  30,37.433036,-122.097455,.01,7.2,-.58,3

                  35,37.432951,-122.097468,.02,7.2,-10.79,4

                  40,37.43287,-122.09747,.03,7.2,-10.26,5

                  45,37.432795,-122.097477,.04,7.2,-9.68,6

                  51,37.432713,-122.097504,.05,6,-9.28,7

                   

                  Then the Export Field Contents as the OP is doing would work correctly. If not, then the data in the field is not formatted correctly as csv.

                   

                  washutech, please show what is in the Field, Main::gAgentList

                  • 6. Re: Export question
                    washutech

                    Here are the fields that are exported:

                    LastName, FirstName, Distance, Phone, Cell, Email, City, State, InsuranceCompany, CertYear, Status, Upline, AgentStatus

                    They use this to pull data from an area, an example would be if HealthSpring had a training in a certain area we could pull a list of agents that are around that city or zip and let them know about the needed training.

                    • 7. Re: Export question
                      washutech

                      Just upgraded from FM Server 12 to FM Server 16. Mac OS Sierra is what it is running on.

                      • 8. Re: Export question
                        rouelf

                        As was asked, please show what is in the Field, Main::gAgentList.  That means copy and paste here what is in the field Main::gAgentList, a few lines will do, is it formatted as csv ?

                        • 9. Re: Export question
                          washutech

                          Yes, it is formatted as a CSV, but when you open it the data is in just one column.

                          LastName, FirstName, Distance, Phone, Cell, Email, City, State, InsuranceCompany, CertYear, Status, Upline, AgentStatus

                          Adesakin, Babatunde, 2, 832-896-0879, , adesakin@sbcglobal.net, Houston, TX, Cigna HS - Field, , Inactive, TSS, Active
                          Adesakin, Babatunde, 2, 832-896-0879, , adesakin@sbcglobal.net, Houston, TX, Cigna HS - Field, , Inactive, TSS, Active
                          Adesakin, Rekiah T., 4.3, 832-212-2227, , , Houston, TX, Cigna HS - Field, , Inactive, TSS, Inactive
                          • 10. Re: Export question
                            washutech

                            Also, I am not a programmer. Our last FM programmer passed away, I am trying to explain as best as possible. I know some of the basics, and I am trying to learn as I go.

                            • 11. Re: Export question
                              rouelf

                              Thanks, I have copied your text, and pasted in a text field. Then use this script, which is the same as yours:

                               

                              Set Variable [ $Path; Value:Get ( DesktopPath ) & "Test.csv" ]

                              Export Field Contents [ Import CSV::Edit_Text; “$Path”; Automatically open ]

                               

                               

                              and in my Mac, Numbers (Apple's spreadsheet) opens it correctly formatted.

                               

                              OK, I see that Excel does not open it correctly. Let me do some more checks, Stay tuned !!

                              • 12. Re: Export question
                                washutech

                                Ok, so it is something wrong on my end? The managers that use this feature run Windows 7 Pro with Office 2007

                                • 13. Re: Export question
                                  rouelf

                                  Nothing wrong your end. In the Mac (my Mac) the: Export Field Contents [ Import CSV::Edit_Text; “$Path”; Automatically open ]

                                  seems to export as Unicode (UTF-16 Little-Endian. And apparently Excel does not like that. I changed / saved with the coding format to UTF-8, using a word processor, I used TextWrangler, Then Excel opened it correctly. Also open the csv exported file with Numbers, then export to Excel (.xlsx) and of course it opens correctly in Excel.

                                  • 14. Re: Export question
                                    beverly

                                    See the article on how to get the correct end-of-line.

                                     

                                    Also, should the CSV have double quotes around the text values?

                                     

                                    Sent from miPhone

                                    1 2 Previous Next