function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
aam1raam1r 

Moving Notes to ContentNotes

Hi, I've exported all teh notes from an Org and want to import them as ContentNotes (Enhanced Notes).  The problem i'm facing is that the export csv file contains the notes along with all other system fields as expected.  However, when i want to import into ContentNotes the Content field is expecting a directory path to an individual file (eg. txt) and does not accept a simple field mapping.  How can i work around this?  Is there a way of exporting Notes as txt files?
Best Answer chosen by aam1r
aam1raam1r
OK, I managed to resolve this. And here is how i did it...

1. Exported all Notes into a csv file
2a. Opened the export file (File1) and created a row called Row Number, into which i then populated 1, 2, 3, etc. all the way to the last row of data. I then created another Column called Content and used a Concatonate formula to create an entry for each row using the string 'C:\Temp\Row ', the Row Number cell and finally '.txt'.  This resulted in a column with 'C:\Temp\Row 1.txt', 'C:\Temp\Row 2.txt', etc.
2b. I also created a Column called ShareType containing 'i' for every row (see developer notes to chose your prefered one, for some rows i had to use 'v' for viewer).
2c. Another column called Visibility and set every row to AllUsers (again, see developer notes to chose your prefered one)
3. I then saved the file (File1) and also saved a copy of the file (File2)
4. I opened File2 and and deleted all the columns except the one containing the Description, ie. the actual contents of the note.  I also deleted the first Row (of headers).
5. Saved the file (File2)
6. In File2, again, i created a VB script using some cool suggestions by other people online + a slight tweak of my own - Press Alt+F11, Click Tools, then Marco... and insert this in to the form:

' ******************************************************************************************************
Sub File_save()
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
' for large files, if progream quits half way, edit A1 here to the next file required to continue.
For Each cell In Rng
Num = cell.Row
 
 Range("A" & Num).EntireRow.Copy
 Workbooks.Add
 ActiveSheet.Paste
 Application.CutCopyMode = False
 ActiveWorkbook.SaveAs Filename:="C:\Temp\Row " & Num & ".txt", _
 FileFormat:=xlTextPrinter, CreateBackup:=False
 ActiveWorkbook.Close True  'otherwise a excell window will remain open for each row!!!!
 
 ThisWorkbook.Activate
 Range("A" & Num).EntireRow.Select
Next
End Sub
' ******************************************************************************************************

7. I created the directory 'C:\Temp\', before running the script (click the green Play button)
8. For every row a file called Row n.txt was created in the Temp directory (where n is the number of the Row in File1, and this file contained the body of the note.
9. Now i imported the File1 into Note(ContentNote) using dataloader, mapping the Content field and the Title field.
10. Once imported, i used the success file to import into Content Document Link, mapping the ContentDocumentId to ID, LinkedEntityId to ParentID, Sharetype and Visibility

Now i had all the notes imported in to New Notes, but not without some glitches.  I found that some larger notes were trimmed to a single word (not good). I also found that i could not map the audit fields into the new Notes, although i have the permissions and the settings to do so.  So, in File1 i created a new column for the body of the note called New Body and used c combination of a User export file, VLookup and Concatonation to create a string to append to each note, which contained the audit info required.  it gave me something along this:

'Body of text, blah blah.

[Created by So-and So on 2016-11-11:21:00, Modified by So And-So on 2016-12-12:21:00]'

Anyways, although i couldn't find a fix for the missing/trimmed text in some of the notes i thought i'd post this as it might just help someone else and could lead to teh same or someone else finding the missing piece.
Enjoy!!

aamir

All Answers

aam1raam1r
OK, I managed to resolve this. And here is how i did it...

1. Exported all Notes into a csv file
2a. Opened the export file (File1) and created a row called Row Number, into which i then populated 1, 2, 3, etc. all the way to the last row of data. I then created another Column called Content and used a Concatonate formula to create an entry for each row using the string 'C:\Temp\Row ', the Row Number cell and finally '.txt'.  This resulted in a column with 'C:\Temp\Row 1.txt', 'C:\Temp\Row 2.txt', etc.
2b. I also created a Column called ShareType containing 'i' for every row (see developer notes to chose your prefered one, for some rows i had to use 'v' for viewer).
2c. Another column called Visibility and set every row to AllUsers (again, see developer notes to chose your prefered one)
3. I then saved the file (File1) and also saved a copy of the file (File2)
4. I opened File2 and and deleted all the columns except the one containing the Description, ie. the actual contents of the note.  I also deleted the first Row (of headers).
5. Saved the file (File2)
6. In File2, again, i created a VB script using some cool suggestions by other people online + a slight tweak of my own - Press Alt+F11, Click Tools, then Marco... and insert this in to the form:

' ******************************************************************************************************
Sub File_save()
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
' for large files, if progream quits half way, edit A1 here to the next file required to continue.
For Each cell In Rng
Num = cell.Row
 
 Range("A" & Num).EntireRow.Copy
 Workbooks.Add
 ActiveSheet.Paste
 Application.CutCopyMode = False
 ActiveWorkbook.SaveAs Filename:="C:\Temp\Row " & Num & ".txt", _
 FileFormat:=xlTextPrinter, CreateBackup:=False
 ActiveWorkbook.Close True  'otherwise a excell window will remain open for each row!!!!
 
 ThisWorkbook.Activate
 Range("A" & Num).EntireRow.Select
Next
End Sub
' ******************************************************************************************************

7. I created the directory 'C:\Temp\', before running the script (click the green Play button)
8. For every row a file called Row n.txt was created in the Temp directory (where n is the number of the Row in File1, and this file contained the body of the note.
9. Now i imported the File1 into Note(ContentNote) using dataloader, mapping the Content field and the Title field.
10. Once imported, i used the success file to import into Content Document Link, mapping the ContentDocumentId to ID, LinkedEntityId to ParentID, Sharetype and Visibility

Now i had all the notes imported in to New Notes, but not without some glitches.  I found that some larger notes were trimmed to a single word (not good). I also found that i could not map the audit fields into the new Notes, although i have the permissions and the settings to do so.  So, in File1 i created a new column for the body of the note called New Body and used c combination of a User export file, VLookup and Concatonation to create a string to append to each note, which contained the audit info required.  it gave me something along this:

'Body of text, blah blah.

[Created by So-and So on 2016-11-11:21:00, Modified by So And-So on 2016-12-12:21:00]'

Anyways, although i couldn't find a fix for the missing/trimmed text in some of the notes i thought i'd post this as it might just help someone else and could lead to teh same or someone else finding the missing piece.
Enjoy!!

aamir
This was selected as the best answer
aam1raam1r
Oh, one more thing, i found that the script would fail randomly at some point.  To continue from where it failed, see which file number was created last, eg. Row 1335.txt  You then re-open the script Alt+F11 and edit the line:

Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))  to 
Set Rng = Range(Range("A1336"), Range("A" & Rows.Count).End(xlUp))

..and re-run the script.  This is essential as you don't want to overwrite the ones created thus far!!  You also don't want to save the successfully cretaed files to another location and re-run the script without this above mentioned change - just to avoid overwriting the files.  This would cause a mess.. trust me!