This week, I was working with a customer to onboard Azure Active Directory for Office 365. Since this is a rather large customer, I opted to use the IdFix DirSync Error Remediation Tool to scan for possible problems way ahead of time.
About IdFix
The Office 365 IdFix Error Remediation Tool identifies errors such as duplicates and formatting problems in an Active Directory environment before you synchronize to Azure Active Directory to achieve a Hybrid Identity setup.
The current version is version 1.08.
I’m glad I used the tool, because it offered me a bucket filled with challenges:
The screenshot above illustrates perfectly the power of IdFix and the errors it detects: In the screenshot you’ll notice the duplicate email addresses, and invalid characters in mailnicknames like ampersands and asterisks.
We’ve opted to use the Export button to be able to work with the data. Sorting, reordering and changing the column width, for instance, are features that are not in scope for this tool. Luckily, you can do all that stuff in Microsoft Excel.
When you hit the Export button in IdFix, the tool allows you to specify a comma separated values (CSV) file per RFC 4180:
Challenges with the export file
This file is useful, but unfortunately, you can’t work with it like you’d want to. When you open it in Microsoft Excel, you’ll come to the conclusion that working with a Distinguished Name (DN) in a CSV file is not the most intuitive: When you use the Text to columns functionality in Microsoft Excel, due to differences in Organizational Units (OU) structure, data in the rest of the columns don’t line up in the right columns.
Making the export file useful
I used the following steps to get the data in a format I could work with:
- Open your exported file in Notepad.
- Press Ctrl+h. or select the Replace command from the Edit menu.
- Replace ,contact with ;contact
- Replace ,user with ;user
- Replace ,group with ;group
- Save the file by pressing Ctrl+S or selecting Save from the File menu.
- Now, open the edited CSV file in Microsoft Excel.
- Select the first column. Use Text to Columns, select ; as the separator to separate the Distinguished Names from the rest in the information.
- Select the second column. Use Text to Columns, select , as the separator to separate the rest of the information.
Following these steps, you, too, can use the data in Microsoft Excel.
Enjoy!
Hat tip
This method was explained to me by Mathieu van Wijngaarden.
Further reading
Download IdFix DirSync Error Remediation Tool
Install and run the Office 365 IdFix tool
Office 365 – IdFix DirSync Error Remediation Tool
2857352 “Character" is displayed in the ERROR column for one or more objects after you run the IdFix tool
Hello, in my opinion the best option to export IDFIX list with in format is simply clicking on first left record + shift click on last right bottom record. this will select all values and ctrl+c and paste it to new excel sheet. it works like a charm.
Alternatively, you can use PowerShell:
$IdFix = Import-Csv -Path IdFix_Errors.csv
$IdFix | Export-Csv -Delimiter ";" -Encoding UTF8 -NoTypeInformation -Path IdFix_DelimiterFix_Errors.Csv