程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> Visual Basic語言 >> VB.NET >> 應用VisualBasic中TextFieldParser解析器把CSV格式倒入數據庫

應用VisualBasic中TextFieldParser解析器把CSV格式倒入數據庫

編輯:VB.NET

應用VisualBasic中TextFieldParser解析器把CSV格式倒入數據庫。本站提示廣大學習愛好者:(應用VisualBasic中TextFieldParser解析器把CSV格式倒入數據庫)文章只能為提供參考,不一定能成為您想要的結果。以下是應用VisualBasic中TextFieldParser解析器把CSV格式倒入數據庫正文


寫了個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();
    
        }
    }
}

作者:cnblogs 木宛城主

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved