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

本文关键字：sql server 文件 何使用 CSV | 更新日期: 2023-09-27 18:26:16

当我尝试使用C#将csv文件上传到SQL Server表时，收到了以下错误消息（csv文件没有头）。错误消息："名为"的列已属于此DataTable"

我试着在网上找到一些解决方案，但我真的被它卡住了。我的代码：

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();

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

我认为这个链接将帮助您完成这项工作。

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);
                     }
            }
        }
    }
}

CsvHelper NuGet库有一个IDataReader的实现，您可以将其传递到BulkCopy WriteToServer方法中。这使得代码非常简单，并允许您自定义数据导入。

using CsvHelper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Globalization;
using System.IO;
public int ReplaceTableWithFile(string table, FileInfo csvFile)
{
    using var fileReader = new StreamReader(csvFile.OpenRead());
    using var csv = new CsvReader(fileReader, CultureInfo.InvariantCulture);
    using var csvDataReader = new CsvDataReader(csv);
    var connection = GetDbConnection();
    using var command = new SqlBulkCopy(connection);
    command.EnableStreaming = true;
    command.DestinationTableName = table;
    command.WriteToServer(csvDataReader);
    return command.RowsCopied;
}

CsvHelper NuGet包中的CsvDataReader

相关文章：