Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Extract latest date from tab-limited file

Posted on 2012-04-01
8
Medium Priority
?
338 Views
Last Modified: 2012-04-15
Hi Experts,

Please assist in extracting latest date from tab delimited file.

I get this file weekly when system are polled by agent.
This agent just adds the date to current field 'SMS_R_System.Agent Time' though not consistent.

would like to extract the latest date from this field as mentioned in desired_result tab.
Attached is the file for current data and result_data

I am importing this file in Access.
conversion formula can be in excel or during import.

Thank you
Regards
Sample.xlsx
0
Comment
Question by:macentrap
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 85
ID: 37792943
Import the Excel sheet into an Access table, add a new column named "Latest_Date" and then open a recordset based on that table. Then, use the Split function to examine the contents:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM tmpImport")

Dim arr() As STring
Dim LatestDate As String
Dim i As Integer

Do Until rst.EOF
  LatestDate = "0"
   arr = Split(rst("SMS_R_System.Agent Time"), ",")
   For i =0 to Ubound(arr)
    If CDate(LatestDate) < arr(i) Then
      LatestDate = arr(i)
    End If
   Next i
  '/ LatestDate would now contain the most recent date for this line 
  rst("Latest_Date") = LatestDate
  rst.MoveNext
Loop

Open in new window

0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 37792972
Use this function

=getmaxdate(B2)

with this code

Function getmaxdate(dl As String)
    Dim dlst() As String, d As Variant, dmax As String
    dlst = Split(dl, ",")
    dmax = "1/1/80"
    For Each d In dlst
        If CVDate(dmax) < CVDate(d) Then dmax = d
    Next d
getmaxdate = CVDate(dmax)
End Function

Open in new window

0
 
LVL 7

Author Comment

by:macentrap
ID: 37793000
Thank you LSM Consulting and ssaqibh,

ssaqibh is yours excel based VBA, if so will try this one first over Access
Do I just paste the VBA ?
Thank You
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37793007
Right-click on the sheet tab name
Select view code
Select    Insert > Module
Paste the given code in this window
Close the window
Put this function in C2        =getmaxdate(B2)
Copy it down
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37793014
Thoughts:
* these dates are coming in as dd/mm/yyyy so the regional settings will affect how they are treated by the VB function

* If you are working with dates, you should use Date data types to reduce the number of CDate() invocations.

* You don't need to start with an artificial date, you can use the first date in the comma-delimited list as the first date.  This also reduces the number of comparisons/iterations

The following is based on the LSM code.
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM tmpImport")

Dim arr() As STring
Dim LatestDate As Date
Dim DateItem As Date
Dim i As Long

Do Until rst.EOF
   arr = Split(rst("SMS_R_System.Agent Time"), ",")
   LatestDate = CDate(arr(0))
   For i =1 to Ubound(arr)
     DateItem = CDate(arr(i))
     If LatestDate < DateItem Then
      LatestDate = DateItem
    End If
   Next i
  '/ LatestDate would now contain the most recent date for this line 
  rst("Latest_Date") = LatestDate
  rst.MoveNext
Loop

Open in new window

0
 
LVL 85
ID: 37793911
Wouldn't your line here fail, if there is no data in the column:

 LatestDate = CDate(arr(0))

My point in pre-setting the LatestDate value was to avoid that possibility.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37795497
@LSM

If there is the possibility of no dates, the SQL would be changed to prevent zero-length (or Null) rows from populating the recordset.  This would improve performance by reducing the amount of work to be performed and minimizing I/O.
0
 
LVL 7

Author Closing Comment

by:macentrap
ID: 37849724
Apologies, for late reply.
THought i closed it.

Thank you ssaqibh and Experts for help
ssaqibh: easy to follow.
I didnt try other solutions
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question