Remove Double Quotes in Excel Sheet using SSIS

Remove Double Quotes in Excel Sheet using the transformations is the most common question asked by many of our friends and SSIS blog followers. In this article, we will show you how to Remove Double Quotes in Excel Sheet using SSIS with a live example.

The below screenshot will show you the data present in the Customers.xls Excel file. If you observe closely, every row under the Education column surrounded by the double quotes (“”). Let us see the Remove Double Quotes in Excel Sheet using SSIS steps to resolve it

Remove Double Quotes in Excel sheet using SSIS Example

STEP 1: Drag and drop the data flow task from the toolbox to control flow region

Double click on it will open the data flow tab.

STEP 2: Drag and drop EXCEL Source, OLE DB Destination from SSIS toolbox to the data flow region.

STEP 3: Double click on Excel source in the data flow region will open the connection manager settings and provides an option to select the table holding the source data. From the below screenshot, you can see we are choosing the Customers.xls file present in our local hard drive

Since our excel sheet contains the column names in the first row, we are check-marking the First row has column names option. If your excel file is different, don’t select it.

From the below screenshot, we are selecting the Customer tab sheet from Customers.xls excel file.

STEP 5: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns.

Click OK and drag and drop the Derived Column Transformation from the toolbox to the data flow region. Next, connect the excel source output to this.

STEP 5: Double click or right-click on the Derived Column Transformation to edit and convert our source columns data.

In the Derived Column Transformation editor, we were adding the new column as New Education and added the expression to it.

LTRIM(RTRIM(REPLACE(Education,"\"","")))

If you observe the Expression code, We used the LTRIM and RTRIM to remove the extra spaces and REPLACE function to replace the double quotes.

STEP 7: Next, provide the Server, database, and table details of the target. So double-click on the OLE DB Destination and provide the required information.

Here, we are creating a new table

NOTE: It is always necessary to convert data types during the transfer from Excel to SQL server database in SSIS. Here, we are creating a table with NVARCHAR data types, but in real-time, scenarios will be different.

STEP 8: Click on the Mappings tab to check whether the source columns exactly mapped to the destination columns.

Click OK to finish our package design. Let us run the package and see whether we successfully remove Double Quotes in Excel Sheet using SSIS or not

Let us open the Management studio and Check the results