Solved

importing exchange tracking logs to access

Posted on 2000-02-23
9
273 Views
Last Modified: 2013-12-23
I am having trouble importing exchange 5.5 sp3 tracking logs to access/excel.  I realize the logs are tab delimited and select the appropriate button.  

What happens is the recepient's name (which should be column 13) appears as a new record.  This makes it impossible to utilize the logs.  This field is separated from the previous one by a line feed (according to M$), but I can't figure out how to get it to ignore this.  During the import you can see a preview of the data you are importing and no matter what I've tried I can't get it formatted properly.  I have searched M$ site and can't find much help on this, they simply tell you to import the log as tab delimited.

I am using Access 2000 with NT 4 SP 5.  Any ideas?
0
Comment
Question by:posivibe
[X]
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
  • 5
  • 3
9 Comments
 

Expert Comment

by:RoLaAus
ID: 2552750
try www.promodag.com  -- I've been using it awhile and it works great, it seems to be free, though a nag screen has just recently started pop-ing up, but if that's all then, I'm satisfied
0
 
LVL 2

Author Comment

by:posivibe
ID: 2552779
I have experience with Promodag, but it limits you to 15 days of import.  I think it's a good tool, but too expensive at this time for our needs.  It would be more useful if I could import 31 days of data, but I understand that they have to make money too... just too costly for now.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 2552790
Hi posivibe,

Well I know it's tedious doing it b hand...from my xperience...

so what you can is this

+with the file just import it so the name will be in the next line.
+now press ALT+F11
+then goto :: insert>new module from the menu
+then paste this code

'=======STARTCODE===============

Option Explicit

Sub DR()

' Data Reorder
' I= The counter for the source sheet
' rwIndex = The counter for the rows in the sheet
' clIndex = The counter for the colls in the sheet

Dim I As Integer
Dim rwIndex As Integer
Dim clIndex As Integer
Dim rngCount As Integer

rngCount = (ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row) - 1
For I = 4 To rngCount
  clIndex = 13
  rwIndex = I
  Sheets("Sheet1").Select
  Range("A" & I).Select
  Selection.Copy
  Cells(rwIndex - 1, clIndex).Select
  ActiveSheet.Paste
  Rows(I).Delete
Next I

End Sub

'=======ENDCODE===============

+it will start at row 4 thats the I
+it will loop through the sheet
+copying the names in collumn 13
+and delete the then empty row
+if you would test it on the first records to that with F8 and step by step
+if all goes well(tested here :O) ) then just press F5
+after it is finished you can close the code window and delete the macro or whatever

HTH:O)Bruintje
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 2

Author Comment

by:posivibe
ID: 2556157
I'll give this a shot tomorrow.
0
 
LVL 2

Author Comment

by:posivibe
ID: 2558299
ok... I tried your code and instructions, but I was unsuccessful.  First of all when I import the data I get the recipient record on the second line and I get another blank record below that.  To be clear I get 3 records for each one I want.

Secondly I cut and pasted your code and then tried to run the macro I get "xlCellTypeLastCell variable undefined error".

0
 
LVL 44

Expert Comment

by:bruintje
ID: 2558906
Hi,

could you send me a sample of such a file?

then i'll can customize it and look at the exact format.

mulbum@worldonline.nl is the address
0
 
LVL 44

Accepted Solution

by:
bruintje earned 150 total points
ID: 2559462
as said we came up with this code for the logs to be more readable in Excel

Option Explicit

Sub DR()

' Data Reorder
' I= The counter for the source sheet
' rwIndex = The counter for the rows in the sheet
' clIndex = The counter for the colls in the sheet

Dim I As Integer
Dim rwIndex As Integer
Dim clIndex As Integer
Dim rngCount As Integer
Dim shName As String

shName = ActiveSheet.Name
ActiveSheet.Range("A1").Select
Selection.EntireRow.Insert

'fill the first row with fields
Range("A1").Value = "Message Id Or MTS - Id"
Range("B1").Value = "Event Number"
Range("C1").Value = "Date/Time"
Range("D1").Value = "Gateway Name"
Range("E1").Value = "Partner Name"
Range("F1").Value = "Remote ID"
Range("G1").Value = "Originator"
Range("H1").Value = "Priority"
Range("I1").Value = "Length"
Range("J1").Value = "Seconds"
Range("K1").Value = "Cost"
Range("L1").Value = "Recipients"
Range("M1").Value = "Recipient Name"
Range("N1").Value = "Recipient Report Status"

'counter for no of rows in report
rngCount = ((ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row) / 3) + 2
'loop through the rows starting at 3
For I = 3 To rngCount
  clIndex = 14
  rwIndex = I
  Sheets(shName).Select
  'select the two fields 13 and 14 on the next row
  Range("A" & I, "B" & I).Select
  'copy the selection
  Selection.Copy
  'select the columnon the first line
  Cells(rwIndex - 1, clIndex).Select
  'paste the copy
  ActiveSheet.Paste
  'delete 2 next rows
  Rows(I).Delete
  Rows(I).Delete
Next I

End Sub

:O)Bruintje
0
 
LVL 2

Author Comment

by:posivibe
ID: 2566119
Adjusted points to 100
0
 
LVL 2

Author Comment

by:posivibe
ID: 2566120
Your code works well, my only problem is my logs are too big for excel.  Let me know if you can figure out a solution for Access.  Thanks all.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Trying to figure out group policy inheritance and which settings apply where can be a chore.  Here's a very simple summary I've written which might help.  Keep in mind, this is just a high-level conceptual overview where I try to avoid getting bogge…
Resolve DNS query failed errors for Exchange
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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