An article for myself really as I couldn't find much online to address the errors I encountered below but also to document the import process of a bunch of files into a ZohoCRM system.
Why?
Our use-case here is a data migration for a client from a legacy CRM to ZohoCRM. They have a lot of files they want attached to their respective records in CRM. This took me a while as our own documentation is scatty and the online forums weren't too helpful either.
How?
In the example below, we are going to document how to import files associated to CRM Contact records. There's first the preparation of an "Attachments.csv" file that needs the correct values in each column and the "Attachments.zip" containing the files to attach that needs some formatting as well:
Note that any IDs used in the snippets of code or screenshots below are made up.
the Attachments.csv
A data backup or export of the Attachments from ZohoCRM will return to you a CSV of attachments. View it to see what is expected of the reverse process, the import file.
The import file I got working had the following columns:
- Attachment Id: This needs to the unique filename of the actual file on disk (as opposed to "File Name" which is the file name as it is to be displayed in ZohoCRM). For example "5a4bed37590588baa5fc272219e8e553_image001.jpg" for Attachment ID and just "image001.jpg" for File Name.
- Attachment Owner Id: This is a "zcrm_" prefixed ID of the user who owns the attachment.
- Modified By Id: This is a "zcrm_" prefixed ID of the user who modified the attachment.
- Created By Id: This is a "zcrm_" prefixed ID of the user who created the attachment.
- Created Time: This is the created time of your file in the format that your CRM system is using (eg. "dd/MM/yyyy HH:mm:ss")
- Modified Time: This is the modified time of your file in the format that your CRM system is using (eg. "dd/MM/yyyy HH:mm:ss")
- File Name: This the file name as displayed in your CRM. For example "image001.jpg". This is different to Attachment ID which has to be unique as you may have many files called "image001.jpg" but for other records.
- Parent Id: The CRM Record ID you are going to attach the file to (eg. "zcrm_123450000000000123456").
the Attachments.zip
This needs to be a ZIP / compressed file of a folder called "Attachments" (uppercase A and the word Attachments - no other option). The Attachments folder must contain no subfolders and only unique filenames herein. Perhaps if you were doing this over several imports, you could use the same filenames, I didn't try this, instead my program generates a unique MD5 for each file with the filename appended. Because of MS Windows 11 limitations (any MS OS really), you need to ensure that long hyphens (#151) and some other funny characters are replaced out as the OS won't allow the file to be sent to a compressed folder.
the Import process
- Simply login to ZohoCRM > Go to Setup/Settings > Under Data Administration, select "Import" > Select Zoho CRM
- Now upload all the files needed for your import: this will be the Attachments.csv, Attachments.zip and then any CSV that includes the ZCRM Record IDs of the records to attach these files to. So for example, if you have a mix of files that will be attached to Contact records and Sales Orders, you will need to include at this step the "Contacts_001.csv" and "Sales Orders_001.csv" (preferably taken from a backup):
- Watch the spinning spinny progress animation or go and do something more productive:
- Ensure the CSV files are selected for mapping (in this case Attachments.csv and Contacts_001.csv - by the way, my Attachments.csv here is called "Attachments_001.csv"... I didn't do that, this process seems to have done that):
- On the next step, check your Attachments CSV has the mandatory columns and as previously mentioned, Attachment ID is the unique filename (as it is on disk) and File Name is the name displayed in CRM (can be same as on other records):
- Click on 'Save and Next' and you will be asked to check your other CSV files... The only correction I had here is to specify the date/time format:
- On the Review screen it tells you how many fields were mapped. Amazing but not as useful as it is interesting:
- Then you're into the penultimate step of the migration where you watch another but different progress animation which is possibly all your data being mapped, associated, and imported... I personally had to refresh the screen a few times after about 10k records as the browser webpage seemed to hang or get stuck. It just resumes where it left off and ultimately will display a notification saying your migration is complete!
- Lastly, you will be pleased that the files to attach are in your CRM but unfortunately so are a ton of duplicate contacts and whatever else you had to import. So go into a listview of the CRM Contacts and select "Deduplicate Contacts", then say compare by email and the foreign key (3rd-party unique ID for that contact).
Error(s) Encountered
- Could not find a matching parent record for this lookup field. - Parent Id: Ensure that CRM Record ID exists and that you are uploading the associated module CSV at the same time as the attachments (eg. if Parent Id is a CRM contact record, then include in the upload "Contacts_001.csv")
- The mandatory field 'Attachment Id' must be mapped: Give a unique value for the column attachment ID (filename column does not need to be unique) and ensure there are no subfolders.
- Uploaded files does not contain any attachments folder hence disassociating attachment module from migration.: "Attachments.zip" must be a zip of the folder "Attachments" (with capitalized "A") and have no subfolders with all files in the root.
- File not found. Please check the attachment ID.: The Attachment ID column must contain the unique field names. File name column can contain names used on other records.
Additional:
Here's an AHK script I wrote that produces a GUI that takes an external file where column A contains the key/ID of the Contact in the 3rd-party database and column B contains the filename as downloaded from the 3rd-party database. And an internal file where column A is the ZCRM Record ID (eg. if Contacts module then the CRM Contact ID) and column B contains the foreign key/id from the 3rd-party database. It then prompts for a folder containing the original files and will collapse any subfolders as well as rename the files to unique filenames.
; ; Language: English ; Platform: Microsoft Windows 11 ; Author: Joel Lipman ; Version: 1.0 ; Description: This program will generate a clean Attachments.zip and its Attachments.csv to upload to Zoho. v_ChangeLogText = ( Date (v1.0): 10/01/2023 - Initial Release Date (???): ??? - Issue: ) ; -------------------------------------------------------------------------------------- ; Set program Defaults ; -------------------------------------------------------------------------------------- #NoEnv #Warn SendMode Input SetWorkingDir %A_ScriptDir% #SingleInstance force v_Company := "Joel Lipman .Com" v_CompanyKey := StrReplace( v_Company, " ", "_" ) v_Title := v_Company " - Generate ZCRM Attachments" v_SubTitle := "Generate ZCRM Attachments" v_Desc := "This program will generate a clean attachments.zip to upload to Zoho." v_TempDir := A_Temp v_WorkDir := A_ScriptDir v_ThisProgram := A_ScriptName v_AccessProgram := A_ScriptDir "\" A_ScriptName v_DefaultVersion := "1.0" v_GuiBgColor := "FFFFFF" v_GuiWidth := 300 v_GuiHeight := 120 v_GuiOptions := "-Caption -Border +OwnDialogs +AlwaysOnTop -ToolWindow" v_GuiXpos := A_ScreenWidth - v_GuiWidth SysGet, v_WorkArea, MonitorWorkArea v_GuiYpos := v_WorkAreaBottom - v_GuiHeight - 2 ; Output file names v_ProcessID := DllCall("GetCurrentProcessId") v_FileName_Errors := "Errors_" v_ProcessID v_FileName_Success := "Success_" v_ProcessID v_FileName_Prep := "Prep_" v_ProcessID ; to be set on setup (leave blank) v_TotalHours := 0 v_YourProfile := "" v_YourEmail := "" v_YourBrowser := "" v_YourName := "" v_Message := "" v_YourDate := A_Year "-" A_MM "-" A_DD b_KeepOpen := false v_RunFolder := A_ScriptDir "\Run_" v_YourDate "_" A_Hour "-" A_Min ; -------------------------------------------------------------------------------------- ; Setup tray icon Menu, Tray, Icon, %A_ScriptDir%\favicon.ico,,1 Menu, Tray, Add Menu, Tray, Add, Exit, CloseMe Menu, Tray, Tip, %v_Title% ; -------------------------------------------------------------------------------------- ; Setup if not setup already ; -------------------------------------------------------------------------------------- ; Display Tooltip DisplayGUI: ; add menu Menu, FileMenu, Add, Select External File..., SelectExternal Menu, FileMenu, Add, Select Internal File..., SelectInternal Menu, FileMenu, Add, Select Attachments Folder..., SelectAttachmentsFolder Menu, FileMenu, Add Menu, FileMenu, Add, &Reload App, Restart Menu, FileMenu, Add, E&xit, CloseMe Menu, HelpMenu, Add, &Help..., AboutGUIShow Menu, MainMenu, Add, &File, :FileMenu Menu, MainMenu, Add, &Help, :HelpMenu v_ListViewHeaders := "Date Created|Date Modified|Foreign Key|Zoho CRM ID|Original File with Path|New File with Path|Original Filename|New Filename|Display Filename|KB File Size" ; add gui Gui, New, -MinimizeBox -MaximizeBox +OwnDialogs, %v_Title% Gui, Color, FFFFFF Gui, Font, s10 w700, Verdana Gui, Add, Text, vHeading x10 y10 w400, %v_SubTitle% Gui, Font, s7 w400 c999999, Verdana Gui, Menu, MainMenu Gui, Add, Text, vAttachmentInstruction1 x12 y35 w400, Select the file which contains the foreign key and the file name(s) Gui, Add, Text, vAttachmentInstruction2 x462 y35 w400, Select the file which contains the foreign key and the ZCRM ID Gui, Add, Text, vAttachmentInstruction3a x12 y85 w400, Specify what column holds the foreign key (eg. 2) Gui, Add, Text, vAttachmentInstruction3b x12 y105 w400, Specify what column holds the file name (eg. 3) Gui, Add, Text, vAttachmentInstruction4a x462 y85 w400, Specify what column holds the ZCRM ID (eg. 5) Gui, Add, Text, vAttachmentInstruction4b x462 y105 w400, Specify what column holds the foreign key (eg. 4) Gui, Add, Text, vAttachmentInstruction5 x12 y135 w400, Specify the folder containing the files Gui, Font, s8 w400 c000000, Verdana Gui, Add, Edit, r1 vMyExternalFile w300 x10 y55, Gui, Add, Edit, r1 vMyExternalFileColKey w30 x310 y80, Gui, Add, Edit, r1 vMyExternalFileColFile w30 x310 y105, Gui, Add, Edit, r1 vMyExternalFileColPrimaryID w30 x760 y80, Gui, Add, Edit, r1 vMyExternalFileColForeignID w30 x760 y105, Gui, Add, Edit, r1 vMyInternalFile w300 x460 y55, Gui, Add, Edit, r1 vMySelectedFolder w300 x10 y150, Gui, Add, Button, w120 x310 y55 gSelectExternal, External File... Gui, Add, Button, w120 x760 y55 gSelectInternal, Internal File... Gui, Add, Button, w100 x310 y150 gSelectAttachmentsFolder, Browse... Gui, Add, Button, w100 x410 y150 gPrepareAttachments, Prepare... Gui, Add, Button, w100 x510 y150 gGenerateAttachments, Generate... Gui, Add, ListView, vTimesheetOverviewList +Grid x10 y180 w880 h390 Checked gMyListView, %v_ListViewHeaders% Gui, Add, StatusBar Gui, Show, Center w900 h600, %v_Title% SB_SetParts(500, 100, 100, 120, 80) SB_SetText("Ready.", 1) Return ; -------------------------------------------------------------------------------------- ; Sub-routines SelectAttachmentsFolder: FileSelectFolder, v_BrowsedFolder, %A_MyDocuments%,,3 if v_BrowsedFolder != GuiControl,, MySelectedFolder, %v_BrowsedFolder% Return SelectExternal: FileSelectFile, v_SelectedExternalFile, 3, %A_MyDocuments%, Open a File, Comma Separated Values File (*.csv) if v_SelectedExternalFile != GuiControl,, MyExternalFile, %v_SelectedExternalFile% Return SelectInternal: FileSelectFile, v_SelectedInternalFile, 3, %A_MyDocuments%, Open a File, Comma Separated Values File (*.csv) if v_SelectedInternalFile != GuiControl,, MyInternalFile, %v_SelectedInternalFile% Return GenerateAttachments: FileCreateDir, %v_RunFolder%\Attachments GuiControlGet, v_FolderToSearch, , MySelectedFolder v_FilesCopied := 0 v_FilesTotal := 0 ; attachments csv headings v_CsvLine := "Attachment Id,Attachment Owner Id,Modified By Id,Created By Id,Created Time,Modified Time,File Name,Parent Id`r`n" FileAppend, %v_CsvLine%, %v_RunFolder%\Attachments.csv ; errors csv headings v_CsvLine := "Date Created,Date Modified,FK,Zoho CRM ID,Original File Name and Path,New File Name and Path,Original Filename,New Filename,FileSize (KB),Reason`r`n" FileAppend, %v_CsvLine%, %v_RunFolder%\Errors.csv Loop % LV_GetCount() { v_FilesTotal := v_FilesTotal + 1 RowNumber := A_Index ;get first selected row RowChecked := LV_GetNext(RowNumber - 1 , "Checked" ) If ( RowNumber = RowChecked ) { v_FilesCopied := v_FilesCopied + 1 LV_GetText(v_FilePathFrom, A_Index, 5) LV_GetText(v_FilePathTo, A_Index, 6) FileCopy, %v_FilePathFrom%, %v_FilePathTo% SB_SetText("Copying Attachment #" A_Index, 1) LV_GetText(v_Col01, A_Index, 8) LV_GetText(v_Col02, A_Index, 2) ; overwriting created time with modified time LV_GetText(v_Col03, A_Index, 2) LV_GetText(v_Col04, A_Index, 9) LV_GetText(v_Col05, A_Index, 4) v_CsvLine := v_Col01 ",zcrm_516000000000000001,zcrm_516000000000000001,zcrm_516000000000000001," v_Col02 "," v_Col03 "," v_Col04 "," v_Col05 "`r`n" FileAppend, %v_CsvLine%, %v_RunFolder%\Attachments.csv } Else { LV_GetText(v_Col01, A_Index, 1) LV_GetText(v_Col02, A_Index, 2) LV_GetText(v_Col03, A_Index, 3) LV_GetText(v_Col04, A_Index, 4) LV_GetText(v_Col05, A_Index, 5) LV_GetText(v_Col06, A_Index, 6) LV_GetText(v_Col07, A_Index, 7) LV_GetText(v_Col08, A_Index, 8) LV_GetText(v_Col09, A_Index, 9) LV_GetText(v_Col10, A_Index, 10) v_Reason := "Unknown" if(v_Col09 < 1) { v_Reason := "File Size Too Small (less than 1Kb)" } if(v_Col04 == "-") { v_Reason := "File not associated to a Zoho CRM Record" } v_CsvLine := v_Col01 "," v_Col02 "," v_Col03 "," v_Col04 "," v_Col05 "," v_Col06 "," v_Col07 "," v_Col08 "," v_Col09 "," v_Col10 "," v_Reason "`r`n" FileAppend, %v_CsvLine%, %v_RunFolder%\Errors.csv } } SB_SetText("Copied " v_FilesCopied " of " v_FilesTotal " file(s). Ready.", 1) MsgBox, 0,Done, Copied %v_FilesCopied% of %v_FilesTotal% file(s) Return MyListView: if (A_GuiEvent = "DoubleClick") { LV_GetText(RowText, A_EventInfo, 4) Clipboard := RowText MsgBox, 0, %RowText% } return PrepareAttachments: ; init v_FileCount := 0 v_FilesToProcessCount := 0 v_FKCountTotal := 0 v_PKCountTotal := 0 GuiControlGet, v_FolderToSearch, , MySelectedFolder LV_Delete() ; create directory and log file FileCreateDir, %v_RunFolder% v_CsvLine := StrReplace(v_ListViewHeaders,"|", ",") ",Process`r`n" FileAppend, %v_CsvLine%, %v_RunFolder%\Log.csv ; Loop through directory of files Loop, Files, %v_FolderToSearch%\*.* , R { ; init v_OriginalFile := A_LoopFileName v_OriginalFileFull := A_LoopFileFullPath v_OriginalFilePath := StrReplace(A_LoopFileFullPath, v_FolderToSearch "\", "") ; transform FormatTime, v_TimeModified, %A_LoopFileTimeModified%, dd/MM/yyyy hh:mm:ss FormatTime, v_TimeCreated, %A_LoopFileTimeCreated%, dd/MM/yyyy hh:mm:ss ; format new file name (path) v_NewFile := v_OriginalFilePath if(InStr(v_NewFile, "\")>0) { v_NewFile := StrReplace(v_NewFile, "\", " ") } v_NewFile := SubStr(v_NewFile, 1, InStr(v_NewFile, ".",, -1)-1) v_DisplayName := A_LoopFileName ; if file names begin with exclamation mark (for saleslogix) if(InStr(v_NewFile, "!")>0) { v_NewFilePt1 := SubStr(v_NewFile, InStr(v_NewFile, "!")+1, 12) v_NewFilePt2 := SubStr(v_NewFile, 14) v_NewFile := v_NewFilePt2 " " v_NewFilePt1 v_DisplayName := v_NewFilePt2 "." A_LoopFileExt v_NewFile := v_NewFile "." A_LoopFileExt } v_NewFile := fn_Slugify(v_NewFile) v_NewFile := fn_MD5(v_NewFile, true) "_" v_DisplayName StringLower, v_NewFile, v_NewFile ; find the foreign key v_ForeignKey := "-" v_FKCount := 0 GuiControlGet, SearchExtFile,, MyExternalFile Loop, Read, %SearchExtFile% { a_ColumnValues := StrSplit(A_LoopReadLine, ",") v_ValueToSearchFor := StrReplace(a_ColumnValues[2], """", "") if(v_ValueToSearchFor == v_OriginalFilePath) { v_ForeignKey := StrReplace(a_ColumnValues[1], """", "") v_FKCount := v_FKCount + 1 SB_SetText("Foreign Row #" v_FKCount, 2) break } } ; find the Zoho key v_ZohoKey := "-" v_PKCount := 0 GuiControlGet, SearchIntFile,, MyInternalFile Loop, Read, %SearchIntFile% { a_ColumnValues := StrSplit(A_LoopReadLine, ",") v_ValueToSearchFor := StrReplace(a_ColumnValues[2], """", "") if(v_ValueToSearchFor == v_ForeignKey) { v_ZohoKey := StrReplace(a_ColumnValues[1], """", "") v_PKCount := v_PKCount + 1 SB_SetText("Zoho Row #" v_PKCount, 2) break } } ; counters v_FkCountFound := (v_ForeignKey != "-") ? 1 : 0 v_FKCountTotal := v_FKCountTotal + v_FkCountFound v_PkCountFound := (v_ZohoKey != "-") ? 1 : 0 v_PKCountTotal := v_PKCountTotal + v_PkCountFound ; tick checkbox to process or not (if filesize is less than 1kb than no) v_LogProcessNote := "No" v_FileSizeKB := A_LoopFileSizeKB v_RowOptions := "-Check" if(v_FileSizeKB > 1) { if(v_ZohoKey != "-") { v_LogProcessNote := "Yes" v_RowOptions := "+Check" v_FilesToProcessCount := v_FilesToProcessCount + 1 } } ; add row to listview LV_Add(v_RowOptions, v_TimeCreated, v_TimeModified, v_ForeignKey, v_ZohoKey, A_LoopFileFullPath, v_RunFolder "\Attachments\" v_NewFile, v_OriginalFilePath, v_NewFile, v_DisplayName, A_LoopFileSizeKB) ; increment v_FileCount := v_FileCount + 1 SB_SetText("Scanning Directory... Attachment #" A_Index, 1) ; output to CSV LV_GetText(v_Col01, A_Index, 1) LV_GetText(v_Col02, A_Index, 2) LV_GetText(v_Col03, A_Index, 3) LV_GetText(v_Col04, A_Index, 4) LV_GetText(v_Col05, A_Index, 5) LV_GetText(v_Col06, A_Index, 6) LV_GetText(v_Col07, A_Index, 7) LV_GetText(v_Col08, A_Index, 8) LV_GetText(v_Col09, A_Index, 9) LV_GetText(v_Col10, A_Index, 10) v_CsvLine := v_Col01 "," v_Col02 "," v_Col03 "," v_Col04 "," v_Col05 "," v_Col06 "," v_Col07 "," v_Col08 "," v_Col09 "," v_Col10 "," v_LogProcessNote "`r`n" FileAppend, %v_CsvLine%, %v_RunFolder%\Log.csv } LV_ModifyCol() LV_ModifyCol(5, 0) LV_ModifyCol(6, 0) v_ForeignKeyPercent := Round((v_FKCountTotal / v_FileCount) * 100, 2) v_ZohoKeyPercent := Round((v_PKCountTotal / v_FileCount) * 100, 2) v_ProcessablePercent := Round((v_PKCountTotal / v_FileCount) * 100, 2) SB_SetText("FK: " v_ForeignKeyPercent "%", 2) SB_SetText("PK: " v_ZohoKeyPercent "%", 3) SB_SetText("Can process " v_FilesToProcessCount " of " v_FileCount " file(s). Ready.", 1) Return AboutGUIShow: Return ; -------------------------------------------------------------------------------------- ; FUNCTIONS fn_Slugify(v_Ugly) { StringLower, v_Ugly, v_Ugly v_Ugly := RegExReplace(v_Ugly, "/\s+/g", "_") v_Ugly := RegExReplace(v_Ugly, "/[^\w\_]+/g", "") v_Ugly := RegExReplace(v_Ugly, "/\_\_+/g", "_") v_Ugly := RegExReplace(v_Ugly, "/^_+/", "") v_Ugly := RegExReplace(v_Ugly, "/_+$/", "") return v_Ugly } fn_MD5(string, case := False) ; by SKAN | rewritten by jNizM { o:="" static MD5_DIGEST_LENGTH := 16 hModule := DllCall("LoadLibrary", "Str", "advapi32.dll", "Ptr") , VarSetCapacity(MD5_CTX, 104, 0), DllCall("advapi32\MD5Init", "Ptr", &MD5_CTX) , DllCall("advapi32\MD5Update", "Ptr", &MD5_CTX, "AStr", string, "UInt", StrLen(string)) , DllCall("advapi32\MD5Final", "Ptr", &MD5_CTX) loop % MD5_DIGEST_LENGTH o .= Format("{:02" (case ? "X" : "x") "}", NumGet(MD5_CTX, 87 + A_Index, "UChar")) return o, DllCall("FreeLibrary", "Ptr", hModule) } ;https://autohotkey.com/boards/viewtopic.php?f=6&t=21 ; -------------------------------------------------------------------------------------- ; EXIT FUNCTIONS ; Close About CloseAbout: Gui, AboutGUI:Destroy Return ; Restart script Restart: Reload Return ; Pause script Pause: Pause Return ; Terminate script Exit: GuiClose: OverviewGUIGuiClose: CloseMe: ExitApp Return
Source(s):
Category: Zoho :: Article: 828