Nhập nhiều tệp excel vào máy chủ sql

Tôi được yêu cầu nhập dữ liệu vào SQL từ nhiều (15) tệp Excel và mỗi tệp Excel chứa dữ liệu lịch sử với hơn 200.000 hàng

Tôi có thể làm điều đó với 15 luồng dữ liệu và kết nối Excel trong SSIS, nhưng tôi không muốn làm theo cách đó vì tôi muốn tìm một cách hiệu quả hơn là tạo 15 luồng dữ liệu và kết nối Excel. Tôi quá lười để tạo tất cả các luồng dữ liệu và kết nối Excel đó. Điều đó sẽ mất rất nhiều thời gian. Vì vậy, tôi Googled nó

Tôi đã tìm thấy nhiều bài báo, nhưng mỗi bài báo hơi khác nhau và sử dụng một cách tiếp cận khác để xử lý tình huống

Tôi đã làm theo các bước trong một số bài viết và video trên YouTube, nhưng tôi không gặp may. Mỗi khi có điều gì đó không ổn hoặc tôi đã bỏ lỡ điều gì đó. tôi gãi đầu

Sau một thời gian, tôi đã tìm ra cách để làm cho nó hoạt động và tôi muốn chia sẻ nó với mọi người để tiết kiệm thời gian của bạn

Dưới đây là hướng dẫn từng bước của tôi để tạo gói SSIS để nhập dữ liệu từ nhiều tệp Excel sang SQL chỉ với một luồng dữ liệu và một kết nối Excel

Ví dụ: tôi có hai tệp Excel tại C. \BI\SSIS\Nhập dữ liệu lịch sử\Tệp nguồn\Kiểm tra

Lưu ý rất quan trọng

Đảm bảo rằng mỗi tệp Excel có cùng một lược đồ

Ví dụ,

1. Số cột
2. Tên của tiêu đề cột
3. Tên của các tab

Cách nhập nhiều tệp Excel vào SQL

1. Mở Visual Studio
2. Tạo một dự án mới

1. Chọn Dự án Dịch vụ Tích hợp

1. Tạo một biến

• Tên. Đường dẫn tập tin
• Loại dữ liệu. Sợi dây
• Giá trị. trống

1. Kéo và thả Bộ chứa vòng lặp Foreach vào Luồng điều khiển

1. Nhấp chuột phải vào Vùng chứa vòng lặp Foreach sau đó nhấp vào Chỉnh sửa

1. Vào Bộ sưu tập > Nhập đường dẫn thư mục > Nhập phần mở rộng tệp

Đảm bảo rằng Trình liệt kê là “Foreach File Enumerator” và Truy xuất tên tệp được đặt thành “Đủ điều kiện”

Trong loạt bài hướng dẫn (xem tài liệu tham khảo ở phía dưới), tôi đã trình bày cách bạn có thể xuất dữ liệu từ cơ sở dữ liệu SQL sang tệp Excel. Trong bài viết này tôi sẽ trình bày cách chúng ta có thể nhập dữ liệu từ tệp Excel vào bảng đích trong cơ sở dữ liệu SQL. Tôi sẽ sử dụng Microsoft SQL Server 2019, Visual Studio 2019 và Microsoft Excel 365. Một số mã C# sẽ không hoạt động nếu bạn không sử dụng. MẠNG 4. 8 hoặc muộn hơn

Dung dịch

Nhập dữ liệu (hoặc tải dữ liệu) vào cơ sở dữ liệu SQL là một phần của quy trình ETL. Đôi khi điều này được thực hiện với gói SSIS, Trình hướng dẫn Nhập và Xuất Máy chủ SQL, OPENROWSET hoặc Máy chủ được Liên kết. Đối với quy trình này, các tệp Excel được sử dụng rộng rãi, thứ nhất vì cấu trúc của chúng tương tự như cơ sở dữ liệu (ví dụ: các trang tính Excel có thể được xem như các bảng cơ sở dữ liệu và các cột trong một trang tính có thể được xem như các cột bảng) và thứ hai vì nó rất dễ dàng.

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách nhập tệp Excel có nhiều trang tính vào cơ sở dữ liệu mới bằng bảng SQL

Cơ sở dữ liệu tôi sử dụng cho bài kiểm tra này có thể được tạo bởi các câu lệnh sau

IF NOT EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'dbtest')
    create database dbtest
