c# - Script task not able to handle space in sheet name

Question

Hi I have a script task which pick up the excel files from a folder. The files have several sheets with names 'Question 1', 'Question 2'....etc.

My problem is that it is not reading or accessing the above sheet with 'space' in between Question and 1...

but when i replace it with (_) i.e. Question_1, Question_2...it is getting retrieved..my script task code is as below..any suggestions what should i change in my code to pick the sheetname as original.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
//using System.Text.RegularExpressions;

namespace ST_0762ccf78a5c4709b806530e3c885949
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        // public static string RemoveSpecialCharacters(string str) { return str.Replace("[^A-Za-z0-9_\\.]")};

        public void Main()
        {
            String FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();

            string StartingColumn = Dts.Variables["User::StartingColumn"].Value.ToString();
            string EndingColumn = Dts.Variables["User::EndingColumn"].Value.ToString();
            string StartReadingFromRow = Dts.Variables["User::StartReadingFromRow"].Value.ToString();

            var directory = new DirectoryInfo(FolderPath);
            FileInfo[] files = directory.GetFiles();

            //Declare and initilize variables
            string fileFullPath = "";
            //Get one Book(Excel file at a time)
            foreach (FileInfo file in files)
            {
                string filename = "";
                fileFullPath = FolderPath + "\" + file.Name;
                //filename = file.Name.Replace(".xlsx", "");
                filename = file.Name.Replace("'", " ");
                MessageBox.Show(fileFullPath);

                //Create Excel Connection
                string ConStr;
                string HDR;
                HDR = "YES";
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties="Excel 12.0;HDR=" + HDR + ";IMEX=1"";
                OleDbConnection cnn = new OleDbConnection(ConStr);

                //Get the sheetname and filename as columns
                /* SqlConnection myADONETConnection = new SqlConnection();
                 myADONETConnection = (SqlConnection)(Dts.Connections["Pulse_All_Tables"].AcquireConnection(Dts.Transaction) as SqlConnection);
                 string SQLColumnList = "";
                 string SQLQueryToGetMatchingColumn = "";
                 SqlCommand cmd = myADONETConnection.CreateCommand();
                 cmd.CommandText = SQLQueryToGetMatchingColumn;
                 SQLColumnList = (string)cmd.ExecuteScalar();*/


                //Get Sheet Name

                cnn.Open();
                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetname;
                sheetname = "";
                //sheetname = sheetname.Replace(" ", string.Empty);
                //string sheetname1 = "";

                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    //if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                    if (!drSheet["Table_Name"].ToString().Contains("FilterDatabase") && !drSheet["Table_Name"].ToString().EndsWith("$'"))
                    {
                        sheetname = drSheet["TABLE_NAME"].ToString();
                        //Display Sheet Name , you can comment it out
                        MessageBox.Show(sheetname);


                        //Load the DataTable with Sheet Data
                        //Get the sheetname and filename as columns

                        //OleDbCommand oconn = new OleDbCommand("select * from [" + sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn);
                        OleDbCommand oconn = new OleDbCommand("select " + "*" + ",'" + filename + "' AS FileName" + ",'" + sheetname + "' AS SheetName from [" + sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn);
                        //cnn.Open();
                        OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                        DataTable dt = new DataTable();
                        adp.Fill(dt);

                        //drop $from sheet name
                        sheetname = sheetname.Replace("$", "");

                        //sheetname = sheetname.Replace((sheetname.Replace("$", "")),"_");


                        //sheetname1 = sheetname.Replace(" ", "");

                        // Generate Create Table Script by using Header Column,It will drop the table if Exists and Recreate                  
                        string tableDDL = "";
                        tableDDL += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = ";
                        tableDDL += "OBJECT_ID(N'[dbo].[" + filename + "_" + sheetname + "]') AND type in (N'U'))";
                        tableDDL += "Drop Table [dbo].[" + filename + "_" + sheetname + "]";
                        tableDDL += "Create table [" + filename + "_" + sheetname + "]";
                        tableDDL += "(";
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (i != dt.Columns.Count - 1)
                                tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)" + ",";
                            else
                                tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)";
                        }
                        tableDDL += ")";


                        //use ADO.NET connection to Create Table from above Definition
                        SqlConnection myADONETConnection = new SqlConnection();
                        myADONETConnection = (SqlConnection)(Dts.Connections["Pulse_All_Tables"].AcquireConnection(Dts.Transaction) as SqlConnection);
                        //you can comment the messagebox, it is for debugging
                        MessageBox.Show(tableDDL.ToString());
                        SqlCommand myCommand = new SqlCommand(tableDDL, myADONETConnection);
                        myCommand.ExecuteNonQuery();
                        //Comment this message, it is for debugging
                        MessageBox.Show("TABLE IS CREATED");


                        //Load the data from DataTable to SQL Server Table.
                        SqlBulkCopy blk = new SqlBulkCopy(myADONETConnection);
                        blk.DestinationTableName = "[" + filename + "_" + sheetname + "]";
                        blk.WriteToServer(dt);
                    }
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };

    }
}

question from:https://stackoverflow.com/questions/66045966/script-task-not-able-to-handle-space-in-sheet-name

Answer 1

You will have to replace the ' in the Sheetname. Try building your query as in following example:

oconn = new OleDbCommand("SELECT * FROM " + ("[" + dr["TABLE_NAME"].ToString() + "B1:B1]").Replace("'", ""), cnn);

Following a short example app I used to test this logic with a dummy Excel file which featured 3 sheets:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Data;
using Microsoft.Win32;

namespace ConsoleApp13
{
    class Program
    {
        static void Main(string[] args)
        {
            string fileName = "c:\temp\test.xlsx";

            string ConStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=NO;Empty Text Mode=NullAsEmpty""", fileName);
            OleDbConnection cnn = new OleDbConnection(ConStr);
            cnn.Open();

            DataTable dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            foreach (DataRow dr in dt.Rows)
            {
                Console.WriteLine(dr["TABLE_NAME"]);
                
                //currently hardcoded B1:B1 - has to be replaced with your StartColumn:EndColumn logic
                string cmd = "SELECT * FROM " + ("[" + dr["TABLE_NAME"].ToString() + "B1:B1]").Replace("'", "");
                Console.WriteLine(""+cmd);


                DataTable dt2 = new DataTable();
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd, cnn);

                da.Fill(ds);
                dt2 = ds.Tables[0];

                foreach(DataRow dr2 in dt2.Rows)
                {
                    Console.WriteLine("" + dr2[0].ToString());
                }
            }

            cnn.Close();

            Console.WriteLine("Ende...");
            Console.ReadKey();
        }
    }
}