使用平面檔案名作為表名將同一檔案夾中的多個平面檔案匯入SQLServer

我一直在尋找一種通過單擊按鈕將同一檔案夾中的 100 個文本檔案上傳到 SQL Server 的有效方法。我每月執行一次手動映射表。該程序需要一些時間手動選擇每個平面檔案 (.txt) 并在準確提取之前映射到正確的加載表。我已經搜索了許多不同的方法來做到這一點，包括 SSIS 和集成服務包。所有這些都太手動了，需要太多時間來執行，尤其是維護。

我有一個想法，例如以下同一檔案夾中的 5 個平面檔案：

1. Extract_ABC.txt
2. Extract_YMCA.txt
3. Extract_WSS.txt
4. Extract_RMC.txt
5. Extract_HBO.txt

使用各自的檔案名以模式 [upload] 上傳到 SQL 服務器。最終輸出將如下所示。

1. [服務器名].[上傳].Extract_ABC
2. [服務器名稱].[上傳].Extract_YMCA
3. [服務器名稱].[上傳].Extract_WSS
4. [服務器名].[上傳].Extract_RMC
5. [服務器名稱].[上傳].Extract_HBO

然后可以在查找表中手動為這些表分配位置并插入到正確的表中。除了在服務器中維護位置表之外，這將消除幾乎所有的手動作業。

請注意，插入時也會創建這些表，這一點很重要

有誰知道可以執行此操作的方法？它必須避免使用 Visual Studio 手動包等。而是將每個平面檔案批量插入到具有各自檔案名的 SQL Server 中。

一個有效的查詢/流程可以每月為我節省幾天的時間。

uj5u.com熱心網友回復：

正如我所提到的，我實際上會使用類似 Powershell 的東西來做到這一點。執行此操作的腳本實際上非常簡單。

#Below is a Linux Path, as I am running SQL Server (and Powershell) on Linux
$ImportFolder = "/home/mssql/ImportSample" #The Folder your files are in
$Instance = "." #The Instance you are inserting the data into. "." means local host
$Database = "ImportSample" #The database you are inserting the data into

$Files = Get-ChildItem -Path $ImportFolder -Filter "*.txt" #Get all the txt files

#Loop the files
foreach($File in $Files){
    #Create the statement
    $SQL = "BULK INSERT dbo.[$($File.BaseName.Replace("]","]]"))] FROM '$($File.FullName.Replace("'","''"))' WITH (FORMAT = 'CSV', FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a', FIRSTROW = 2);"

    Write-Debug $SQL

    #Execute the statement
    Invoke-SqlCmd -ServerInstance $Instance -Database $Database -Query $SQL
}

這假定 Windows 身份驗證，但是，如果您使用 SQL 身份驗證，您可以將-Username和-Password開關傳遞給Invoke-SqlCmd.

uj5u.com熱心網友回復：

我有點把這當作一個挑戰，看看我是否能做到。這是我在c#中想出的...

    public static string cstr = "Enter your connection string to destination here";
    
    //if you are going to use script task inside SSIS then this is what goes into whatever the Main is in SSIS.
    static void Main(string[] args)
    {
        var dir = @"C:\whereever";

        foreach(var file in new DirectoryInfo(dir).GetFiles().AsEnumerable().Where(f=>f.Extension.Contains("txt")))
        {
            DataTable dt = ConvertCSVtoDataTable(file.FullName, '|');
            //getColumnNames into an object
            List<string> colNames = new List<string>();
            foreach (DataColumn dc in dt.Columns)
                colNames.Add(dc.ColumnName);

            //create table
            string tableName = Path.GetFileNameWithoutExtension(file.Name);
            createTable(colNames, tableName);
            //load table
            loadDtIntoDb(dt, colNames, tableName);
        }
    }

    public static void loadDtIntoDb(DataTable dt, List<string> cols, string tableName)
    {
        using (var conn = new SqlConnection(cstr))
        {
            using (var bcp = new SqlBulkCopy(conn))
            {
                bcp.DestinationTableName = tableName;
                foreach (var col in cols)
                    bcp.ColumnMappings.Add(col, col);
                bcp.WriteToServer(dt);
            }
        }
    }

    public static void createTable(List<string> dcs,string tableName)
    {
        StringBuilder sb = new StringBuilder();
            sb.AppendLine(string.Format("Create table {0}(", tableName));
        foreach (var dc in dcs)
            sb.AppendLine(string.Format("{0} varchar(255),", dc));
        //Clean sql by removing final ',' and adding a close paren 
        string sql = sb.ToString().TrimEnd(',')   ")";
        using (var conn = new SqlConnection(cstr))
        {
            using (var cmd = new SqlCommand(sql, conn))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }

    public static DataTable ConvertCSVtoDataTable(string strFilePath, char delim)
    {
        DataTable dt = new DataTable();
        using (StreamReader sr = new StreamReader(strFilePath))
        {
            string[] headers = sr.ReadLine().Split(delim);
            foreach (string header in headers)
            {
                dt.Columns.Add(header);
            }
            while (!sr.EndOfStream)
            {
                string[] rows = sr.ReadLine().Split(',');
                DataRow dr = dt.NewRow();
                for (int i = 0; i < headers.Length; i  )
                {
                    dr[i] = rows[i];
                }
                dt.Rows.Add(dr);
            }
        }
        return dt;
    }

以下是我的 using 陳述句串列：

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Data.SqlClient;

標籤：

上一篇：將多行壓縮為每個鍵

下一篇：表存盤有兩列，如store_id和products。撰寫SQL查詢以查找僅銷售洗發水和餅干的store_ids