C＃将Active Directory导出为CSV以包含所有用户并包含逗号

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):

我正在努力导出一个CSV文件,其中包含我公司的Active Directory用户列表。我希望CSV文件显示每个用户的员工ID,姓名,电子邮件和电话号码。虽然我能够生成CSV文件中的用户列表,但我遇到了两个问题:1)CSV文件似乎只输出1,000个用户的列表(同时应该接近3,000个用户)和2)I无法弄清楚如何识别其中包含逗号的值以保留在一列中,而不会被推送到下一列。例如,某些员工姓名在该字段中有逗号。我希望它被读作(|代表列中断):

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

123456 |约翰史密斯,ABC | jsmith@company.org | (123)456-7890

instead of:

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

123456 |约翰史密斯| 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.

如果有人看到问题所在,请告诉我!任何,所有的帮助将不胜感激!提前致谢。

2 个解决方案

#1

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

DirectorySearcher每个查询不能返回超过1000个结果。有关解决方法,请查看以下答案:

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

我可以从Asp.Net中的DirectorySearcher获取超过1000条记录吗?

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

至于在字段周围添加引号,请查看以下答案:

how to use comma in csv columns

如何在csv列中使用逗号

#2

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)

通过更改foreach下的代码(结果中的SearchResult sr),我能够通过名称列中的逗号更正问题

            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.

这似乎解决了那些可能一直在努力解决问题的人的问题。

#1