Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 22, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

DTS to Excel File

I tried a DTS export of a table to an Excel file. The data types of all the columns in the table are varchar. When it exports to the Excel file, Excel forces every column to be text. So every value of every cell has a single quote before it (not in the cell itself, but in the value shown right below the toolbar). Since the other system that's going to process the Excel file does not expect the single quote, the process always fails.

How do I get rid of the single quote?

This apostrophe is a formatting character that's displayed in Excel as a ('). I haven't found a way to remove it during the DTS export.

In Excel, you can remove it by formatting a cell as text (Format, Cells..., select Text in the General tab). Copy the cell, select the cells with the apostrophe, click Paste Special, and then select the Add radio button. This method doesn't affect the data but removes the leading apostrophe. You can do this manually or create a routine in VBA that runs those steps automatically.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date