Copy- paste data from SQL to EXCEL

Question

A very straightforward procedure is too difficult for me now. I wrote a query then copied the data with headers when I pasted into excel it looked messy but now I know what's happening. The address column in my application is a multi-line text box - in sql the column type: varchar(Max). However in the Sql that column is a single line value.. So what happened is that in Excel. It broke the Address field into the way its entered in the application .. Any idea how to fix this.

I tried Replace and found the ASCHI character but again it didn't work.

replace(replace(replace([GP], CHAR(13), ''), CHAR(32), ' '), CHAR(10), ', ')

Example:

Database Table: ID; PK int Identity Name: varchar(5) Address1: varchar(Max) - MultiLine textbox in application Address2: Varchar(MAX)- MultiLine textbox in application Comment: Varchar(MAX)- MultiLine textbox in application

Table in Sql Management Studio

 ID Name    Address1             Address2                        Comment
1   Dave    13 Close Drive, Newcastle, United Kingdom, NW12 8GS 12 Driveway, Newcastle, NW12 8HS    This comment is shown in SQL SERVER Management in one long line ( not in New Line)

Once its copied to Excel:

ID   Name Address1           Address2      Comment
1   Dave   13 Close Drive      12 Driveway, This comment is shown in SQL 

Newcastle, 
United Kingdom,
NW12 8GS
Newcastle, 
NW12 8HS
SERVER Management in one long line ( not in New Line)

You can't copy data from a server. You can only copy data from a client tool that display the database data, for example SSMS. Don't do that if you want to get the exact data out. Grids for example won't display newlines. Export the data to a CSV and read that, or use SSIS to export the data to Excel. That's available through the Export Data wizard in the database's context menu — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 15, 2018 at 17:07
Any reason you are replacing a space (char(32)) with a space (' ') ? — PaulF
– PaulF, Commented Feb 15, 2018 at 17:09
Panagiotis Kanavos - I did right click Save results as CVS and it looks the same way as in address its broken into multiple lines?! — Warda
– Warda, Commented Feb 15, 2018 at 17:17
Panagiotis Kanavos - I am using SQL Server Management Studio. I've actually tried coping another table in the same database and it copied ok with headers. So the problem is with the other table. the fields that are Multiline textbox that are very long is broken into new lines when I copy it to excel whilst on Management studio it's all in one line.. hope that makes sense. the problem is with the way Multiline textbox are saved in the application ( thats my assumption) — Warda
– Warda, Commented Feb 16, 2018 at 9:57

Andrei Rantsevich · Accepted Answer · 2019-08-11 20:45:46Z

2

SSMSBoost add-in adds the possibility to "Copy-Paste" data from ResultsGrid to Excel, preserving all data types (native Excel clipboard format is used).

(I am the developer of this add-in)

answered Aug 11, 2019 at 20:45

Andrei Rantsevich

2,97523 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SChowdhury · Accepted Answer · 2018-02-15 17:10:04Z

0

Try using Import-Export wizard of SSMS. Dump the data of the table/view into an Excel.

answered Feb 15, 2018 at 17:10

SChowdhury

1631 silver badge11 bronze badges

8 Comments

Warda Over a year ago

I am using SQL Server 2012. I don't see Import-Export option?

SChowdhury Over a year ago

If you are using SSMS as a GUI then,Right click on DB >> Task >> Export data

Warda Over a year ago

But that's exporting the entire database. while with me i've written query and i want to export the returned results!

SChowdhury Over a year ago

Wrap the query in a view or dump it in a table. Export the object (table or view you create), delete the view/table afterwards

Warda Over a year ago

SChowdhury- I am using SQL Server Management Studio. I've actually tried coping another table in the same database and it copied ok with headers. So the problem is with the other table. the fields that are Multiline textbox that are very long is broken into new lines when I copy it to excel whilst on Management studio it's all in one line.. hope that makes sense. the problem is with the way Multiline textbox are saved in the application ( thats my assumption)

|

Jan · Accepted Answer · 2018-02-16 21:02:59Z

0

I'd like to add comments, but not at that level yet.

When trying to export to Excel from query results, you should be able to right click and 'save as'; but instead of selecting the default of .csv change it to tab delimited .txt. Prior to doing this change (or add) in the replace statement 'CHAR(9)' the TAB character replacing with "".

I've also had success of selecting all (upper left corner of the query display) then right clicking in data and selecting 'copy with headers' then pasting into a new workbook sheet.

answered Feb 16, 2018 at 21:02

Jan

4215 silver badges15 bronze badges

Collectives™ on Stack Overflow

Copy- paste data from SQL to EXCEL

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related