Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Extract latest date from tab-limited file

Posted on 2012-04-01
8
313 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 84
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 500 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 45

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 84
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 45

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 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