During a recent project, I needed to migrate 75 million records into Zoho Creator from a client's old legacy database. The files were in a series of CSVs, but I faced limitations with the standard 'Import Data' feature in the Reports module of Zoho Creator. You only have the capability to import up to 100,000 records and less than a 5MB file size.
This limitation meant that an alternative was required to import the data over a weekend prior to go live.
The Excel Data Migration tool that Zoho offer for migrating data is now out of support, only runs on Windows machines and under strain seems to fail consistently. I've raised this with the Zoho Creator team who have promised that a newer robust migration tool will be made available in the future.
So, after discussing with Zoho Creator and trying different methods, I've come up with a solution that seems to be the most effective way of importing many millions of records into Zoho Creator.
Use Google Sheets instead. You can connect Zoho Creator directly to Google Drive using the native integration (no impact on API counts either). Import your files into Google Drive and then whilst in your Zoho Creator report, use the Import Tool as normal.
Select "Cloud" as your Data Location and choose your connection. I've only tested Google Drive, but Zoho Docs, OneDrive, Dropbox and Box may have the same desired results.
The process will be the same as a standard import once it's attached the file. You select your mappings and proceed. Don't forget to switch off any workflows at this point especially if importing millions of records (speaks from experience!)
I keep the CSV record counts under 1M. This seems to work best and as long as you have the patience to monitor uploads, it will cause less stress if you need to rollback when record counts don't match.
For CSVs with many thousands of records, probably over 100k (Zoho Creator standard import limit), it will throw up an error, but don't worry, it's still importing the records. You can check this by refreshing the report view until you see the desired number of records imported.
Again, not ideal, but it works. So, ignore the error message and just click 'OK'
Zoho Creator has really opened up the solution for this particular client. CRM doesn't cater for many millions of records but we've now deployed a CRM Front End which brings all the associated benefits of an integrated system, but with the power and scalability of an unlimited data back-end in Zoho Creator.
Contact Us if you need some help with this process or you're a business thinking about migrating from your legacy database to a modern, dynamic, secure cloud-based solution.