13 Replies Latest reply on Nov 17, 2009 10:10 AM by mark_d2x

    Re calculation field

    mark_d2x

      Title

      Re calculation field

      Post

      Hi,

       

      I am using a calculation field to supposedly help when I export the file as excel for uploading as a .csv file.

       

      I have used the following calculation:

       

      "\"" & FIELD TITLE & "\""

       

      I then save records as Excel.  Open up the xls file in Excel to finish off the data everything looks fine e.g. "James" - but when I open in a text editor it is showing as """James""" etc

       

      If I repeat without the calculation field the xls file has James as does the csv opened with a text editor.

       

      What am I doing wrong?

       

      This is doing my head in!

        • 1. Re: Re calculation field
          david_lalonde@d-cogit.ca
            

          The CSV file format encodes quotes by escaping them because quotes are used to encapsulate values. The escape sequence for quotes is the doubling of quotes.

           

          Because your calculation actually adds quotes to the data, FileMaker Pro escapes the quotes, which are part of the data, to export to CSV. You do not see these extra quotes in Excel because Excel processes the escape sequence. A text editor does not process any text. A text editor will show you the raw record data: the non-data characters, the enclosing characters, and the data.

          • 2. Re: Re calculation field
            philmodjunk
               You might try Quote ( Field Title )
            • 3. Re: Re calculation field
              mark_d2x
                

              Hi David,

               

              I am really confused now.

               

              When I did the data upload last year I exported the field contents into excel then added a new column before the data and entered newpara, then after the last column i added endpara, then saved this as a .csv.

               

              I then opened up the csv in a text editor to replace

               

              newpara, with "  

              ,endpara with "

              , with ","

               

              This worked fine to do the encapsulation for uploading.

               

              This time, i thought I could do that with Filemaker so that the encapsulation was done hence the calculation field.

               

              If I save the records as excel (.xls) it looks correct as. xls, then when i save it as a csv it has the """ problem - the whole file is rejected because of this.  When i tried to export from FIlemaker as a csv it wouldn't let me export all the necessary fields ;-(

               

              In the first part of the upload, PATIENT there are about 16 fields - TUMOUR 50, TREATMENT 70 and FOLLOW UP 15 

               

              The data will only upload if it is encapsulated  within ""  

               

               

              ie "1234567890","Fred","Flintstone","1","01/01/2009",  etc

               

              I guess my question is how do I now export the data so that it is ready to upload?  Is there something fundamental I am doing wrong?

               

              n.b. some of the data is directly from the main table other data is from the lookup tables e.g. male = 1 female = 2 etc 

               

              Yours confused

               

              Mark 

               

               

               

               

              • 4. Re: Re calculation field
                mark_d2x
                  

                Hi Phil,

                 

                That is easier than the method i had previously used, I still have the problem of getting the data ready for uploading -  when i tried that it looks fine in excel however in text editor there is still the """ problem even if i export as a csv file ;-( 

                 

                 

                 

                • 5. Re: Re calculation field
                  david_lalonde@d-cogit.ca
                    
                  Hello Mark of London,
                   
                  (By the way, is it London, Ontario, Canada or London, capital of England?)
                   
                  I assure you that exporting a CSV from FileMaker Pro will encapsulate each field's data in between double-quote characters. I just verified this in FileMaker Pro 10 Advanced on Mac OS X. All other versions of FileMaker Pro on all platforms have had this behaviour as far as I can remember.
                   
                  On the other hand, I will not guarantee that Excel encapsulates all fields on export. It is quite possible that Excel only encapsulates what it considers text and not what it considers a number. I have seen other software do this.
                   
                  CSV is a loose standard, if a standard at all. It only states (or it is only understood) that values in a record be separated by a delimiter (any delimiter except the record delimiter), that records be separated by a new line or carriage return or both, and that any delimiter in the data be escaped by a consistent escape mechanism. The most common CSV implementation encapsulates values between quotes anytime the possibility of escaping exists and the field delimiter is a comma. Quotes are often used by the exporting software as a means of indicating to the importing software that the value is to be treated as a text value as opposed to a numerical value.
                   
                  I am curious to know which fields do not export as a CSV from FileMaker. All but container and summary fields are exportable.
                  • 6. Re: Re calculation field
                    mark_d2x
                      

                    Hi David,

                     

                    London UK - thats the easy bit! 

                     

                    The file was rejected because the receiving server  sees the " as """ within the csv file. Hence the  palaver with text editor.  The csv file format is very specific to be able to do the upload - perhaps I am still going to have to tweak the file every time the file is rejected because of the validation rules that it uses to accept the incoming csv file. 

                     

                     best wishes from blighty

                    • 7. Re: Re calculation field
                      david_lalonde@d-cogit.ca
                         I am still interested to know which fields do not export from FileMaker Pro to CSV. I believe this is where your root problem lies.
                      • 8. Re: Re calculation field
                        mark_d2x
                          

                        Hi again,

                         

                        I was working my way through the fields,  some were from the main tumour table and others from the look up tables, I managed to get to line 20 ish and then the lookup wouldn't move across on the export records rather than save as excel.

                         

                        I have tried it at home on a new blank database - again seems fine in excel but whn i open in text editor it shows 3 " around each data item - i will send you the test csv via email if thats ok? 

                         

                        Mark 

                        • 9. Re: Re calculation field
                          david_lalonde@d-cogit.ca
                             That is OK. You can find my email address on my WEB site.
                          • 10. Re: Re calculation field
                            david_lalonde@d-cogit.ca
                              
                            I just looked at your files and these are my findings.

                            The CSV file contains exactly what one would expect FileMaker Pro to export. If you remove the quotes in FileMaker Pro, the extra quotes you see in the CSV will dissapear.

                            I am not understanding the issue of looked-up values. FileMaker Pro stores looked-up in the table that calls for the look-up. Looked-up values are not related values. The look-up process gets its data from related values.
                            • 11. Re: Re calculation field
                              mark_d2x
                                

                              Thanks David

                               

                              Thats the problem i just need a single set of " around everything in the final file - the only way i have amanaged to get a file that the host computer is happy with is to manually add the " in a text editor or change the """" manually in a text editor ;-(

                               

                               

                               

                              • 12. Re: Re calculation field
                                david_lalonde@d-cogit.ca
                                   I will send you a file via email to demonstrate you can do this in FileMaker Pro.
                                • 13. Re: Re calculation field
                                  mark_d2x
                                    

                                  Absolute star!

                                   

                                  David,

                                   

                                  Exactly what I need.  

                                   

                                  I have only one complaint - time difference!  

                                   

                                  Went to work this morning, really despondent knowing I have this data upload to do.  Arrived at 7, managed to export and manually alter the csv file to get the patient and tumour records uploaded (jeez they have some strange validation rules - ho hum).

                                   

                                  Spoke to a colleague who told me that information management have to manually alter the csv with a text editor  for their uploads.   Heart sank even further....  I thought computers were here to make life easier.

                                   

                                  By 2 pm I was feeling pretty c^&*, so decided to go home early for once.....  arrived home had an hours kip and logged to find your email (cannot get personal emails at work).

                                   

                                  wahooo! you sent me the perfect solution -  it is exactly as I need it!!!!

                                   

                                   

                                  My faith in life has been restored ;-)

                                   

                                  I can go to bed knowing that tomorrow I can replicate that solution in the reporting section of the database for the 4 csv files (20 +60+70+15 fields) I have to create, just need to tag on the other 1 fields with a find/exclude for the records i want to upload, then send a nice sarcastic email saying that all I have to do is press a button (oh and where is the pay rise? Also when you sending me on the Filemaker Developer course??)

                                   

                                  Seriously though, many thanks for the solution,  really appreciate your help - definitely owe you a beer - I am so easily pleased in my old age 

                                   

                                  Best wishes

                                   

                                  Mark