Be Careful What You Get When CSV Exporting of Mailboxes in Exchange
I learned an important lesson the other day when I went to export the list of mailboxes from my firm’s Microsoft Exchange server – Microsoft did not take into consideration the commas that are potentially found in the name of the Mailbox, Size (KB), and Total Items columns.
To find out what I am talking about open up the Exchange System Manager on your Microsoft Exchange server, then browser to Administrative Groups –> [Group Name] –> Servers –> [Server Name] –> [Storage Group Name] –> Mailbox Store –> Mailboxes. Right-click on Mailboxes and go to Export List…, at which point an Export List window appears that will ask for the File name of your export file and the format you wish to save it in. Choose Text (Comma Delimited) (*.csv) from the Save as type drop-down menu, choose a name, and click Save.
The CSV file that you now have will be a complete mess; especially if you try and open it with Microsoft Excel (I am using Excel 2007). Instead of the six columns that are found under Mailboxes (Mailbox, Last Logged on By, Size (KB), Total Items, Last Logon Time, and Last Logoff Time), you could have (as in my case, dependent on the actual sizes of the mailboxes) ten columns. In my case, the Mailbox was split into two columns (first and last name), the Size (KB) column was split into three columns, and the Total Items was split into two columns. This is because Microsoft pretends everything is text, and no commas are stripped from any of the number columns. The names are problematic simply because our system has a Last, First schema for Mailbox names.
The way around this mess would be to actually follow RFC 4180 memo, that while not an actual standard, has good practices and conventions for CSV files, one of which is to place quotes around each column to preserve the internal commas. But as always Microsoft does not think ahead very far and their QA program is abysmal. Shame on them, and too bad for us. The only way to export your list of mailboxes (that I am aware of) is to export them as a tab delimited text file that can then be imported into Excel and formatted as it needs to be.
To my mind, Microsoft should have either placed quotes around every column entry (why not, since they are treating everything as text), or not sent the numbers as text with commas into the file (they are only numbers after all). With this second option<, the export would only fail on the Mailbox name, which is an issue only when you have a schema where there is a comma in the name (which I also believe is bad, but I did not set it up).
Either way, could Microsoft stop being so near-sighted and think ahead at what they are doing?