importing exchange tracking logs to access

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?
LVL 2
posivibeAsked:
Who is Participating?
 
bruintjeConnect With a Mentor Commented:
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
 
RoLaAusCommented:
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
 
posivibeAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
bruintjeCommented:
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
 
posivibeAuthor Commented:
I'll give this a shot tomorrow.
0
 
posivibeAuthor Commented:
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
 
bruintjeCommented:
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
 
posivibeAuthor Commented:
Adjusted points to 100
0
 
posivibeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.