Solved

importing exchange tracking logs to access

Posted on 2000-02-23
9
227 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
  • 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
 
LVL 2

Author Comment

by:posivibe
ID: 2556157
I'll give this a shot tomorrow.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Downtime reduced, data recovered by utilizing an Experts Exchange Business Account Challenge The United States Marine Corps employs more than 200,000 active-duty Marines with operations in four continents, all requiring complex networking system…
This is the first one of a series of articles I’ll be writing to address technical issues that are always referred to as network problems. The network boundaries have changed, therefore having an understanding of how each piece in the network  puzzl…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now