We help IT Professionals succeed at work.

importing exchange tracking logs to access

posivibe
posivibe asked
on
315 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?
Comment
Watch Question

Commented:
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

Author

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.
Brian MulderFreelance
Top Expert 2006

Commented:
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

Author

Commented:
I'll give this a shot tomorrow.

Author

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".

Brian MulderFreelance
Top Expert 2006

Commented:
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
Freelance
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Adjusted points to 100

Author

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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.