使用oledb,odbc读取csv,excel文件

/// <summary>
        /// 使用OLEDB读取excel和csv文件
        /// </summary>
        /// <param name="path">文件所在目录地址</param>
        /// <param name="name">文件名</param>
        /// <returns></returns>
        public static DataSet ReadFile(string path, string name)
        {
            if (string.IsNullOrWhiteSpace(path) || string.IsNullOrWhiteSpace(name) || !File.Exists(path+name))
                return null;
            // 读取excel
            string connstring = string.Empty;
            string strSql = string.Empty;
            if (name.EndsWith(".xls") || name.EndsWith(".xlsx"))
            {
                connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + name + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
                strSql = "select * from [sheet1$]";
            }
            // 读取csv文件
            else if (name.EndsWith(".csv"))
            {
                connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='text;HDR=YES;FMT=Delimited';";
                strSql = "select * from " + name;
            }
            else
            {
                return null;
            }
            DataSet ds = null;
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(connstring);
                conn.Open();
                OleDbDataAdapter myCommand = null;

                myCommand = new OleDbDataAdapter(strSql, connstring);
                ds = new DataSet();
                myCommand.Fill(ds, "table1");
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }

 

--2

/// <summary>
        /// 将CSV转化为DataTable 使用odbc读取excel和csv文件
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>

public static DataTable CSVToDataTable(string strFileFullPath)
        {
            if (string.IsNullOrEmpty(strFileFullPath))
            {
                return null;
            }
            DataTable dt = new DataTable();
            try
            {
                //得到路径
                string strFilePath = Path.GetDirectoryName(strFileFullPath);
                //得到文件名
                string strFileName = Path.GetFileNameWithoutExtension(strFileFullPath);
                //读取CSV文件
                string connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strFilePath + ";Extensions=asc,csv,tab,txt;";
                using (OdbcConnection odbcConn = new OdbcConnection(connString))
                {
                    odbcConn.Open();
                    OdbcCommand oleComm = new OdbcCommand();
                    oleComm.Connection = odbcConn;
                    oleComm.CommandText = "select * from [" + strFileName + "#csv]";
                    OdbcDataAdapter adapter = new OdbcDataAdapter(oleComm);
                    adapter.Fill(dt);
                    dt.TableName = strFileName;
                    odbcConn.Close();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }