January 4, 2017 at 8:01 am
This is my first time seeing this:
I need to query a database on a remote server and copy down the result to my laptop.
After I paste it (with header) to excel, the content is NOT the content on the query result, messed up.
I cannot save it as csv and copy it out because of the current database/server setting.
How could this happen?
Any clue is highly appreciated.
January 4, 2017 at 8:15 am
Findings:
I just noticed one of the row was split into two rows in excel when it is copied, I believe there are more, which messed up the rest.
January 4, 2017 at 8:22 am
If your data has text in it one(or more) of the columns likely has a delimiter in it, the default is a tab when you copy and paste out of SSMS. That will mess up when you copy and paste it into excel. You can try stripping that character out of the columns in your query or using SSIS or some other tool to export the data with a text qualifier, or a different delimiter.
January 4, 2017 at 8:28 am
So I have narrowed down the root cause to the following finding:
select case_desc from table where id = 13245
Please see the screenshot for the cell case_desc in the result, basically there is a carrigiage return character in it:
Intra-Company Transferee (POE)
*Change of Conditions*
It should be Intra-Company Transferee (POE) *Change of Conditions*
so by changing the select to the following resolved this particular finding:
replace(replace(case_desc,char(10),' '),char(13),'')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply