By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,226 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,226 IT Pros & Developers. It's quick & easy.

Extract month and year from filename into MS Access SQL query

P: 2
I have an Excel spreadsheet with a filename that contains a month and year eg. 'Consolidated Works Update Sheet SEPTEMBER 2011.xls'. I would like to extract the month and year from the filename and convert it to a date in the format dd/mm/yyyy within an SQL Update query. The 'dd' part of the date needs to be the last day of the month eg. for the above example it would need to be 31/09/2011. Any advice on how to approach this would be much appreciated.
Nov 9 '11 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,679
  1. Is the Naming Convention always consistent as the example you have Posted, namely:
    Expand|Select|Wrap|Line Numbers
    1. <Base Filename> & " & " " & <Month> & " " & <Year> & .xls
    2. 'Consolidated Works Update Sheet SEPTEMBER 2011.xls'
  2. If the Filename is not always consistent, is the only Numeric Value in the Filename a Year, preceded by a Month?
Nov 9 '11 #2

P: 2
Hi ADezii,

The filename may not always be consistent, but the only numeric value in the filename and year would be preceded by a month. On occasions, there may be text after the numeric year eg. '..SEPTEMBER 2011 - amended.xls'.

Thanks,
James
Nov 10 '11 #3

dsatino
100+
P: 393
Your best bet would be to change the naming convention for the file to YYYY-MM-etc...

This would make things much easier than coding for a bunch of possible scenarios.
Nov 10 '11 #4

ADezii
Expert 5K+
P: 8,679
I had a little trouble with this Thread, as it proved to be a little more involved than I originally anticipated. I did, however, arrive at a workable solution which I will walk you through now. Do not be intimidate by the Code since approximately 50% of its content deals with Variable Declarations and Code related to the Office File Dialog. If you are having trouble understanding the Code and/or concept, I will be more than happy to Attach the Demo that I used for this Thread. Just let me know if you require it.
  1. Set a Reference to the Microsoft Office XX.X Object Library.
  2. When first executed, the Office File Dialog will open filtered for Excel Files only (*.xls)
  3. Once the User selects a File:
    1. All Components of the File PATH are placed into a Variant Array using a Space (" ") as the delimiter.
    2. Every Element in the Array is examined via the 4 leftmost characters (to allow for yyyy.xls) to look for the Numeric Value, since you claimed that the Year would be the only Numeric Value in the File Name.
    3. Once the Year is found it is extracted and placed into a Variable.
    4. The Month is then easily extracted since it is the Element in the Array prior to the Year. The String representing the Month is then passed to a Function which will return the Numeric Value representing the Mon th (1 to 13), or 999, which will represent an in valid Month Name. Three possible Variantions of the M on th are taken into consideration, namely: Jan, Jan., or January.
    5. The Return Value of the Function is then plaqced into another Variable where it is analyzed.
    6. If the Month Value is not valid, a Message Box appears, and the Routine is exited.
    7. The extracted Year and Month is then used to calculate the Last Day of the Month, and the Final Date is now properly Formatted to dd/mm/yyyy.
  4. After this entire process, this generated Date can now be used in an SQL Statement.
  5. Should you have any questions, or require the Demo, just let me know.
Nov 10 '11 #5

ADezii
Expert 5K+
P: 8,679
I had a little trouble with this Thread, as it proved to be a little more involved than I originally anticipated. I did, however, arrive at a workable solution which I will walk you through now. Do not be intimidate by the Code since approximately 50% of its content deals with Variable Declarations and Code related to the Office File Dialog. If you are having trouble understanding the Code and/or concept, I will be more than happy to Attach the Demo that I used for this Thread. Just let me know if you require it.
  1. Set a Reference to the Microsoft Office XX.X Object Library.
  2. When first executed, the Office File Dialog will open filtered for Excel Files only (*.xls)
  3. Once the User selects a File:
    1. All Components of the File PATH are placed into a Variant Array using a Space (" ") as the delimiter.
    2. Every Element in the Array is examined via the 4 leftmost characters (to allow for yyyy.xls) to look for the Numeric Value, since you claimed that the Year would be the only Numeric Value in the File Name.
    3. Once the Year is found it is extracted and placed into a Variable.
    4. The Month is then easily extracted since it is the Element in the Array prior to the Year. The String representing the Month is then passed to a Function which will return the Numeric Value representing the Month (1 to 12), or 999, which will represent an in valid Month Name. Three possible Variations of the Month are taken into consideration, namely: Jan, Jan., or January.
    5. The Return Value of the Function is then placed into another Variable where it is analyzed.
    6. If the Month Value is not valid, a Message Box appears, and the Routine is exited.
    7. The extracted Year and Month is then used to calculate the Last Day of the Month, and the Final Date is now properly Formatted to dd/mm/yyyy.
  4. After this entire process, this generated Date can now be used in an SQL Statement.
  5. Should you have any questions, or require the Demo, just let me know.
