c# - 如何使用 C# 将 CSV 文件上传到 sql server 表？

Question

当我尝试使用 C# 将 csv 文件上传到 SQL Server 表时收到以下错误消息(csv 文件没有标题)。错误消息:“名为 ' ' 的列已属于此数据表”

我试图在网络上的某个地方找到一些解决方案，但我真的坚持下去。我的代码:

SqlConnection con = new SqlConnection(@"server=.;Initial Catalog=myDtabase;Integrated Security=SSPI;");

            string filepath = @"c:\\my_CSV_file.csv";

            StreamReader sr = new StreamReader(filepath);

            string line = sr.ReadLine();
            string[] value = line.Split(',');
            DataTable dt = new DataTable();
            DataRow row;

            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while (!sr.EndOfStream)
            {
                value = sr.ReadLine().Split(',');
                if (value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();
                    row.ItemArray = value;
                    dt.Rows.Add(row);
                }
            }

            SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
            bc.DestinationTableName = "my_SQLServer_Table";
            bc.BatchSize = dt.Rows.Count;
            con.Open();
            bc.WriteToServer(dt);
            bc.Close();
            con.Close();

Answer 1

我认为此链接将帮助您完成此操作。

http://forums.asp.net/t/1695615.aspx

像往常一样，剥猫皮的方法不止一种。所以，如果您不喜欢上面列出的解决方案，请试试这个脚本，我知道它对您有用。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string server = "EXCEL-PC\\EXCELDEVELOPER";
            string database = "AdventureWorksLT2012";
            string SQLServerConnectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, database);


            string CSVpath = @"C:\Users\Ryan\Documents\Visual Studio 2010\Projects\Bulk Copy from CSV to SQL Server Table\WindowsFormsApplication1\bin"; // CSV file Path
            string CSVFileConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};;Extended Properties=\"text;HDR=Yes;FMT=Delimited\";", CSVpath);

            var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
            string File_Name = string.Empty;

            foreach (var file in AllFiles)
            {
                try
                {
                    DataTable dt = new DataTable();
                    using (OleDbConnection con = new OleDbConnection(CSVFileConnectionString))
                    {
                        con.Open();
                        var csvQuery = string.Format("select * from [{0}]", file.Name);
                        using (OleDbDataAdapter da = new OleDbDataAdapter(csvQuery, con))
                        {
                            da.Fill(dt);
                        }
                    }

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLServerConnectionString))
                    {
                        bulkCopy.ColumnMappings.Add(0, "MyGroup");
                        bulkCopy.ColumnMappings.Add(1, "ID");
                        bulkCopy.ColumnMappings.Add(2, "Name");
                        bulkCopy.ColumnMappings.Add(3, "Address");
                        bulkCopy.ColumnMappings.Add(4, "Country");
                        bulkCopy.DestinationTableName = "AllEmployees";
                        bulkCopy.BatchSize = 0;
                        bulkCopy.WriteToServer(dt);
                        bulkCopy.Close();
                    }

                }
                catch(Exception ex)
                     {
                         MessageBox.Show(ex.Message, "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                     }
            }
        }
    }
}