using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Document_Database
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Starting the app");
            var buildCSVs = new BuildCSVs();
            buildCSVs.CSVExport();
            var mergeCSVs = new MergeCSVs();
            mergeCSVs.CSVMerge();
            Console.ReadLine();
        }
    }
}

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

namespace Document_Database
{
    class BuildCSVs
    {
        public void CSVExport()
        {
            string connectionString = "Data Source=localhost;" + "Initial Catalog=AdventureWorks2017;Integrated Security=SSPI";
            string tblListSQL = "SELECT s.name schname, o.name tblname FROM sys.objects o JOIN sys.schemas s on o.schema_id = s.schema_id WHERE o.type = 'U' AND o.name NOT IN ('sysdiagrams') ORDER BY o.name;";
            // Create ADO.NET objects.
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(tblListSQL, con);

            // Execute the command.
            con.Open();

            //int rowsAffected = 
            //string[] tableList = cmd.ExecuteNonQuery();
            int i = 1;
            //string[] names = new string[128];
            var Reader = cmd.ExecuteReader();
            string tblName;
            string schName;
            if (Reader.HasRows)
            {
                while (Reader.Read())
                {
                    //Console.WriteLine(String.Format("{0}", Reader["name"]));
                    tblName = String.Format("{0}", Reader["tblname"]);
                    schName = String.Format("{0}", Reader["schname"]);
                    //Console.WriteLine(tblName);
                    tableInfo(schName, tblName);

                    i += 1;
                }
            }
            Reader.Close();
            con.Close();
        }
        private void tableInfo(string schName, string tblName)
        {
            Console.WriteLine(schName + '.' + tblName);
            Console.WriteLine("<< Go to main list");
            string connectionString = "Data Source=localhost;" + "Initial Catalog=AdventureWorks2017;Integrated Security=SSPI";
            string tblInfoSQL = "dbo.uspTableInfo";
            // Create ADO.NET objects.
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(tblInfoSQL, con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param;
            param = cmd.Parameters.Add("@schema_name", SqlDbType.VarChar, 128);
            param.Value = schName;
            param = cmd.Parameters.Add("@table_name", SqlDbType.VarChar, 128);
            param.Value = tblName;

            // Execute the command.
            con.Open();
            //int rowsAffected = cmd.ExecuteNonQuery();
            string filename = @"C:\temp\Junk\" + schName + '_' + tblName + ".csv";
            using (System.IO.StreamWriter file = new System.IO.StreamWriter(filename))
            {
                file.WriteLine(schName + '.' + tblName);
                file.WriteLine("<< Go to main list");
                file.WriteLine(" ");
                file.WriteLine("TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CONSTRAINT,Notes,Join Information");
            
            var Reader = cmd.ExecuteReader();
            int i = 1;
            string tableName;
            string schemaName;
            string columnName;
            string ordinalPosition;
            string columnDefault;
            string dataType;
            string charMaxLength;
            string constraint;
            string notes;
            string joinInfo;

            if (Reader.HasRows)
            {
                while (Reader.Read())
                {
                    tableName = String.Format("{0}", Reader["TABLE_NAME"]);
                    schemaName = String.Format("{0}", Reader["TABLE_SCHEMA"]);
                    columnName = String.Format("{0}", Reader["COLUMN_NAME"]);
                    ordinalPosition = String.Format("{0}", Reader["ORDINAL_POSITION"]);
                    columnDefault = String.Format("{0}", Reader["COLUMN_DEFAULT"]);
                    dataType = String.Format("{0}", Reader["DATA_TYPE"]);
                    charMaxLength = String.Format("{0}", Reader["CHARACTER_MAXIMUM_LENGTH"]);
                    constraint = String.Format("{0}", Reader["CONSTRAINT"]);
                    notes = String.Format("{0}", Reader["Notes"]);
                    joinInfo = String.Format("{0}", Reader["Join Information"]);
                    file.WriteLine(schemaName + "," + tableName + "," + columnName + "," + ordinalPosition + "," + columnDefault + "," + dataType + "," + charMaxLength + "," + constraint + "," + notes + "," + joinInfo);
                    i += 1;
                }
            }
            Reader.Close();
            }
            con.Close();
            return;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//Added below name spaces
using System.IO;
using System.Data.OleDb;


namespace Document_Database
{
    class MergeCSVs
    {
        public void CSVMerge()
        //static void Main(string[] args)
        {
            //the datetime and Log folder will be used for error log file in case error occured
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\temp\Log\";
            try
            {
                //Declare Variables
                //Provide the source folder path
                string SourceFolderPath = @"C:\Temp\Junk\";
                //Provide the Destination folder path
                string DestinationFolderPath = @"C:\Temp\Destination\";
                //Provide the extension of input files such as .csv or .txt
                string FileExtension = ".csv";
                //Provide the file delimiter such as comma or pipe
                string FileDelimiter = ",";
                //Provide the Excel file name that you want to create
                string ExcelFileName = "AllTables";
                string CreateTableStatement = "";
                string ColumnList = "";

                //Reading file names one by one
                string SourceDirectory = SourceFolderPath;
                string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {


                    //Read first line(Header) and prepare Create Statement for Excel Sheet
                    System.IO.StreamReader file = new System.IO.StreamReader(fileName);
                    string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
                    CreateTableStatement = (" Create Table [" + filenameonly + "] (["
                        + file.ReadLine().Replace(FileDelimiter, "] Text,["))
                        + "] Text)";
                    file.Close();

                    //Construct ConnectionString for Excel
                    string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestinationFolderPath + "\\" + ExcelFileName + "_" + datetime
                        + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
                    OleDbConnection Excel_OLE_Con = new OleDbConnection();
                    OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;

                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Cmd.CommandText = CreateTableStatement;
                    Excel_OLE_Cmd.ExecuteNonQuery();

                    //Writing Data of File to Excel Sheet in Excel File
                    int counter = 0;
                    string line;

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter == 0)
                        {
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                        }
                        else
                        {
                            string query = "Insert into [" + filenameonly + "] ("
                                + ColumnList + ") VALUES('"
                                + line.Replace(FileDelimiter, "','")
                                + "')";
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();

                        }
                        counter++;
                    }
                    Excel_OLE_Con.Close();
                    SourceFile.Close();

                }
            }

            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".txt"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}

CREATE PROCEDURE [dbo].[uspTableInfo] (@schema_name sysname, @table_name sysname)
AS
BEGIN
    SET NOCOUNT ON;
	SELECT s.name TABLE_SCHEMA, so.name TABLE_NAME, SC.NAME COLUMN_NAME, sc.colorder ORDINAL_POSITION, ISNULL(sm.text, '') COLUMN_DEFAULT, t.name DATA_TYPE, CASE WHEN sc.isnullable = 0 THEN 'NOT NULL' ELSE 'NULL' END IS_NULLABLE, 
	CASE WHEN t.name NOT IN('int', 'hierarchyid', 'geometry', 'geography', 'uniqueidentifier', 'datetime', 'tinyint', 'smallint', 'decimal', 'datetime2', 'datetimeoffset', 'image', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'timestamp', 'tinyint') THEN sc.length END CHARACTER_MAXIMUM_LENGTH, ''[CONSTRAINT], '' Notes, ''[Join Information]
	FROM dbo.sysobjects so
	INNER JOIN dbo.syscolumns sc ON so.id = sc.id
	JOIN sys.schemas s on so.uid = s.schema_id
	LEFT JOIN dbo.syscomments sm ON sc.cdefault = sm.id
	JOIN systypes t on t.xtype = sc.xtype
	WHERE so.xtype = 'U' and so.name = @table_name and s.name = @schema_name
	ORDER BY so.[name], sc.colid; 
	SET NOCOUNT OFF;
END;

                file.WriteLine(schName + '.' + tblName);
                file.WriteLine("<< Go to main list");
                file.WriteLine(" ");

                   while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter == 0)
                        {
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                        }
                        else
                        {
                            string query = "Insert into [" + filenameonly + "] ("
                                + ColumnList + ") VALUES('"
                                + line.Replace(FileDelimiter, "','")
                                + "')";
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();

                        }
                        counter++;
                    }

