I have been searching for an efficient way of uploading 100's of text files in the same folder to an SQL Server with one click of a button. I perform this on a monthly basis manually mapping the tables. Th process takes some time manually choosing each flat file (.txt) and mapping to the correct loading table before the exact extract. I have searched many different ways to do this including SSIS and Integration Service packages. All of these are too manual and take too much time to perform and especially maintain.

I have had an idea that for example below of 5 flat files in the same folder:

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

To be uploaded to the SQL server in a schema [upload] with their respective file names. The final output would look like this.

1. [servername].[upload].Extract_ABC
2. [servername].[upload].Extract_YMCA
3. [servername].[upload].Extract_WSS
4. [servername].[upload].Extract_RMC
5. [servername].[upload].Extract_HBO

These tables could then be assigned location manually in a lookup table and inserted to the correct tables. This would remove almost all manual work aside from maintaining a location table in the Server.

Note it is important that these tables are also created when inserted

Does anyone know of a way this can be performed? It would have to avoid the use of Visual Studio manual packages etc. Rather a bulk insert of every flat file into an SQL Server with their respective file names.

An efficient query/process could save me days of time a month.

CodePudding user response：

As I mentioned, I would actually use something like Powershell to do this. The script to do so is actually quite simple.

#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
}

This assumes Windows Authentication, however, if you are using SQL Authentication you can pass the -Username and -Password switches to Invoke-SqlCmd.

CodePudding user response：

I kind of took this as a challenge to see if I could do it. Here is what I came up with in 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;
    }

Here is a list of my using statements:

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