Remove Double Quotes in Excel Sheet using SSIS transformation is the most common question asked by many of our friends and blog followers. In this article we will show you, How to resolve the issue with live example.
Below screenshot will show you the data present in the Customers.xls Excel file. If you observe closely, every row under the Education column is surrounded by the double quotes (” “). Let us see the steps to resolve it
Remove Double Quotes in Excel sheet using SSIS 2014 Example
STEP 1: Drag and drop the data flow task from the toolbox to control flow region
Double click on it and it will open the data flow tab.
STEP 2: Drag and drop EXCEL Source, OLE DB Destination from toolbox to data flow region.
STEP 3: Double click on Excel source in the data flow region will open the connection manager settings and provides option to select the table holding the source data. From the below screenshot you can observe that we are selecting the Customers.xls file present in our local hard drive
Since our excel sheet holds the column names in the first row, we are check marking theFirst row has column names option. If your excel file is different then don’t select it.
From the below screenshot you can observe that, we are selecting the Customer tab sheet from Customers.xls excel file.
STEP 5: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
TIP: If we don’t want any column then there is no point to add it in to your SQL command.
Click OK and drag and drop the Derived Column Transformation from toolbox to data flow region and 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 are adding the new column as New Education and added the expression to it.
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: Now we have to provide Server, database and table details of the destination. So double-click on the OLE DB Destination and provide the required information.
Here, we are creating new table
NOTE: It is always necessary to convert data types while transfer from Excel to SQL server database. Here, we are creating table with NVARCHAR data types but in real-time, scenarios will be different.
STEP 8: Click on Mappings tab to check whether the source columns are 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 SQL Server Management studio and Check the results
Thank You for Visiting Our Blog