go

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'dbtest')
begin
    exec('CREATE TABLE dbtest.dbo.[Company]
    (
        [CompanyCode] varchar(8) NOT NULL,
        [CompanyName] varchar(24) NULL,
        [BIC] varchar(11) NULL,
        [IBAN] varchar(35) NULL,
        CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
        (
            [CompanyCode]
        )
    )')

    exec('CREATE TABLE dbtest.dbo.[EmployeeDetails]
    (
        [EmployeeCode] varchar(8) NOT NULL,
        [Surname] varchar(25),
        [FirstName] varchar(50),
        [Address] varchar(50),
        CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED 
        (
            [EmployeeCode] 
        )
    )')

    exec('CREATE TABLE dbtest.dbo.[Employees]
    (
        [EmployeeCode] varchar(8) NOT NULL,
        [Director] bit,
        [GrossIncome] float,
        [BIC] varchar(11),
        [IBAN] varchar(34),
        CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
        (
            [EmployeeCode] 
        )
    )')
end

Chúng tôi có hai kết nối. một cho Máy chủ SQL và một cho tệp Excel

Chúng ta có thể sử dụng kết nối ODBC để kết nối với tệp Excel. Đối với điều này, chuỗi kết nối trông giống như

string extension = Path.GetExtension(_excelFile);
string connectionString = "";
switch(extension)
{
  // HDR=YES ==> we have headers and do not want them (ignore first row)
  case ".xls": //Excel 97 to 03
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
    break;
  case ".xlsx": //Excel 2007 ->
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES'";
    break;
}

connectionString = string.Format(connectionString, _excelFile); 

Như bạn có thể thấy, chúng tôi có các nhà cung cấp khác nhau để kết nối với tệp Excel, tùy thuộc vào phiên bản tệp Excel. Các tệp Excel cũ hơn được tạo bằng Excel cho đến phiên bản 2003 sử dụng công cụ Jet và các tệp Excel mới hơn sử dụng công cụ ACE. Tôi cho rằng hiện tại hầu hết các tệp Excel được tạo bằng phiên bản Excel mới hơn

Một điều khác cần đề cập là HDR=YES như một thuộc tính mở rộng trong chuỗi kết nối. Điều này nói rằng hàng đầu tiên trong tệp Excel được sử dụng làm tiêu đề cho các cột, vì vậy chúng tôi có thể cung cấp cho mỗi cột một tiêu đề, như trong hình dưới đây

Tên tiêu đề cột không nhất thiết phải giống với tên cột của bảng trong cơ sở dữ liệu nhưng nó có hữu ích không

Đọc trang tính Excel là bước tiếp theo, có thể được thực hiện bằng đoạn mã sau

DataTable dt = null;
using OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
List excelSheets = new List();

// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
    string sheet = row["TABLE_NAME"].ToString();
    if(sheet.StartsWith("'"))
        sheet = sheet.Substring(1);
    // Get rid of $ in the sheet name
    if(sheet.EndsWith("'"))
        sheet = sheet.Substring(0, sheet.Length - 2);
    else
        sheet = sheet.Substring(0, sheet.Length - 1);
    excelSheets.Add(sheet.ToLower());
}

Tên trang tính Excel kết thúc bằng $. Nếu chúng ta sử dụng khoảng trắng trong tên trang tính, nó sẽ được bao quanh bởi dấu ngoặc kép. Ví dụ: nếu chúng tôi có một trang tính có tên là Chi tiết nhân viên (có khoảng trắng trong tên), thì tên chúng tôi đọc bằng OleDB là 'Chi tiết nhân viên$' và nếu trang tính được gọi là Chi tiết nhân viên (không có khoảng trắng trong tên) thì tên . Chúng tôi chỉ cần tên của trang tính, vì vậy chúng tôi cần loại bỏ các ký tự thừa trong tên mà chúng tôi đọc từ Excel. Mục đích của đoạn mã trên là để đảm bảo chúng ta đọc đúng trang tính Excel

Ví dụ: chúng ta có thể đặt tên cho bảng Excel tương ứng trong cơ sở dữ liệu và chúng ta có thể kiểm tra xem bảng đó có tồn tại hay không trước khi đọc dữ liệu từ trang tính

Về cơ bản có hai cách đọc dữ liệu từ tệp Excel. Cách thứ nhất là đọc tất cả các sheet vào bộ nhớ rồi ghi vào các bảng tương ứng. Cách thứ hai là đọc một hàng từ một trang tính và ghi ngay vào bảng tương ứng mà không lưu dữ liệu trong bộ nhớ. Điều này rất hữu ích nếu tệp Excel lớn. Chúng tôi sẽ sử dụng cách tiếp cận đầu tiên trong bài viết này

Tệp Excel chúng tôi sẽ sử dụng có ba trang tính, một trong số chúng được gọi là Công ty. Tờ này có thể được đọc bằng cách sử dụng đoạn mã sau

private void ReadCompany(OleDbConnection conn)
{
    string query = "SELECT * FROM [Company$]'";
    OleDbCommand cmdCompany = new OleDbCommand(query, conn);
    using OleDbDataReader dr = cmdCompany.ExecuteReader();

    // Get only the first company found
    if(dr.Read())
    {
        List cols = new List();
        for(int i = 0; i < dr.FieldCount; ++i)
        {
            cols.Add(dr[i].ToString().Replace("'", "''"));
        }
        _company = cols.ToArray();
    }
}

Tất cả Mã và Dữ liệu Excel mẫu

Sao chép dữ liệu vào một bảng hiện có trong cơ sở dữ liệu rất dễ dàng và có thể tìm thấy trong dự án C# đính kèm. Bài viết chứa đầy đủ dự án C#, tệp Excel được sử dụng làm ví dụ và tập lệnh SQL được sử dụng để tạo cơ sở dữ liệu (tập lệnh cơ sở dữ liệu này nằm ở đầu bài viết này)

Làm cách nào để nhập dữ liệu hàng loạt từ Excel sang SQL Server?

Làm cách nào để nhập dữ liệu hàng loạt trong SQL?