利用Microsoft.VisualBasic中TextFieldParser解析器把CSV格式倒入数据库
利用Microsoft.VisualBasic中TextFieldParser解析器把CSV格式倒入数据库
木宛城主 发表于3年前
利用Microsoft.VisualBasic中TextFieldParser解析器把CSV格式倒入数据库
  • 发表于 3年前
  • 阅读 1
  • 收藏 0
  • 点赞 0
  • 评论 0

【腾讯云】如何购买服务器最划算?>>>   

写了个Demo,利用Microsoft.VisualBasic这个程序集中的TextFieldParser解析器解析CSV格式的文件,然后将解析的数据插入到相关表,这样的好处是不用去用令人头疼的ODBC去操作CSV格式文件,如之前是这样去操作:

利用ODBC去操作

string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False"; string sql_select; OdbcConnection conn; conn = new OdbcConnection(strConnString.Trim()); conn.Open(); OdbcCommand commandRowCount = new OdbcCommand("SELECT COUNT(*) FROM [" + this.FileNevCSV.Trim() + "]", conn); this.rowCount = System.Convert.ToInt32(commandRowCount.ExecuteScalar()); sql_select = "select * from [" + this.FileNevCSV.Trim() + "]"; OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn); OdbcDataReader dataReader = commandSourceData.ExecuteReader(); DataTable dt; dt = dataReader.GetSchemaTable();

利用TextFieldParser操作

namespace ImportCSV { class Program { //连接字符串
        private static readonly string connStr = @"Data Source=BEAR\EYESSQLSERVER;Initial Catalog=Test;Integrated Security=True"; //表明,最好做成是客配置,如Winform程序下拉框
        private static string tableName = "Customer"; /// <summary>
        /// 执行查询,返回DataTable数据源 /// </summary>
        /// <param name="connStr"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static DataTable ExecuteDataTable(string connStr,string cmdText,params SqlParameter[] parameters) { using (SqlConnection conn=new SqlConnection(connStr)) { using (SqlCommand cmd=conn.CreateCommand()) { cmd.CommandText = cmdText; cmd.Parameters.AddRange(parameters); using (SqlDataAdapter adapter=new SqlDataAdapter(cmd)) { DataTable dataTable=new DataTable(); adapter.Fill(dataTable); return dataTable; } } } } /// <summary>
        /// 得到主键列 /// </summary>
        /// <returns></returns>
        static List<string> GetKeyWords() { //SQL Server 系统试图得到主键列
            string sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@TABLE_NAME"; DataTable dt=ExecuteDataTable(connStr, sql, new SqlParameter("TABLE_NAME", tableName)); List<string> listKeyWords=new List<string>(); foreach (DataRow row in dt.Rows) { string keyWord = Convert.ToString(row["COLUMN_NAME"]); listKeyWords.Add(keyWord); } return listKeyWords; } /// <summary>
        /// 得到指定表所有的列 /// </summary>
        /// <returns></returns>
        static List<string> GetAllColumns() { //系统视图得到所有列
            string sql = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME"; DataTable dt = ExecuteDataTable(connStr, sql, new SqlParameter("TABLE_NAME", tableName)); List<string> listCols = new List<string>(); foreach (DataRow row in dt.Rows) { string columnName = Convert.ToString(row["COLUMN_NAME"]); listCols.Add(columnName); } return listCols; } /// <summary>
        /// 得到初主键外所有列 /// </summary>
        /// <returns></returns>
        static List<string> GetAllColumnsWithoutKeyWords() { List<string> listAllColumns = GetAllColumns(); List<string> listKeyWords= GetKeyWords(); return listAllColumns.Except(listKeyWords).ToList(); } /// <summary>
        /// 得到除Identity(标识)外所有列 /// </summary>
        /// <returns></returns>
        static List<string> GetAllColumnsWithoutIdentity() { //得到Identity标志列
            string sql = @"select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and TABLE_NAME=@TABLE_NAME"; DataTable dt = ExecuteDataTable(connStr, sql, new SqlParameter("TABLE_NAME", tableName)); List<string> listColumnsWithoutIdentity = new List<string>(); foreach (DataRow row in dt.Rows) { listColumnsWithoutIdentity.Add(Convert.ToString(row["COLUMN_NAME"])); } List<string> listAllColumns = GetAllColumns(); //从所有列中排除
            return listAllColumns.Except(listColumnsWithoutIdentity).ToList(); } static void Main(string[] args) { using (SqlConnection connection = new SqlConnection(connStr)) { using (SqlCommand cmd = connection.CreateCommand()) { //得到所有列除Identity标志列
                    string[] columnsWithoutIdentity = GetAllColumnsWithoutIdentity().ToArray(); //SQL参数
                    string[] columnsParameters = (from c in columnsWithoutIdentity select "@" + c).ToArray(); StringBuilder sb=new StringBuilder(); //拼接Insert SQL语句
                    sb.AppendLine("insert into " + tableName + "(" + string.Join(",", columnsWithoutIdentity) +
                                  ") output inserted.id values(" + string.Join(",",columnsParameters)+ ")"); cmd.CommandText = sb.ToString(); //从路径得到csv的文件,可以做成打开框
                    using (var myCsvFile = new TextFieldParser(@"C:\Users\eyeswang\Desktop\xxxx.csv",Encoding.Default)) { myCsvFile.TextFieldType = FieldType.Delimited; myCsvFile.SetDelimiters(",");//设置解析器分割符
 connection.Open(); //循环,一行一行读
                        while (!myCsvFile.EndOfData) { string[] fieldArray; try { //读取一行
                                fieldArray = myCsvFile.ReadFields(); for (int i = 0; i < fieldArray.Count(); i++) { //给参数赋值,如果是NULL,则DBNULL.Value插入相关列
                                    cmd.Parameters.Add("@" + columnsWithoutIdentity[i], fieldArray[i].ToUpper() == "NULL" ? (object)DBNull.Value : fieldArray[i]); } //执行完毕后记得Parameters Clear
 cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex) { continue; } } } } } Console.WriteLine("OK"); Console.ReadKey(); } } }

 

共有 人打赏支持
粉丝 1
博文 222
码字总数 199010
×
木宛城主
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: