1 2 Previous Next 15 Replies Latest reply on Jul 28, 2016 5:13 PM by keywords

    Export field name

    tays01s

      I've set a variable that can be used in 'Export Records': $path_oc&"Export_outcome.xlsx"

       

      This works. However, I'd like to use: $path_oc&"Export_outcome"&Get(currentdate)&".xlsx"

       

      I get moaned at that "2016.xlsx" cannot be created. I've tried substituting a $date variable previously created from set variable 'get(currentdate)', but get the same answer. What syntax error/rule am I breaking?

        • 1. Re: Export field name
          user19752

          What date format do you want?

          / can't be in file name.

          • 2. Re: Export field name
            keywords

            The following will resolve the date into international format: i.e. 20160727

            Let (

            [

              date = Get ( CurrentDate )

            ; year = Year ( date )

            ; month = Month ( date )

            ; month0 = If ( month < 10 ; "0" & month ; month )

            ; day = Day ( date )

            ] ;

            year&month0&day

            )

            • 3. Re: Export field name
              tays01s

              keywords: Thank you. Exactly what I needed.

              • 4. Re: Export field name
                beverly

                I use yyyymmdd (leading 0's for the month and day) as this makes the filename 'alpha-sortable'.

                 

                19990110.xlsx would then come after 19990101.xslx,

                where 1999011.xslx might not.

                 

                Also keep in mind that TEXT exported (xml or not) with the '.xlsx' extension does not compress the file as this notes:

                 

                It is a combination of XML architecture and ZIP compression for size reduction.

                 

                You can apply the .xsl extension to XML (Open Office, Spreadsheet) text document and Excel will handle it correctly.

                 

                You may or may not have problems using the .xslx extension. It may depend upon platform: 1. creating the file and 2. reading the file. Just a few caveats to keep in mind in case you do run into problems.

                 

                beverly

                1 of 1 people found this helpful
                • 5. Re: Export field name
                  tays01s

                  1. So substitute:

                  ; day = Day ( date )

                  ; day0 = If ( day < 10 ; "0" & day ; day )

                  ] ;

                  year&month0&day0

                  )

                  ??

                   

                  2. Is it safer then to use ".xls"?

                  • 6. Re: Export field name
                    beverly

                    1. yes

                    2. "safer"? It may be just my preference working with XML and Excel on multiple platforms. I just wanted you to have something "in case". If what you have works, then use it.

                     

                    beverly

                    • 7. Re: Export field name
                      keywords

                      I completely agree that leading zeros are needed with days also. Thanks tays01s and beverly for pointing out and fixing up my carelessness!

                      • 8. Re: Export field name
                        user19752

                        Sometimes I want to avoid using if/case in calculation...

                        Let ( d = Get(CurrentDate) ; Year(d) * 10000 + Month(d) * 100 + Day(d) )

                        4 of 4 people found this helpful
                        • 9. Re: Export field name
                          beverly

                          yes! that works well.

                          beverly

                          • 10. Re: Export field name
                            tays01s

                            Ignorance speaking here because I'm unfamiliar with the above:

                            - What is the value of 'get(currentdate)' that for eg. Year(d) x 10000 gives, in this case, 2016? Sorry, I'd just like to understand what's going on underneath.

                            • 11. Re: Export field name
                              beverly

                              The idea is:

                               

                              2016 * 10000 = 20160000

                              JAN * 100      =            100

                              2nd                =              2

                               

                              NOW, add them together:

                               

                                                     = 20160102

                               

                              The calc is just a different way to get the same value as Keywords answer with my suggestion of leading zeros for day, too.

                               

                              Test it out!

                               

                              beverly

                              3 of 3 people found this helpful
                              • 12. Re: Export field name
                                tays01s

                                I'd already tested it. Brilliant. I was thinking there was some hidden value within 'get(currentdate) but you're just treating like text........

                                • 13. Re: Export field name
                                  philmodjunk

                                  I've also just used substitute to replace the "/" with underscores "_".

                                   

                                  Substitute ( Get ( CurrentDate ) ; "/" ; "_" )

                                   

                                  Just another way to keep the file name "legal"

                                  • 14. Re: Export field name
                                    beverly

                                    I've used that method, too, Phil. However long ago I needed the YYYYMMDD (or YYYY_MM_DD) so the "date" was there, the "date" was recognizable universally (ISO 8601), the "date" could be parsed back out as a datetime element, the "date" was sortable alphabetically and still be sorted as if it is a date - sequentially by the year, month & day.

                                     

                                    I also decided the "_" was unneeded, took up space in long file names, and the "-" or "/" might break something else.

                                     

                                    <<name-of-file>>_yyyymmdd.<<extension>>

                                    Just my 3 cents.

                                    beverly

                                    1 2 Previous Next