Expand|Select|Wrap|Line Numbers
  1. 'First, set a Reference to the Microsoft Office XX.X Object Library
  2. Dim strButtonCaption As String
  3. Dim strDialogTitle As String
  4. Dim strHyperlinkFile As String
  5. Dim varItems As Variant
  6. Dim strPath As String
  7. Dim intCtr As Integer
  8. Dim intYear As Integer
  9. Dim strMonth As String
  10. Dim intMonth As Integer
  11. Dim dteTheDate As Date
  12.  
  13. 'Define your own Captions if necessary
  14. strButtonCaption = "Open"
  15. strDialogTitle = "Select Excel File To Convert"
  16.  
  17. With Application.FileDialog(msoFileDialogFilePicker)
  18.   With .Filters
  19.     .Clear
  20.     .Add ".XLS Files", "*.xls"     'Allow only *.xls File types
  21.   End With
  22.   'The Show Method returns True if 1 or more files are selected
  23.     .AllowMultiSelect = False       'Critical Line
  24.     .FilterIndex = 1
  25.     .ButtonName = strButtonCaption
  26.     .InitialView = msoFileDialogViewDetails     'Detailed View
  27.     .Title = strDialogTitle
  28.     .InitialFileName = CurrentProject.Path
  29.   If .Show Then
  30.     strPath = .SelectedItems(1)
  31.   Else
  32.     Exit Sub
  33.   End If
  34. End With
  35.  
  36. varItems = Split(strPath, " ")
  37. For intCtr = LBound(varItems) To UBound(varItems)
  38.   'Let's find he Numeric Element in the Array, the Month will be
  39.   'immediately before it, we must allow for Filenames such as:
  40.   'Consolidated Works Update Sheet SEPTEMBER 2011.xls
  41.   'Consolidated Works Update Sheet SEPTEMBER 2011 - amended.xls
  42.   If IsNumeric(Left(varItems(intCtr), 4)) Then
  43.     strMonth = varItems(intCtr - 1)
  44.     If InStr(varItems(intCtr), ".xls") = 1 Then
  45.       intYear = Left(varItems(intCtr), InStr(varItems, ".xls") - 1)
  46.     Else
  47.       intYear = Val(varItems(intCtr))
  48.     End If
  49.  
  50.     intMonth = fReturnMonthNum(strMonth)
  51.     If intMonth = 999 Then
  52.       MsgBox "Invalid Month passed to fReturnMonthNum()", vbCritical, "Invalid Month"
  53.         Exit Sub
  54.     End If
  55.  
  56.     'If we get here, all is well, generate the Last Day of the Month Formatted to 'dd/mm/yyyy/
  57.     dteTheDate = Format(DateSerial(intYear, intMonth + 1, 1) - 1, "dd/mm/yyyy")
  58.  
  59.     'Sample SQL usage
  60.     '- "SELECT * FROM [Some Table] WHERE [Some Date] = #" & dteTheDate & "#;"
  61.     MsgBox dteTheDate
  62.   End If
  63. Next
Expand|Select|Wrap|Line Numbers
  1. Public Function fReturnMonthNum(strMonthName As String) As Integer
  2. Dim intMonthVal As Integer
  3. 'Return the Month as a Numeric Value for Date Creation
  4. Select Case strMonthName
  5.   Case "Jan", "Jan.", "January"
  6.     intMonthVal = 1
  7.   Case "Feb", "Feb.", "February"
  8.     intMonthVal = 2
  9.   Case "Mar", "Mar.", "March"
  10.     intMonthVal = 3
  11.   Case "Apr", "Apr.", "April"
  12.     intMonthVal = 4
  13.   Case "May", "May.", "May"
  14.     intMonthVal = 5
  15.   Case "Jun", "Jun.", "June"
  16.     intMonthVal = 6
  17.   Case "Jul", "Jul.", "July"
  18.     intMonthVal = 7
  19.   Case "Aug", "Aug.", "August"
  20.     intMonthVal = 8
  21.   Case "Sep", "Sep.", "September"
  22.     intMonthVal = 9
  23.   Case "Oct", "Oct.", "October"
  24.     intMonthVal = 10
  25.   Case "Nov", "Nov.", "November"
  26.     intMonthVal = 11
  27.   Case "Dez", "Dec.", "December"
  28.     intMonthVal = 12
  29.   Case Else
  30.     intMonthVal = 999           'Invalid Month
  31. End Select
  32.  
  33. fReturnMonthNum = intMonthVal
  34. End Function
Nov 10 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.