C#操作CSV(保存到DataTable、获取标题)

 说明:1、读取CSV文件内容到DataTable中,2、只湖区CSV文件的标题。

方法一:  保存到DataTable并显示页面(OLEDB读取方式)

       #region 读取Csv文件
        public static DataTable getCsvData(string pCsvpath, string pCsvname)
        {
            try
            {
                DataSet dsCsvData = new DataSet();

                OleDbConnection OleCon = new OleDbConnection();
                OleDbCommand OleCmd = new OleDbCommand();
                OleDbDataAdapter OleDa = new OleDbDataAdapter();

                OleCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pCsvpath + ";Extended Properties='Text;FMT=Delimited(,);HDR=YES;IMEX=1';";
                OleCon.Open();
                DataTable dts1 = OleCon.GetSchema("Tables");
                DataTable dts2 = OleCon.GetSchema("Columns");
                OleCmd.Connection = OleCon;
                OleCmd.CommandText = "select * from [" + pCsvname + "] where 1=1";
                OleDa.SelectCommand = OleCmd;
                OleDa.Fill(dsCsvData, "Csv");
                OleCon.Close();

                return dsCsvData.Tables[0];
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

方法二:保存到DataTable并显示页面(OLEDB读取方式)

private void button5_Click(object sender, EventArgs e)
        {
            string fileName = textBox1.Text; //文件路径
            DataSet  ds = new DataSet();
            OleDbConnection csvConn = new OleDbConnection();//Jet
            try
            {
                if (Path.GetExtension(fileName).Equals(".csv"))
                {
                    csvConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName.Substring(0, fileName.LastIndexOf('\\')) + ";Extended Properties='Text;FMT=Delimited;HDR=YES;IMEX=1;ImportMixedTypes=Text'";
                }
                else
                {
                    csvConn.ConnectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + Path.GetDirectoryName(fileName) + "";
                }
                csvConn.Open();
                OleDbCommand oleCmd = new OleDbCommand();
                oleCmd.Connection = csvConn;
                oleCmd.CommandText = "select * from " + Path.GetFileName(fileName) + " where 1=1 ";
                OleDbDataAdapter oleDa = new OleDbDataAdapter();
                oleDa.SelectCommand = oleCmd;
                oleDa.Fill(ds, Path.GetFileName(fileName));
                csvConn.Close();
                this.dataGridView1.DataSource = ds.Tables[0];
                GC.Collect(); 
            }
            catch (Exception vErr)
            {
                MessageBox.Show(vErr.Message);
            }
            finally
            {
                csvConn.Close();
                GC.Collect();//垃圾回收
            }
        }

方法三:读取CSV到DataTable中,并显示页面(StreamReader方式读取,这种方式,读取数据效率低下,但是数据完整)

        public void CSV()
        {
            string pCsvPath = "D:/auto.csv";//文件路径
            try
            {
                String line;
                String[] split = null;
                DataTable table = new DataTable();
                DataRow row = null;
                StreamReader sr = new StreamReader(pCsvPath, System.Text.Encoding.Default);
                //创建与数据源对应的数据列 
                line = sr.ReadLine();
                split = line.Split(',');
                foreach (String colname in split)
                {
                    table.Columns.Add(colname, System.Type.GetType("System.String"));
                }
                //将数据填入数据表 
                int j = 0;
                while ((line = sr.ReadLine()) != null)
                {
                    j = 0;
                    row = table.NewRow();
                    split = line.Split(',');
                    foreach (String colname in split)
                    {
                        row[j] = colname;
                        j++;
                    }
                    table.Rows.Add(row);
                }
                sr.Close();
                //显示数据 
                this.dataGridView1.DataSource = table.DefaultView;
            }
            catch (Exception vErr)
            {
                MessageBox.Show(vErr.Message);
            }
            finally
            {
                GC.Collect();
            }
        }

方法四:获取CSV标题,保存到数组中(OLEDB方式)

       public static string[] GetCsvHead(string pathcsv, string FileName)
        {
            try
            {
                OleDbConnection OleCon = new OleDbConnection();
                OleDbCommand OleCmd = new OleDbCommand();
                OleDbDataAdapter OleDa = new OleDbDataAdapter();
                DataSet dsCsvData = new DataSet();
                OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathcsv + ";Extended Properties='Text;FMT=Delimited(,);HDR=NO;IMEX=1';";
                OleCon.Open();
                DataTable dts1 = OleCon.GetSchema("Tables");
                DataTable dts2 = OleCon.GetSchema("Columns");
                OleCmd.Connection = OleCon;
                OleCmd.CommandText = "select top 1 * from [" + FileName + "]";
                OleDa.SelectCommand = OleCmd;
                OleDa.Fill(dsCsvData, "Csv");
                OleCon.Close();
                string[] CsvHeader;
                if (dsCsvData != null)
                {
                    CsvHeader = new string[dsCsvData.Tables[0].Columns.Count];
                    DataRow row = dsCsvData.Tables[0].Rows[0];
                    for (int i = 0; i < CsvHeader.Length; i++)
                    {
                        CsvHeader[i] = row[i].ToString();
                    }
                    return CsvHeader;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception vErr)
            {
                MessageBox.Show(vErr.Message);
                return null;
            }
            finally
            {
                GC.Collect();
            }
        }

方法五:获取CSV标题,保存到数组中(StreamReader方式)

private void button4_Click(object sender, EventArgs e)
        {
            string strpath = textBox1.Text;//文件的路径
            try
            {
                int intColCount = 0;
                bool blnFlag = true;
                DataColumn mydc;
                DataRow mydr;
                string strline;
                string[] aryline;
                DataTable mycsvdt = new DataTable();

                StreamReader mysr = new StreamReader(strpath, System.Text.Encoding.Default);
                if ((strline = mysr.ReadLine()) != null)
                {
                    aryline = strline.Split(new char[] { ',' });
                    //给datatable加上列名
                    if (blnFlag)
                    {
                        blnFlag = false;
                        intColCount = aryline.Length;
                        int col = 0;
                        for (int i = 0; i < aryline.Length; i++)
                        {
                            col = i + 1;
                            mydc = new DataColumn(col.ToString());
                            mycsvdt.Columns.Add(mydc);
                        }
                    }
                    //填充数据并加入到datatable中
                    mydr = mycsvdt.NewRow();
                    for (int i = 0; i < intColCount; i++)
                    {
                        mydr[i] = aryline[i];
                    }
                    mycsvdt.Rows.Add(mydr);
                }
                this.dataGridView1.DataSource = mycsvdt;
            }
            catch (Exception vErr)
            {
                MessageBox.Show(vErr.Message);
            }
            finally
            {
                GC.Collect();
            }
        }

方法六:获取CSV标题,保存到数组中(OLEDB方式)

private void button7_Click(object sender, EventArgs e)
        {
            string fileName = textBox1.Text;  //csv 文件路径
            string[] fileTile;
            DataSet ds = new DataSet();
            OleDbConnection csvConn = new OleDbConnection();
            try
            {
                if (Path.GetExtension(fileName).Equals(".csv"))
                {
                    csvConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName.Substring(0, fileName.LastIndexOf('\\')) + ";Extended Properties='Text;FMT=Delimited;HDR=YES;IMEX=1;ImportMixedTypes=Text'";
                }
                else
                {
                    csvConn.ConnectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + Path.GetDirectoryName(fileName) + "";
                }
                csvConn.Open();
                OleDbCommand oleCmd = new OleDbCommand();
                oleCmd.Connection = csvConn;
                oleCmd.CommandText = "select * from " + Path.GetFileName(fileName) + " where 1<>1 ";
                OleDbDataAdapter oleDa = new OleDbDataAdapter();
                oleDa.SelectCommand = oleCmd;
                oleDa.Fill(ds, Path.GetFileName(fileName));
                csvConn.Close();
                GC.Collect();

                fileTile = new string[ds.Tables[0].Columns.Count]; //保存csv文件标题
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    fileTile[i] = ds.Tables[0].Columns[i].Caption.ToString();
                } 
            }
            catch (Exception vErr)
            {
                MessageBox.Show(vErr.Message);
            }
            finally
            {
                csvConn.Close();
                GC.Collect();//垃圾回收
            }
        }

方法六:读取csv到DataTable(streamReader的方式)这个方法对双引号,字段内的分号,进行了处理,可以正常读处理。

private void button2_Click(object sender, EventArgs e)
        {
            string pCsvPath = textBox1.Text;
            String line;
            String[] split = null;
            DataTable table = new DataTable();
            DataRow row = null;
            StreamReader sr = new StreamReader(pCsvPath, System.Text.Encoding.Default);
            //创建与数据源对应的数据列 
            line = sr.ReadLine();
            if (string.IsNullOrEmpty(line))
            {
                return;
            }
            split = line.Split(',');
            for (int i = 0; i < split.Length; i++)
            {
                table.Columns.Add(split[i], System.Type.GetType("System.String"));
            }
            //将数据填入数据表 
            while (!string.IsNullOrEmpty(line = sr.ReadLine()))
            {
                row = table.NewRow();
                //split = line.Split(',');

                string[][] asplit = read_csv(line);
                
                if (table.Columns.Count >= asplit[0].Length)
                {
                    for (int p = 0; p < asplit[0].Length; p++)
                    {
                        row[p] = asplit[0][p];
                    }
                    table.Rows.Add(row);
                    continue;
                }
                if (table.Columns.Count < asplit.Length)
                {
                    for (int p = 0; p < table.Columns.Count; p++)
                    {
                        row[p] = asplit[p];
                    }
                    table.Rows.Add(row);
                    continue;
                }
            }
            sr.Close();
            //显示数据 
            this.dataGridView1.DataSource = table.DefaultView;

        }
        public string[][] read_csv(string text)
        {
            if (text == null)
            {
                return null;
            }
            var text_array = new List<string[]>();
            var line = new List<string>();
            var field = new StringBuilder();
            //是否在双引号内
            bool in_quata = false;
            //字段是否开始
            bool field_start = true;
            for (int i = 0; i < text.Length; i++)
            {
                char ch = text[i];
                if (in_quata)
                {
                    //如果已经处于双引号范围内
                    if (ch == '\"')
                    {
                        //如果是两个引号,则当成一个普通的引号处理
                        if (i < text.Length - 1 && text[i + 1] == '\"')
                        {
                            field.Append('\"');
                            i++;
                        }
                        else
                        {
                            //否则退出引号范围
                            in_quata = false;
                        }
                    }
                    else //双引号范围内的任何字符(除了双引号)都当成普通字符
                    {
                        field.Append(ch);
                    }
                }
                else
                {
                    switch (ch)
                    {
                        #region

                        case ',': //新的字段开始
                            line.Add(field.ToString());
                            field.Remove(0, field.Length);
                            field_start = true;
                            break;
                        case '\"': //引号的处理
                            if (field_start)
                            {
                                in_quata = true;
                            }
                            else
                            {
                                field.Append(ch);
                            }
                            break;
                        case '\r': //新的记录行开始
                            if (field.Length > 0 || field_start)
                            {
                                line.Add(field.ToString());
                                field.Remove(0, field.Length);
                            }
                            text_array.Add(line.ToArray());
                            line.Clear();
                            field_start = true;
                            //在 window 环境下,\r\n通常是成对出现,所以要跳过
                            if (i < text.Length - 1 && text[i + 1] == '\n')
                            {
                                i++;
                            }
                            break;
                        default:
                            field_start = false;
                            field.Append(ch);
                            break;

                        #endregion
                    }
                }
            }
            //文件结束
            if (field.Length > 0 || field_start)
            {
                line.Add(field.ToString());
            }
            if (line.Count > 0)
            {
                text_array.Add(line.ToArray());
            }
            return text_array.ToArray();
        }

  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值