将第二行作为CSV中的标头列将其设置为DataTable转换

Question

我正在尝试读取CSV文件并在C＃中的DataTable中获取数据。

我使用了以下代码，该代码正常：

public static System.Data.DataTable GetcsvExcelRecords(string csvPath, string CSVFileName)
        {
            string[] workSheetNames = new string[] { };  
            System.Data.DataTable dtExcelData = new System.Data.DataTable();
            string SourceConstr  = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + csvPath + "';Extended Properties='Text;HDR=Yes;FMT=Delimited;'";
            using (OleDbConnection excelConn = new OleDbConnection(SourceConstr))
            {
                excelConn.Open();
                OleDbCommand excelCommand = new OleDbCommand();
                OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
                excelCommand = new OleDbCommand("SELECT * FROM [" + CSVFileName + "]", excelConn);
                excelDataAdapter.SelectCommand = excelCommand;
                excelDataAdapter.Fill(dtExcelData);
                return dtExcelData;
            }
        }

但是，我希望第二行数据是标题列，然后再进行行。如何实现这一目标？谢谢

Answer 1

您宁愿使用现有库而不是重新发明(重新实现(...我听说FileHelpers可能有所使用。

在任何情况下，我们都喜欢在意识到需要库之前尝试一些东西。在这种情况下...

如果CSV文件大小很小，您可以尝试使用类似的肮脏的东西...但是这听起来很琐碎。请注意，需要在磁盘上使用oledbconnection ...不要忘记删除创建的文件...

public static System.Data.DataTable GetcsvExcelRecords(string csvPath, string CSVFileName)
{
    string modCSVFileName = CreateFileRemovingFirstLine(csvPath, CSVFileName);
    string[] workSheetNames = new string[] { };
    System.Data.DataTable dtExcelData = new System.Data.DataTable();
    string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + csvPath + "';Extended Properties='Text;HDR=Yes;FMT=Delimited;'";
    using (OleDbConnection excelConn = new OleDbConnection(SourceConstr))
    {
        excelConn.Open();
        OleDbCommand excelCommand = new OleDbCommand();
        OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
        excelCommand = new OleDbCommand("SELECT * FROM [" + modCSVFileName + "]", excelConn);
        excelDataAdapter.SelectCommand = excelCommand;
        excelDataAdapter.Fill(dtExcelData);
        return dtExcelData;
    }
}
private static string CreateFileRemovingFirstLine(string csvPath, string CSVFileName)
{
    string filePath = Path.Combine(csvPath, CSVFileName);
    string modifiedFileName = CSVFileName.Replace(".csv", "2.csv");
    using (StreamReader sr = new StreamReader(filePath))
    {
        string headerLine = sr.ReadLine();
        string str = sr.ReadToEnd();
        // use the appropriate encoding... for example I am using ASCII
        byte[] buffer = Encoding.ASCII.GetBytes(str);
        MemoryStream ms = new MemoryStream(buffer);
        // write to file... check for access permission to create file
        FileStream file = new FileStream(Path.Combine(csvPath, modifiedFileName), FileMode.Create, FileAccess.Write);
        ms.WriteTo(file);
        file.Close();
        ms.Close();
    }
    return modifiedFileName;
}