CODEWITHSUNDEEP
sqlsql-servercsvexport-to-excel
kzoom
kzoom

Reputation: 241

How to export VARCHAR2 data that includes commas(!) to Excel from MSSQLMgmtStudio(2012)

First, a grumble: MS builds SQL Server Studio AND Excel, but can't make one save in the standard format of the other?

OK, I'm a data analyst, but not allowed to change/mod either the data or structures directly. So full READ, but no WRITE.

I'm trying to do a dump so I can do some of this analysis offline, as I have no remote access either.

So one VARCHAR2 column in this table is for comments on the purchase of the asset being described/tracked. Of course, there are commas. The only export types built into SQL Server Studio are .csv and .txt, and .csv just turns into a mess when 'comma' is included as a delimiter.

So after an hour or so of screwing around with this, (including reading a thread on methods for excluding the one column from a SELECT while still exporting the other 221 columns in the table, without having to write them all out manually (fun reading, impressive, but means I'd have to figure out which of them actually works, and then still export the one column separately and insert it in the Excel separately)) I am throwing this problem on the pile at StackOverflow.

Someone else must have worked around this frustration of the .csv format as export VS the commas embedded in 'comment' text. Any help would be appreciated.

Upvotes: 1

Views: 285

Answers (2)

János Spengler
János Spengler

Reputation: 109

So If you replace the ' to some special character you can export it.

Select 
    Replace(columnName,'''','`') 
from Table

Other solution if you use the manager studio https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard

Upvotes: 0

ViKiNG
ViKiNG

Reputation: 1334

Why don't you simply select all data in ssms result window, then copy and then paste in a blank excel file? It should copy paste all data in correct format including comma valued fields in single column.

Try that.

Upvotes: 1

Related Questions