Remove Text Qualifier (Double Quotes Single Quotes or Special Text Qualifier) in Excel Sheet using SSIS

In the CSV file or flat file we have the option to remove the Text Qualifier by setting the property.

Read Here:  Remove double quotes from CSV filein SSIS

https://bageshkumarbagi-msbi.blogspot.com/2021/03/remove-double-quotes-from-csv-file-in.html

In the excel we don’t have such type of property. If we are getting any column value with Single or Double or any Special text qualifier in this we need to use the Derived column transformation to remove the quotes and then we need to load it into the destination table.

See the excel file  

We need to remove the Double Quotes from SalesorderNumber Column and load this into the database.

Taking the data flow task and source as the Excel file and doing the configuration.

  

Now we are taking the Derived Column transformation.

 

(DT_STR,30,1252)REPLACE(LTRIM(RTRIM(SalesOrderNumber)),"\"","")

 Now taking destination as OLEDB Destination and doing the mapping. 

Now our package is ready to run.

See the records in the table.  

Running the package. 

Package executed successfully.

See the records in the table.

  

Data loaded successfully.