Question:
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?
Answer:
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.
