如何读取以逗号分隔的CSV文件，除非它是字段的一部分

小编典典

如何读取以逗号分隔的CSV文件，除非它是字段的一部分

sql

我有以下C＃代码，可读取CSV文件，目标是将其保存到SQL表中：

StreamReader sr = new StreamReader(tbCSVFileLocation.Text.ToString());
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
DataRow row;

foreach (string dc in value)
{
  dt.Columns.Add(new DataColumn(dc));
}

while (!sr.EndOfStream)
{
  value = sr.ReadLine().Split(',');
  if (value.Length == dt.Columns.Count)
  {
    row = dt.NewRow();
    row.ItemArray = value;
    dt.Rows.Add(row);
  }
}

我遇到的问题是我不知道数据从哪里来。

这是CSV文件的示例：

Name,Address,License Number,License Type,Year of Birth,Effective Date,Action,Misconduct Description,Date Updated "563 Grand Medical, P.C.","563 Grand Street Brooklyn, NY 11211",196275,,,09/29/2010,Revocation of certificate of incorporation.,"The corporation admitted guilt to the charge of ordering excessive tests, treatment, or use of treatment facilities not warranted by the condition of a patient.",09/29/2010 "Aaron, Joseph","2803 North 700 East Provo, Utah 84604",072800,MD,1927,01/13/1999,License Surrender,"This action modifies the penalty previously imposed by Order# 93-40 on March 31, 1993, where the Hearing Committee sustained the charge that the physician was disciplined by the Utah State Medical Board, and ordered that if he intends to engage in practice in NY State, a two-year period of probation shall be imposed.", "Aarons, Mark Gold","P.O.Box 845 Southern Pines, North Carolina 28388",161530,MD,1958,12/13/2005,"License limited until the physician's North Carolina medical license is fully restored without any conditions.The physician must also comply with the terms imposed on July 26, 2005 by the North Carolina State Medical Board. The physician has completed the monitoring terms.",The physician did not contest the charge of having been disciplined by the North Carolina State Medical Board for his addiction to drugs.,12/06/2005

当我查看我的SQL表时，将显示以下内容：

Name    Address License Number  License Type    Year of Birth   Effective Date  Action  Misconduct Description  Date Updated                    
Orlando  FL 32836"  173309  MD  1938    2/29/2012   License surrender   The physician did not contest the charge of having had his DEA registration for Florida revoked by the U.S. Drug Enforcement Administration for improperly prescribing controlled substances.   2/22/2012                   
Miami    Florida 33156" 119545  MD  1945    10/10/2002  Censure and reprimand   The physician did not contest the charge of having been disciplined by the Florida State Board of Medicine for giving a patient excessive doses of radiation.   10/10/2002                  
Brooklyn     New York 11229"    192310          11/6/2003   Annulment of certificate of incorporation pursuant to Section 230-a of the New York State Public Health Law and Section 1503(d) of the New York State Business Corporation Law  The corporation admitted guilt to the charge of willfully failing to comply with Section 1503 of the Business Corporation Law in violation of New York State Education Law Section 6530(12).    10/31/2003

如您所见，第一行的第一列没有ORLANDO。不知道发生了什么。

请帮我解决。

阅读 196

2021-04-15

共1个答案

小编典典

一些有助于您入门Debugger的代码。也可以使用来遍历代码

声明一个受保护的静态DataTable csvData并将其初始分配为null

protected static DataTable csvData = null; // declared up top in your class
csvData = GetDataTabletFromCSVFile(fileName); //Converts the CSV File into a DataTable

private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
    csvData = new DataTable(defaultTableName);
    try
    {
        using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
        {
            csvReader.SetDelimiters(new string[]
            {
                tableDelim 
            });
            csvReader.HasFieldsEnclosedInQuotes = true;
            string[] colFields = csvReader.ReadFields();
            foreach (string column in colFields)
            {
                DataColumn datecolumn = new DataColumn(column);
                datecolumn.AllowDBNull = true;
                csvData.Columns.Add(datecolumn);
            }

            while (!csvReader.EndOfData)
            {
                string[] fieldData = csvReader.ReadFields();
                //Making empty value as null
                for (int i = 0; i < fieldData.Length; i++)
                {
                    if (fieldData[i] == string.Empty)
                    {
                        fieldData[i] = string.Empty; //fieldData[i] = null
                    }
                    //Skip rows that have any csv header information or blank rows in them
                    if (fieldData[0].Contains("Disclaimer") || string.IsNullOrEmpty(fieldData[0]))
                    {
                        continue;
                    }
                }
                csvData.Rows.Add(fieldData);
            }
        }
    }
    catch (Exception ex)
    {
    }
    return csvData;
}

fieldData [0] .Contains（“
Disclaimer”）这是我的.csv文件中的列，因此请非常直接地阅读和理解逻辑，并根据需要进行更改以适合您的.csv文件

如果您想尝试一些更简单的操作，然后解析使用“快速监视”窗口时将获得的“ \”字符，请尝试以下操作

var lines = File.ReadLines("FilePath of Some .csv File").Select(a => a.Split(',')).ToArray();

2021-04-15