Strip Single Quotes While Export To Excel

Genrally to export data form Sql server to Excel.We generally do via IMPORT/Export Wizard.
The steps we generally follow are.



-Select the Source Server and the database.









-Select Destination as Microsoft Excel-Locate the file to Export Data



-Specify Whether it is direct export from table or View or any Query



-If it is from table select the table
-Execute the package




-Complete the wizard-Export the data

After export the first character in every single cell of the excel spreadsheet is
the quotation mark ' evn thoygh this is not present in the table values.

That is how Excel ISAM driver was designed to work. It adds the apostrophe to discriminate
between text and numeric values.

The Work around for this problem is

While exporting the data instead of Selecting the destination as Microsoft Excel choose it as Flat file Destination , dont forget to check check Unicode checkbox

-Specify Whether it is direct export from table or View or any Query

-If it is from table select the table

-Configure Flat file destination
set Row delimiter as - {CR}{LF}
set Column Delimtier as - Tab {t}



-Save and Execute the Package

Export the data .

Once the data is exported sucessfully,you can see the single quotes got stripped of.


--Happy Programming
**Harika**