You need to sign in to do that
Don't have an account?
aam1r
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?
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
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
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!