1 Topic by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Topic: Stripping characters out of comment fields

We have SQL that strips formatting (fonts etc) out of comment fields but also need to remove paragraph marks and tabs, both of which seem to cause problems when we export data to excel. We have to export to .xls, then open the spreadsheet and save it as .xslx to get round it. Saving to .xlsx creates a corrupt spreadsheet that can't be opened.

What would be the SQL to remove those characters?

Janet Simkin
British Lichen Society

Janet Simkin's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,620

Re: Stripping characters out of comment fields

[dbo].[ufn_TrimWhiteSpaces] should  do this. You should be able to use  [dbo].[ufn_TrimWhiteSpaces]([dbo].[ufn_RtfToPlaintext](COMMENT FIELD)) to do both at the same time.

Mike Weideli

MikeWeideli's Website

3 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Stripping characters out of comment fields

That was easier than I expected, but I'm not sure it is working. The query runs and says it has updated lots of rows, but if I then run it again it comes back with the same number of rows updated each time. The SQL I am using is, for instance:

update taxon_occurrence
set taxon_occurrence.comment = [dbo].[ufn_TrimWhiteSpaces]([dbo].[ufn_RtfToPlaintext](taxon_occurrence.comment))
where taxon_occurrence.comment like '%}%'


Is there something else I have to do as well to save the changes?

Janet Simkin
British Lichen Society

Janet Simkin's Website

4 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,620

Re: Stripping characters out of comment fields

Hi

I have run it on some test data and it does seem to be working, so I am not sure why it isn't working for you. Have a look at what is actually in the comments field by running   

Select   taxon_occurrence.comment from Taxon_Occurrence WHERE  taxon_occurrence.comment like '%}%'

Mike Weideli

MikeWeideli's Website

5 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Stripping characters out of comment fields

Yes, it is working. There seems to be a hard core of a few rows that don't get updated and so show in the results each time but the actual records that are reformatted are additional to that and the problem characters are all being stripped out. That has solved quite a number of problems for us, which is great.

Unfortunately the .xlsx spreadsheets are still corrupt and can't be opened, so something else must be causing that. Does anyone else have the same problem?

Janet Simkin
British Lichen Society

Janet Simkin's Website

6 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,620

Re: Stripping characters out of comment fields

I haven't noticed a problem in any spreadsheets I have output. Does it happen all the time or js  with some datasets. Is it only happening when comment are included.

Mike Weideli

MikeWeideli's Website