2 Replies Latest reply: Feb 18, 2013 4:39 PM by acanelivehealthier RSS

    Importing portion of file name as a date?

      Hello,

       

      I'm having trouble with my code as I'm trying to automate a file. 

       

      What I'm trying to do is bring a portion of the file name in as a date.  It doesn't appear to work.

       

      My file is named Email by Client - Feb.xls

       

      My logic seems sound.  I am taking the FileBaseName, searching the string for only the 3 characters from the right that I’ve named to abbreviate the month, turning that month into a numeric value, and then trying to turn it into a date.

       

      If I use just (right(FileBaseName(),3) the word “Feb” comes in correctly.  It’s when I add num and date that don’t seem to transform it into a numeric date.  The reason I want to turn it into a numeric date is so that I can drop a bunch of files in this folder and add "Mar, May, June, etc" to their file names, essentially having a file for each month.

       

      LOAD Department,
      Total,
      [Under 24],
      Percentage,
      date(num(right(FileBaseName(),3))) as Month
      FROM
      [C:\Metrics\Reports\2013\Email\Email by Client - Feb.xls]
      (
      biff, embedded labels, table is [Worksheet$]);