1 2 Previous Next 19 Replies Latest reply on Feb 19, 2014 5:11 PM by FentonJones

    filemaker to applescript conversion

      Title

      filemaker to applescript conversion

      Post

           HI

           I have a short applescript that is designed to open a file in my desktop in excel, and save the file as a cdv, then close. This all works fine, except the file is originally created and named by filemaker. The file is named the after the previous month followed by invoices. I do not know how to script this in applescript - below shows what I am trying to say , but the get parts need translation!

           The reason I am doing this is because Filemaker is not capable of exporting as windows csv, only mac type (filled with"""")

            

            
            
            
           set theDoc to (path to desktop as text) & "file:" & "Invoices " & MonthName ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) )& ".csv"
            
           set outPath to (path to desktop as text) &  "Invoices " & MonthName ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) )& ".csv"
            
           tell application "Microsoft Excel"
               open file theDoc
               tell workbook 1
                   tell sheet 1
                       save in outPath as CSV file format
                   end tell
                   close without saving
               end tell
           end tell

        • 1. Re: filemaker to applescript conversion
          philmodjunk

               Filemaker is not capable of exporting as windows csv,

               I will gently disagree with that. The presence of the quotes does not make this a "mac type csv". It's expected for csv exports to keep embeeded commas from being perceived as field separating commas and most programs that import from csv can handle the quotes. As we determined in another thread, your other software is unable to handle this format--a limitation of the other program, not FileMaker.

               PS. Haven't owned a Mac in years so can't help you with the Applescripting.

          • 2. Re: filemaker to applescript conversion

                 Yup I thought that might be the case. But an applescript appears to be the only way I can automate this process....

                 Thank you

            • 3. Re: filemaker to applescript conversion
              FentonJones

                    

                   The main problem I see is:

                   MonthName ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) )

                    

                   This is saying to "month name" (test) minus "day" (text, but like number). 

                   It would think you're trying to subtract the day from the name of the month. I think you meant to put:

                   MonthName ( Get ( CurrentDate )) & "-" & Day ( Get ( CurrentDate ))

                    

                   The other problem is just how to mix FileMaker commands (such as above) with pure AppleScript commands, such as: (path to desktop as text), etc.; especially when they are run via the FM script step, using the "by calculation" option, as it requires extra "\"" in order to show a quotation mark in the result.

                    

                   Personally, I very seldom use that. Because it's harder, especially when the AS is long. But in this case it seemed fairly appropriate, so I gave it a go. It too me a while, but this is what I got:

                    

                   "set theDoc to (path to desktop as text) & " & "\"Invoices "  & ( MonthName ( Get ( CurrentDate )) & "-" & Day ( Get ( CurrentDate )) ) & ".csv\"" & ¶ & 

                   "set outPath to (path to desktop as text) & " & "\"Invoices "  & ( MonthName ( Get ( CurrentDate )) & "-" & Day ( Get ( CurrentDate )) ) & ".csv\"" & ¶ & 

                    

                   "tell application \"Microsoft Excel\"" & ¶ & 

                       "open file theDoc" & ¶ & 

                       "tell workbook 1" & ¶ & 

                           "tell sheet 1" & ¶ & 

                               "save in outPath as CSV file format" & ¶ & 

                           "end tell" & ¶ & 

                           "close without saving" & ¶ & 

                       "end tell" & ¶ & 

                   "end tell"

                    

                   I did NOT ever test the Excel part of it. Also notice that I just put the SAME for "theDoc" and "outPath". I think that your "theDoc" was incorrect, as you would not use "file:" in an AS script; only FM uses "file:" to start a file path. It is what you could use to Export to/create a file. In which case it's easier to let FM do it itself, by setting a Variable, then passing the Variable as the Export path (it will except this); example Variable script step calc:

                   "file:" & Get ( DesktopPath ) & "Invoices " & MonthName ( Get (CurrentDate )) & "-" & Day ( Get (CurrentDate )) & ".csv"

                    

                   I guess you'd need to change the name slightly, if you want to have 2 files. Or (I believe), you can use just the 1 file path, and Excel will just override the existing file? However I didn't test that. But let me know if it doesn't work.*

                    

                   * It may also be possible to do this Export from FM via xml/xsl, to produce what you need; but that's another world (is some ways).

                    

                   P.S. I used to be better at this sort of thing. However a stroke 3 years ago has messed with my ability to do much that I could before; including type or reading this post (reading long sentences is one of my worst problems) :-/

              • 4. Re: filemaker to applescript conversion

                     Dear Fenton 

                     Thank you so much for such a thorough response - I think even those who had not suffered a stroke would have difficulty comprehending my waffle!

                     I shall look over it when my brain is fully engaged and let you know how I get on..

                     Thank you again

                     Will

                • 5. Re: filemaker to applescript conversion
                  philmodjunk

                       Hmmm, you could also just export your data as an excel file.

                       Or I think that you can set up an Excel file with a Macro that runs when you open the file to: a) import, b) export, c) delete all rows.

                       And from FileMaker Send Event can be used to open that excel file....

                  • 6. Re: filemaker to applescript conversion

                         The software I am importing to, only accepts windows CSV files ( no """''). Either I export as cdv from FM as usual and then auto open in excel, save as cdv windows, or I automate it - this is what I hope to achieve with Fenton's suggestion...I am even more of a novice with excel as I am with Filemaker (don't get me started on Applescript!)

                    • 7. Re: filemaker to applescript conversion
                      philmodjunk

                           Yes, but by exporting as Excel and then exporting from Excel as CSV, you save a step in the process.

                      • 8. Re: filemaker to applescript conversion
                        FentonJones

                              

                             I may be just adding to the task. I have another way to do this; something I wrote long ago (when my brain worked fine). I taught myself the basics of XML/XSL. One of its strong points is the ability to be used to Export from FileMaker as more or less however you wanted; it was especially used to solve this Export as CSV from a Mac and get a PC result. The result will be a Windows file (as far as I can tell, as it has the correct "
"). I also has no "quotation marks" (or does, if you want).
                              
                             The question I have is whether you want the first line of the result to be a "header" line, i.e., the field names. Or do you not want a header, just the data?
                             [ If you do want a header, do you want to use the field names? Or do you want to hand-write the header yourself? All of this is done with an XSL text file, to say what you get, and it can use either FM's field names, or just what you write.
                              
                             I have a small FileMaker file which has a few separate scripts that can do this, each using a different XSL file to do used to produce the different result. I created this for myself. I have the various XSL files needed. I could make this all available for download. But if I knew exactly which you wanted, it might be simpler for you.
                              
                             This is a picture of the result, with a Header (which has hand-written in the XSL). It opened by BBEdit, and you'll see it is a Windows file (at the bottom):
                        • 9. Re: filemaker to applescript conversion
                          FentonJones

                                

                               If anyone wants to look at the full FM file, and the various XSL files. It is fairly small, not really complex, though XML / XSL are a bit much for the first time (days…). It is mostly text. The FM part is not that complex either. However, you'll want to keep the .zip file, in case.

                                

                               FileMaker handles looking at setting of the Export as XML steps this way. You click on the Export Records, then on "Specify output file", which asks the normal; but then, after you say OK, it opens another small dialog, to let you specify the XSL file. I just use a simple text file, which I keep there close to the FM file (as I may have worked on it, may want to see it that way). But you could also put it someway else, even online, or you couple copy/paste it into a calculation (though I believe you'd have to follow the FM calc rules, for quotation marks, etc.).

                                

                               Finally, but sure to NOT Save any of what you're looking at (unless you know what you've done), as it will ask after just looking at that above (it seems not quite as smart, user friendly) as other FM script steps on that).

                                

                                

                               If anyone wants to look at the full FM file, and the various XSL files. It is fairly small, not really complex, though XML / XSL are a bit much for the first time (days…). It is mostly text. The FM part is not that complex either. However, you'll want to keep the .zip file, in case.

                                

                               FileMaker handles looking at setting of the Export as XML steps this way. You click on the Export Records, then on "Specify output file", which asks the normal; but then, after you say OK, it opens another small dialog, to let you specify the XSL file. I just use a simple text file, which I keep there close to the FM file (as I may have worked on it, may want to see it that way). But you could also put it someway else, even online, or you couple copy/paste it into a calculation (though I believe you'd have to follow the FM calc rules, for quotation marks, etc.).

                                

                               Finally, but sure to NOT Save any of what you're looking at (unless you know what you've done), as it will ask after just looking at that above (it seems not quite as smart, user friendly) as other FM script steps on that).

                                

                          https://www.dropbox.com/s/eykd5fyvl7t8k33/CSV_Text_noQuotes.zip

                          • 10. Re: filemaker to applescript conversion

                                 HI Fenton

                                 Quick answer to your question - I do not need header line! So at least that makes things a little easier! Will run some tests tomorrow and let you know - thank you

                            • 11. Re: filemaker to applescript conversion
                              FentonJones

                                   Quick fix. I redid the FM file a bit, and put it instead on my earlier post. Mostly I renamed the scripts, so it's easier to see which each one's result will be. I also added a "plain" script for the PC (so they'd have all 3; I already had the XSL file for it). That is the one you want, with no header, PC file. 

                              • 12. Re: filemaker to applescript conversion

                                     Hi Fenton

                                     I have  had a brief go on your script to test out. I set the variable to :

                                     Case ( Abs ( Get ( SystemPlatform )) = 1; "filemac:"; "filewin:") & 
                                     Get ( DesktopPath ) &  "Invoices " & MonthName ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) )& ".csv"

                                      

                                     and also the calculated XML file name to

                                     Case ( Abs ( Get ( SystemPlatform )) = 1; "filemac:"; "filewin:") & 

                                     Get ( DesktopPath ) &  "Invoices " & MonthName ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) )& ".csv"

                                     I run the script and get this error -

                                SAXParseException: unable to determine file base pathname (Occurred in an unknown entity, at line 0, column 0.)

                                     I am using the script -Export CSV Text noQuotes 8 plain PC, and I upgraded the file to FMA13

                                     A file is created on my desktop called "January Invoices.csv" but it contains no data.  I am sure you will know immediately what error I have made - I could spend hours fumbling around on the net so thought a little quicker to ask your help again! thank you!

                                • 13. Re: filemaker to applescript conversion
                                  FentonJones

                                        

                                            I think your problem was the original problem again. Which is that you cannot say "month name" minus "day". They are text and you want a text result. You want "-" as text between two text results. Since this is for the Export, you want to use this in a Variable (which you can then use as the file path to Export to (just type the $name as the file path). Try this.
                                             
                                            "filemac:" & Get ( DesktopPath ) & "Invoices " & MonthName ( Get (CurrentDate )) & "-" & Day ( Get (CurrentDate )) & ".csv"
                                             
                                            We can just type "filemac:" as this is all for a Mac computer, hence we need no test for platform. That was just left over from a copy/paste from another file I had (which was cross-platform). I say just "filemac:" (or "file:", either works for this), as Get (DesktopPath) result starts with "/".
                                             
                                            Make sure your other calculations for AppleScript do not also have that "month name" minus "day" problem. (Also notice the ")" in both cases is after its text, not all at the end: MonthName ( Get (CurrentDate ))
                                             
                                            One other thing. Please download my file again. I redid the naming, to make more sense, and added the script you need. Which was "Export CSV Text PC", the simples one for XML as PC file export. 
                                            [ I could tell you have the original file as it had "8" in the name. Which was for FileMaker 8, vs. FileMaker 7. Which hints how long ago I wrote that.]
                                             
                                            Sorry I haven't been entirely clear with all this. If you're still having trouble with this I will pull together "your Export file" (with the "month name", etc.) with my "XML Export" file, so that you can see it all as one thing. I'll have to fake an "invoice" however. But that is not critical, as the XSL does not care what the table is or how many fields are involved, hence will work as is (other than you setting up the "Specify export order", which is just a normal FM setting.

                                        

                                  • 14. Re: filemaker to applescript conversion

                                         Hi Fenton

                                         I think i am getting in a slight muddle with this as confusing which parts I need to adapt to my  "Invoices " & MonthName ( Get (CurrentDate )) & "-" & Day ( Get (CurrentDate )) & ".csv"

                                         I was altering my original FM script, but it is in fact the Applescript part - but your script has no applescript, just the FM script to export to XML (which I have nil experience with!).
                                          
                                         I know what I am trying to achieve - some method for filemaker to convert a newly created file (called invoices plus prev. month's  name) from a mac csv to a windows  csv. I will bolt this script onto the end of my existing export script.
                                          
                                         I know I am going backwards a little but this is turning out a little tricky for a weekend brain!
                                         I looked at the a script (redownloaded) that you wrote,  and moved the other files to my desktop. It works fine - but how do I alter this to convert the existing invoicesprevmonth.csv file to a windows type? I tried to replace the variable with the text as you suggested but it didn't like it much. And if we do use your altered script, does that mean no need for applescript? Sorry for this and thank you so much for your help on this...
                                          
                                          
                                          
                                    1 2 Previous Next