Export from SQL Server to Excel, all text fields have a leading single quote

Question

Using SQL Server 2019 I'm exporting data from a table to Excel, however, ALL cells with data have a leading single quote. The corrupts all the data. How can this be avoided?

Thank you.

Answer 1

I have not used the Export wizard to get data to Excel, so I can't speak to that. When I export data to Excel, I do one of these:

1. Run a query in SSMS and copy paste into Excel. This works for simple stuff, but since Excel guesses the data type and outsmarts itself, this can fail. For instance, if there are strings like 00110011, Excel thinks it's a number, and you lose the zeroes.
2. In Excel, go to the Data menu. Select New Query, second item from the left. Here select From Database and then from SQL Server. In the form you get, select Advanced options. This will permit you to run a query.

There is an menu item Get External Data in Excel that also seems to support from SQL Server, but I have not worked with that one.

By the way, I would expect those single quotes are there to prevent Excel from interpreting the data in its own way. That is, you want to enter 00110011 in a cell manually, you need to enter '00110011 for Excel to obey.

Answer 2

This is how I exported data from sql server to Excel - right click on the db, tasks, export data, select the table, finish...

Answer 3

This can happen when the target data type in excel is defined as Text, change it to string and ensure that you are using right delimiters and tab identifiers.

Answer 4

"change it to string and ensure that you are using right delimiters and tab identifiers."

How do I do this? Where are these options?

Answer 5

Hi @ moondaddy-8531,
According to the error, it seems column Co_GeneralInfo was truncated because the number of characters in the source exceeds the number of characters allowed for conversion.
Could you please try to increase the size of destination column data type in select source tables and views page->Edit Mappings?

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November