Importing a Constituent note revised in Excel creates error message when opening the note in RE

Jill Freidmutter

I am hoping that someone out there has faced this and discovered a solution. I have a constituent note in RE that I want to update but I don't want to lose the history, so I'm exporting the note to Excel, revising and concatenating various pieces into the new note, and importing the new note into RE. That all works perfectly. But when I open the note in RE I receive the message "The contents of this Notepad cannot be saved as they were entered and have been modified. Some content was lost due to conversion to Rich Text Format. To save this content as it was entered you may need to use the media functionality instead." I can click through the message and save the note, so it doesn't do any harm, and the message doesn't show up when I open the note a second time. Support reminded me to save the file as a csv and suggested that I clear the formatting before importing, which I've done, but the error is still there. Support says that the error should occur only once when the notepad is first opened, that they don't have a set cause for this so they cannot tell me why it is happening, and that there is no other solution except those provided. I am reluctant to import 18,000 notes into RE that have this glitch. Does anyone out there know how to avoid this particular glitch? Does anyone have experience living with this glitch and found it incidental and nothing to worry about?  Thank you for sharing your experience and wisdom.


PS: When I cut and paste the revised note from the Excel spreadsheet into the RE Notepad, it doesn't create the same error, just when importing it. I've done two import tests of 5 records so far, varying the type of carriage return used to no avail. e.g. CHAR(10) vs CHAR(13)

Steven Cinquegrana 2

Hi Jill,


My advice to anyone working with data is to avoid Excel like the plague.


I have nothing against Excel generally speaking; I use it all the time, for what it was made for. I just avoid it whenever I want the results to end up in a database.


Excel will "help" you by changing credit card numbers to exponentials, by screwing up your dates, by adding in hidden characters, by changing your character encoding, etc, etc.


When Blackbaud support suggested sticking with csv, I'm guessing they meant in the entire flow of your manipulation of the data. If at any point, it's been through Excel, this is probably where the issues have arisen.


I would approach things one of two ways:


1. Use a good, dedicated text editor such as Notepad++ for the entire process or, if you really need the functionality that Excel provides,

2. Work initially in Excel, then export to csv or txt and check the data again, in a good text editor such as Notepad++ before creating your import file(s).


Editors like Notepad++ have built-in encoding facilities plus a bunch of plug-ins available for validating your data. Eg, I use it a lot for editing PHP web content where it will alert you to errant BOM pages, EOL issues and the like if you set it up right.


Good luck!

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions


 

Ryan Hyde

For small numbers of notes, I copy the note portion from Excel to Notepad, remove any unexpected characters (usually there are leading and closing quotation marks that show up that weren't visible in the Excel cell) and copy them back. I almost never have a problem when I do this, though it's not 100% foolproof.


For 18k records, I would absolutely use Steven's method. Notepad++ is fantastic for a lot of things.

Jill Freidmutter

Steve, thanks so much for your detailed suggestions. I am not familiar with Notepad++. I'll need to do some research on how to do this. Would I concatenate the notes in Excel and then open and save the spreadsheet in Notepad++ to clear the formatting errors? Does Notepad++ have a spreadsheet format that will hold 18,000 rows/records? Can I save a Notepad++ sheet in csv format? 

Steven Cinquegrana 2

Hi again,

Notepad++ is a text editor like Microsoft Notepad, but open source, more powerful, supports plug-ins, selectable encodings, etc, etc. There are others out there but, personally, I stick with NP++ 'cos I know it and like it.


You'll need to have a read and a play with NP++ before you start and, of course, back up your original export for safety. What you do depends on the addendum. If it’s the same addendum for each note, then you shouldn't need to use Excel at all. Otherwise, yes, Excel, then NP++. You'll need to do some searching for non-printable characters, etc. Then check your encoding before saving and doing a test import, preferably to a copy of your RE DB with such a large import, especially the first time you go through such a manipulation. That's what I'd do, in any case.


Have fun!

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

 

Jill Freidmutter

Hi Steve, It's the same formula for each note, but not the same change. I'm concatenating into one note: new note (including date stamp and name of note writer), carriage return, original note date, original note author, original note. Perhaps this is something that Notepad++ can do. Gotta learn! Is there a resource you would recommend?  

Phillip Bicknell

I wonder if Raiser's would work with /n for carriage-return/enter? Because that's what's used for Address Lines on import.


+1 for NP++

-10 for Excel auto functions when handling data - but I'm used to it now

+100 for Gedit - if anyone here uses Linux - I do at home

+1000 for Vi or similar - wow, you're very dedicated :-)

Alicia Barevich

I haven't dealt with this particular problem, but I've been able to work around similar issues in Excel by using Google Sheets. I upload the Excel file, convert to Google Sheets, then download as CSV. If you're not used to working with csv files in Norepad++, this is likely much easier, if it works.