C# Export Active Directory to CSV to include all users & include commas

Question

I am working on exporting a CSV file with a list of the Active Directory users at my company. I would like the CSV file to show the employee ID, name, email, and phone number for each user. While I am able to generate a list of the users within the CSV file, I run into 2 problems: 1) the CSV file only seems to output a list of 1,000 users (while there should be closer to 3,000 users) and 2) I can't figure out how to identify values that have commas within them to stay in one column, without being pushed to the next. For example, some employee names have a comma within the field. I want it to be read as (the | represents the column break):

123456 | John Smith, ABC | jsmith@company.org | (123)456-7890

instead of:

123456 | John Smith | ABC | jsmith@company.org | (123)456-7890

I know for the latter issue, it involves adding quotes around the field "name", but I'm not sure how to do it or where it belongs. Here is my code for this

    //creates a data table for Active Directory information
    public void AD_Table()
    {
        // **Query all of the users within the AD**            
        DirectoryEntry de = new DirectoryEntry(ConnectToDomain());
        SearchResultCollection results;
        DirectorySearcher ds = null;

        ds = new DirectorySearcher(de);
        ds.PropertiesToLoad.Add("employeeID");
        ds.PropertiesToLoad.Add("name");
        ds.PropertiesToLoad.Add("mail");
        ds.PropertiesToLoad.Add("telephoneNumber");

        //filters out inactive or invalid employee user accounts
        ds.Filter = "(&(objectCategory=person)(objectClass=user)(employeeID>= 000001)(employeeID<= 999999))";
        results = ds.FindAll();



        //header columns for Data Table
        DataTable dt = new DataTable();
        dt.Columns.Add("Employee ID", typeof(string));
        dt.Columns.Add("Full Name", typeof(string));
        dt.Columns.Add("Office Email", typeof(string));
        dt.Columns.Add("Office Phone", typeof(string));


        foreach (SearchResult sr in results)
        {
            DataRow dr = dt.NewRow();
            DirectoryEntry entry = sr.GetDirectoryEntry();
            if (entry.Properties["employeeID"].Count > 0)
                dr["Employee ID"] = entry.Properties["employeeID"].Value.ToString();
            if (entry.Properties["name"].Count > 0)
                dr["Full Name"] = entry.Properties["name"].Value.ToString();
            if (entry.Properties["mail"].Count > 0)
                dr["Office Email"] = entry.Properties["mail"].Value.ToString();
            if (entry.Properties["telephoneNumber"].Count > 0)
                dr["Office Phone"] = entry.Properties["telephoneNumber"].Value.ToString();
            dt.Rows.Add(dr);
        }

        string fullFilePath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"\\LSSUpdaterVM\AD\Update\ADWorkEmailPhone.csv");
        //move datatable into CSV
        CreateCSVFile(dt, fullFilePath);

    }


    //creating the CSV file with the AD user info
    public void CreateCSVFile(DataTable dt, string strPath)
    {
        try
        {
            StreamWriter sw = new StreamWriter(strPath, false);
            int columnCount = dt.Columns.Count;
            for (int i = 0; i < columnCount; i++)
            {
                sw.Write(dt.Columns[i]);
                if (i < columnCount - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);

            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < columnCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        sw.Write(dr[i].ToString());
                    }
                    if (i < columnCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

}

If someone sees where the problem(s) is, please let me know! Any, and all help will be much appreciated! Thanks in advance.

also, the "ConnectToDomain" is a function that accesses the Active Directory using the LDAP path, just for clarification. — rheanna, Jul 13 '15 at 16:05
Also: [powershell](https://gallery.technet.microsoft.com/scriptcenter/Powershell-script-to-5edcdaea) — Mitch, Jul 13 '15 at 16:52

score 0 · Answer 1 · edited May 23 '17 at 11:44

0

DirectorySearcher cannot return more than 1000 results per query. Please take a look at the following answer for a work around:

Can I get more than 1000 records from a DirectorySearcher in Asp.Net?

As for adding quotes around a field, take a look at the following answer:

how to use comma in csv columns

edited May 23 '17 at 11:44

Community

1
1

answered Jul 13 '15 at 16:41

Alexandre St-Amant

86
8

Using the first link you provided, I was able to correct the issue I had with returning more than 1000 entriers, but I'm still having issues with the second issuw regarding the comma. I looked at the link you provided along with countless others that I found on a search engine, but it's hard to understand how to apply it to my situation, since I'm using an Active Directory and a datatable. Whenever I try to add double quotes or something, I get an error message stating that I can't do it since it's an object and not a string (even though I converted to a string...) – rheanna Jul 13 '15 at 18:11

score 0 · Accepted Answer · answered Jul 13 '15 at 18:58

I was able to correct the problem with the comma in the name column by changing the code under the foreach (SearchResult sr in results)

            if (entry.Properties["name"].Count > 0)
            {
                dr["Full Name"] = entry.Properties["name"].Value.ToString();
                    dr["Full Name"] = string.Format("\"{0}\"", dr["Full Name"]);
            }

This seems to have fixed the problem for those who might have been struggling with it as well.

C# Export Active Directory to CSV to include all users & include commas

2 Answers2