            using (var sr = new StringReader(Properties.Resources.DataTransform))
...
            using (var cmd = new SqlCommand(Properties.Resources.TablesQuery, cnn))

using System;
using System.IO;
//using System.Collections.Generic;
//using System.Linq;
//using System.Text;
//using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace Document_Database
{
    class CopyCSV
    {
        public void CSVCopy()
        {

            Excel.Application srcApp;
            Excel.Workbook srcWorkbook;
            Excel.Worksheet srcWorksheet;

            Excel.Application destApp;
            Excel.Workbook destWorkbook;
            Excel.Worksheet destWorksheet;

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\temp\Log\";

            //Declare Variables
            //Provide the source folder path
            string SourceFolderPath = @"C:\Temp\Junk\";
            //Provide the Destination folder path
            string DestinationFolderPath = @"C:\Temp\Destination\";
            //Provide the extension of input files such as .csv or .txt
            string FileExtension = ".csv";
            //Provide the file delimiter such as comma or pipe
            //string FileDelimiter = ",";
            //Provide the Excel file name that you want to create
            string ExcelFileName = "AllTables";
            //string CreateTableStatement = "";
            //string ColumnList = "";

            int i =1;

            //Reading file names one by one
            string SourceDirectory = SourceFolderPath;
            destApp = new Excel.Application();
            string destPath = DestinationFolderPath + ExcelFileName + ".xlsx";
            destWorkbook = destApp.Workbooks.Open(destPath, 0, false);
            string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
            foreach (string fileName in fileEntries)
            {
                System.IO.StreamReader file = new System.IO.StreamReader(fileName);
                string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
                string srcPath = SourceFolderPath + filenameonly + ".csv";
                srcApp = new Excel.Application();
                srcWorkbook = srcApp.Workbooks.Open(srcPath);
                srcWorksheet = srcWorkbook.Worksheets.get_Item(1);
                Excel.Range srcRange = srcWorksheet.get_Range("A1", "J60");



                //worKbooK = excel.Workbooks.Add(Type.Missing);
                //var xlNewSheet = destWorkbook.Worksheets.Add(filenameonly);
                //var xlSheets = destWorkbook.Sheets as Excel.Sheets;
                //var xlNewSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[i], Type.Missing, Type.Missing, Type.Missing);
                //var xlNewSheet = (Excel.Worksheet)destWorkbook.Worksheets.Add(Type.Missing, destWorkbook.Worksheets[destWorkbook.Worksheets.Count], Type.Missing, Type.Missing);
                //xlNewSheet.Name = filenameonly;

                //var worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)destWorkbook.ActiveSheet;
                var worksheet = destWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                string wsName = "";
                if (filenameonly.Length > 31)
                    wsName = filenameonly.Substring(1, 31);
                else wsName = filenameonly;

                worksheet.Name = wsName;
                //xlNewSheet.Cells[1, 1] = "New sheet content";

                destWorksheet = destWorkbook.Worksheets.get_Item(1);
                Excel.Range destRange = destWorksheet.get_Range("A1", "J60");


                srcRange.Copy(Type.Missing);
                //_ = destRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone);
                Console.WriteLine("Source: " + srcPath + ":" + "srcWorksheet" + " Destination: " + destPath + ":" + destWorksheet);

                destApp.DisplayAlerts = false;
                destWorkbook.SaveAs(DestinationFolderPath + ExcelFileName + "_" + datetime + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing);

                i++;
                srcApp.Application.DisplayAlerts = false;
                srcWorkbook.Close(false, null, null);
                srcApp.Quit();
            }
            destApp.Application.DisplayAlerts = false;
            destWorkbook.Close(true, null, null);
            destApp.Quit();
        }


    }
}