将.csv转换为数据表时额外的引号

本文关键字：csv 转换数据表 | 更新日期: 2023-09-27 18:07:20

我需要用两个excel文件填充两个数据表。文件可以是逗号分隔值文件(.csv)，也可以是Excel文档(.xlsx)文件。我使用一个函数将我的。csv转换为数据表:

public DataTable GetDataTableFromCsv(string path)
{
    DataTable dataTable = new DataTable();
    String[] values;
    values = File.ReadAllLines(path);
    string[] csvRows = System.IO.File.ReadAllLines(path);
    string[] headers = csvRows[0].Split(',');
    // Adding columns name
    foreach (var item in headers)
        dataTable.Columns.Add(new DataColumn(item));
    string[] fields = null;
    foreach (string csvRow in csvRows)
    {
        fields = csvRow.Split(',');
        DataRow row = dataTable.NewRow();
        row.ItemArray = fields;
        dataTable.Rows.Add(row);
    }
    return dataTable;
}

当我试图用

比较我的两个数据表时，问题发生了:

ieDiff = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);

.xlsx文件获得的第一个数据表包含正确的值，而.csv文件获得的第二个数据表包含正确的值，但有一些额外的引号。

ex:

dt₁。行[10](数据表来自.csv)将包含:

Item1
第二条
"。"
"Item4"

dt2。行[10](来自.xlsx的数据表)将包含:

Item1
第二条
。
Item4

我知道我可以删除所有这些，但是肯定有更好的方法来执行它吗?

将.csv转换为数据表时额外的引号

最简单的答案是您在问题中提到的，即修剪引号字符。

我的测试代码:

var test = new[] { "Item1", "Item2", "'".'"", "'"Item4'"" };
foreach (var s in test)
{
    textBox1.AppendTextAddNewLine(s.Trim('"'));
}

输出结果:

Item1
Item2
.
Item4

我在下面写了5个方法来把Csv文件变成DataTable

它们被设计成考虑可选的引号(例如:"符号)，并且在不使用其他库的情况下尽可能通用:

    public static DataTable GetDataTabletFromCSVFile(string filePath, bool isHeadings)
    {
        DataTable MethodResult = null;
        try
        {
            using (TextFieldParser TextFieldParser = new TextFieldParser(filePath))
            {
                if (isHeadings)
                {
                    MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);
                }
                else
                {
                    MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);
                }
            }
        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }
    public static DataTable GetDataTableFromCsvString(string csvBody, bool isHeadings)
    {
        DataTable MethodResult = null;
        try
        {
            MemoryStream MemoryStream = new MemoryStream();

            StreamWriter StreamWriter = new StreamWriter(MemoryStream);
            StreamWriter.Write(csvBody);
            StreamWriter.Flush();

            MemoryStream.Position = 0;

            using (TextFieldParser TextFieldParser = new TextFieldParser(MemoryStream))
            {
                if (isHeadings)
                {
                    MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);
                }
                else
                {
                    MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);
                }
            }
        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }
    public static DataTable GetDataTableFromRemoteCsv(string url, bool isHeadings)
    {
        DataTable MethodResult = null;
        try
        {
            HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(url);
            HttpWebResponse httpWebResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            StreamReader StreamReader = new StreamReader(httpWebResponse.GetResponseStream());
            using (TextFieldParser TextFieldParser = new TextFieldParser(StreamReader))
            {
                if (isHeadings)
                {
                    MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);
                }
                else
                {
                    MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);
                }
            }
        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }

    private static DataTable GetDataTableFromTextFieldParser(TextFieldParser textFieldParser)
    {
        DataTable MethodResult = null;
        try
        {
            textFieldParser.SetDelimiters(new string[] { "," });
            textFieldParser.HasFieldsEnclosedInQuotes = true;

            string[] ColumnFields = textFieldParser.ReadFields();
            DataTable dt = new DataTable();
            foreach (string ColumnField in ColumnFields)
            {
                DataColumn DataColumn = new DataColumn(ColumnField);
                DataColumn.AllowDBNull = true;
                dt.Columns.Add(DataColumn);
            }

            while (!textFieldParser.EndOfData)
            {
                string[] Fields = textFieldParser.ReadFields();

                for (int i = 0; i < Fields.Length; i++)
                {
                    if (Fields[i] == "")
                    {
                        Fields[i] = null;
                    }
                }
                dt.Rows.Add(Fields);
            }
            MethodResult = dt;
        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }
    private static DataTable GetDataTableFromTextFieldParserNoHeadings(TextFieldParser textFieldParser)
    {
        DataTable MethodResult = null;
        try
        {
            textFieldParser.SetDelimiters(new string[] { "," });
            textFieldParser.HasFieldsEnclosedInQuotes = true;
            bool FirstPass = true;
            DataTable dt = new DataTable();
            while (!textFieldParser.EndOfData)
            {
                string[] Fields = textFieldParser.ReadFields();
                if(FirstPass)
                {
                    for (int i = 0; i < Fields.Length; i++)
                    {
                        DataColumn DataColumn = new DataColumn("Column " + i);
                        DataColumn.AllowDBNull = true;
                        dt.Columns.Add(DataColumn);
                    }
                    FirstPass = false;
                }
                for (int i = 0; i < Fields.Length; i++)
                {
                    if (Fields[i] == "")
                    {
                        Fields[i] = null;
                    }
                }
                dt.Rows.Add(Fields);
            }
            MethodResult = dt;
        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }

如果，像我一样，你正在从报告服务中保存，那么你应该这样使用:

    Warning[] warnings;
    string[] streamids;
    string mimeType;
    string encoding;
    string filenameExtension;
    byte[] bytes = rvMain.ServerReport.Render("csv", null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
    string CsvBody = System.Text.Encoding.UTF8.GetString(bytes);
    DataTable dt = GetDataTableFromCsvString(CsvBody,true);

否则，你所需要做的就是:

    bool IsHeadings = true; //Does the data include a heading row?
    DataTable dt = GetDataTableFromCsvString(CsvBody, IsHeadings);

或者直接从csv文件中使用

    bool IsHeadings = true; //Does the data include a heading row?
    DataTable dt = GetDataTabletFromCsvFile(FilePath, IsHeadings)

或者使用远程存储的csv文件

    bool IsHeadings = true; //Does the data include a heading row?
    DataTable dt = GetDataTabletFromRemoteCsv(Url, IsHeadings)