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.

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.

Sound Off! -   Be the first to post a message to Sound Off!

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary