CODEWITHSUNDEEP
c#c#-4.0ssis
user3662215
user3662215

Reputation: 526

How to automate renaming of column headers in Excel 2013 worksheets using C#

I am developing a script task in an SSIS package using Visual Studio 2012 that will be cleaning up the worksheet and column header names for several hundred Excel 2013 (.xlsx) files to be eventually imported into a SQL Server 2012 database with each worksheet of each workbook (Excel file) becoming a unique table. Each Excel file can contain one or over twenty worksheets and each worksheet can contain one or multiple columns. The good news is that all of the worksheets contain column headers on the first row. The dynamic importing process handling an inconsistent number of naming of worksheets and column headers is not a problem, I've already developed this next step in the flow control.

What I cannot figure out is how to rename the column headers (containing spaces) to not contain spaces for each worksheet.

Using the following C# code (.NET Framework 4) I've already been able to successfully rename the worksheets so that they do not contain spaces. SSIS does not work with worksheet names containing spaces and special characters.

    public void Main()
    {

        string xlFile = Dts.Variables["User::ExcelFile"].Value.ToString();

        Excel.Application app = new Excel.Application();
        Excel.Workbook excelWorkbook;
        //Excel.Worksheet excelWorksheet;

        try
        {
            excelWorkbook = app.Workbooks.Open(xlFile);

            string tempsheet = " ";
            //int CountWorksheets = excelWorkbook.Sheets.Count;

            // Rename worksheets replace empty space with an underscore needed for an SSIS import
            foreach (Excel.Worksheet sheet in excelWorkbook.Worksheets)
            {
                tempsheet = sheet.Name;
                tempsheet = tempsheet.Replace(" ", "_");
                sheet.Name = tempsheet;
                //MessageBox.Show(Path.GetFileName(xlFile) + "  " + tempsheet + "  " + sheet.UsedRange.Columns.Count);
            }

            excelWorkbook.Save();
            excelWorkbook.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show("Excel sheet rename failed for file " + xlFile + " based on " + ex.Message);
        }
        finally
        {
            app.Quit();
            app = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

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

The above code is applying the following Namespaces:

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Text;

Based on the above code, can anybody provide a suggestion as to how to also rename the column headers (on the first row of every worksheet) as I've already done in renaming the worksheet names? For example, a column headers of "First Name" and "Last Name" become "First_Name" and "Last_Name". With these eventually becoming field names in new database tables, I like to avoid including the space between words. I've run multiple searches but could not find a good solution. Thanks in advance.

Upvotes: 0

Views: 2350

Answers (1)

user3662215
user3662215

Reputation: 526

Since field names containing spaces will not prevent the import from working in an SSIS package, the field names originating from the worksheet column headers can be updated later after they're created in the tables and populated. This can be done with TSQL using sp_rename. The following code generates the sp_rename for every field in the database containing one or more spaces. Then all that is required is to copy and past from the Results it generates into the query and run them all.

SELECT 'EXEC sp_rename '''+table_name+'.['+column_name+']'','''+replace(column_name,' ','_')+''',''COLUMN''' 
FROM information_schema.columns
WHERE column_name like '% %'

It would still be nice to know how to gather the worksheet column headers using the C# Task Script.

Upvotes: 1

Related Questions