Step-by-Step Guide

Step 2: Import the Exchange Server SMTP log file into Microsoft Excel

Now that you've completed the instructions in Step 1, the SMTP log file is in space delimited format and ready to import into Microsoft Excel:

  1. Close the log file and open Microsoft Excel. Select the File -> Open command.

  2. By default, Microsoft Excel is configured to look for .XLS files. Since we aren't opening a true spreadsheet though, set the file type option to Text Files.

  3. Navigate to the %SYSTEMROOT%\system32\LogFiles\SMTPSVC1 folder and open the modified SMTP log file to launch the Microsoft Excel Text Import Wizard shown in Figure C.


    Figure C: Excel will use the Text Import Wizard to import your SMTP log file.

  4. As you can see in Figure C, the wizard reports incorrectly that the data is fixed width. Since you are not working with a fixed-width data set, select the Delimited option and click Next.


    Figure D: You must use a space delimiter instead of a tab delimiter.

  5. You will now see the screen shown in Figure D, which asks you what type of delimiter the file uses. As I mentioned earlier, this file is space delimited. Therefore, deselect the Tab checkbox and select the Space checkbox instead. The Data Preview section of the Text Import wizard will show the log file organized into rows, as shown in Figure E.


    Figure E: The Data Preview should show the data organized into columns.

  6. Click Finish and your data will be imported to Microsoft Excel.

    Requires Free Membership to View

However, if you click Next instead, you will see a screen similar to the one that's shown in Figure F. This screen allows you to perform some additional formatting on your data before importing it.


Figure F: You can custom format the columns.

For example, the first column of data consists of the date in year-month-day format. You can actually tell Microsoft Excel to reformat the date in month-day-year format. You can perform similar types of formatting on the other columns. You can also elect to omit columns that you do not need.

When you are done formatting the data, click Finish to import the data into Microsoft Excel.


STEP-BY-STEP GUIDE: HOW TO ANALYZE SMTP LOG FILES IN EXCEL

 Home: Introduction
 Step 1: Convert the Exchange Server SMTP log file to a text file
 Step 2: Import the Exchange Server SMTP log file into Excel
 Step 3: Analyze SMTP traffic bandwidth consumption in Excel
 Step 4: Related resources from SearchExchange.com

ABOUT THE AUTHOR:   
Brien M. Posey, MCSE
Brien M. Posey, MCSE, is a Microsoft Most Valuable Professional for his work with Exchange Server, and has previously received Microsoft's MVP award for Windows Server and Internet Information Server (IIS). Brien has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, Brien has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Brien's personal Web site at http://www.brienposey.com.

This was first published in February 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: