dangers of copying to excel from ssms

• skeezwiz

  SSC Eights!

  Points: 896

  More actions

  April 28, 2011 at 8:38 pm

  #249904

  sql 2008 & excel 2010

  Been using the copy all records from sql grid to excel for long time but recently found some funny text combinations (in comments/description/feedback varchar columns) when copied to excel, seem to corrupt the output.

  select 1,'"wet season is here','=38489'

  union all

  select 2,'wef q3t4 q3 truireyty895','"45y45y489'

  union all

  select 3,'="t u7it78i ruireyty895','qeyq45y'

  union all

  select 4,'" today is warm o 45ty247895','735673567356489'

  union all

  select 5,'hot season 789hstrh strh wr5','=45464'

  Only records 1,3,4 are copied and even these are not copied correctly to excel!

  How reliable is this feature and whether people should be aware of its problems.

  Does anybody have any known black list characters to find in sql columns that cause this?

  I was playing around with "use text import wizard" and after a few tries got it to export correctly, but doesnt guarantee full copy success.

  Note: using import/export GUI tool has no problems.

  Regards

• KevinGurney

  SSCommitted

  Points: 1550

  More actions

  April 29, 2011 at 1:44 am

  #1317684

  In my experience Excel tries to be 'clever' with formatting and presentation.

  If it 'recognises' something then it will pre-format.

  Usually it gets it right, but sometimes it can mess up your output.

  A general rule is, if the output is critical and/or needs to be moved somewhere after its stay in Excel, then using the Export tool is a wise move

  as it explicitly sets what the actual format should be rather than allowing Excel to guess.

  HTH.

  ---------------------------------------------
  If data can screw you, it will; never assume the data are correct.

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  April 29, 2011 at 11:51 am

  #1317898

  I think you're running into issues because of the double-quotes. If you escape those by doubling them up I think you'll have better results...like this:

  select 1,'""wet season is here','=38489'

  union all

  select 2,'wef q3t4 q3 truireyty895','""45y45y489'

  union all

  select 3,'=""t u7it78i ruireyty895','qeyq45y'

  union all

  select 4,'"" today is warm o 45ty247895','735673567356489'

  union all

  select 5,'hot season 789hstrh strh wr5','=45464'

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato