[Vtigercrm-developers] importing comments
Juan C. Dulanto
juan at compuxpert.com
Sat Jan 19 21:00:01 PST 2013
Importing Comments into vTiger 5.3.0 - Windows Users
I realized the import/export icon is greyed-out on the module comments, just when I needed to import over 10,000 comments (notes) to our vTiger windows-based server.
I searched for clues but I did not find any recent solutions, only requests for information.
I spent a few hours of my weekend and came up with a working solution. It involves working directly with the database, but it is not difficult at all! I will share it with you.
The data needs to be imported into 3 tables: vtiger_modcommentscf, vtiger_modcomments and vtiger_ crmentity, but first you MUST take care of the current vtiger_crmentity_seq id value.
STEP 1
Go to vtiger_crmentity_seq table (I recommend using phpMyAdmin) and take note of the last used id.
Let's assume its value is 1000.
Realize that crmentity_seq is a sequential auto-incremented number, and if you don't update this table when importing your comments, then your data may be overwritten by the next user of vtiger or there may be double ids.
If you want to import 500 new comments, the id must be incremented by 500, so you must change the id to 1500.
The easiest way to do it is by using phpMyAdmin. Go ahead and change its id to the new value now.
STEP 2
Go to vtiger_modcommentscf and select to export this table.
Use phpMyAdmin to open this table, and select "Export". Include these parameters:
Export Method = Custom - display all possible options.
Rows = Dump some rows; Number of rows 10; Row to begin at 0.
Format = CSV for MS Excel.
Format-specific options = Put columns names in the first row.
[cid:image002.png at 01CDF6A1.172AC960]
Note: Do the same with vtiger_modcomments and vtiger_crmentity. This way you will have a better understanding of the structure of each table involved in this process.
Next, create your own excel-based data and name it vtiger_modcommentscf.csv. It should look like:
id
1001
1002
1003
1004
...
1500
Now, import your new data. Remember to always skip row 1 (column names) and use ";" as column separator value.
[cid:image004.png at 01CDF6A1.172AC960]
STEP 3
Prepare an excel-based csv sheet, with the new values for vtiger_modcomments. It should look like:
modcommentsid
commentcontent
related_to
parent_comments
1001
Any comment content 1001
10
1002
More comment content 1002
10
1003
More comment content 1003
10
1004
More comment content 1004
12
...
1500
Final comment content 1500
12
"commentcontent" is the actual comment that you wish to import.
"related to value" is the contactid (if comment is related to a contact), which can be obtained from the table vtiger_contactdetails.
Save it as vtiger_modcomments.csv and then Import it. Remember to always skip row 1 (column names) and use ";" as column separator value.
STEP 4
Finally, you need to prepare your vtiger_crmentity excel-based csv file, with the proper data, and as equally important: the proper format. It should look like this:
crmid
smcreatorid
smownerid
modifiedby
setype
description
createdtime
modifiedtime
viewedtime
status
version
presence
deleted
1001
1
1
1
ModComments
NULL
2013-01-19 20:15:00
2013-01-19 20:15:00
NULL
NULL
0
1
0
1002
1
1
1
ModComments
NULL
2013-01-19 20:15:01
2013-01-19 20:15:01
NULL
NULL
0
1
0
1003
1
1
1
ModComments
NULL
2013-01-19 20:15:02
2013-01-19 20:15:02
NULL
NULL
0
1
0
1004
1
1
1
ModComments
NULL
2013-01-19 20:15:03
2013-01-19 20:15:03
NULL
NULL
0
1
0
...
1500
1
1
1
ModComments
NULL
2013-01-19 20:15:05
2013-01-19 20:15:05
NULL
NULL
0
1
0
Pay particular attention to the createdtime and modifiedtime format. It should be "yyyy-mm-dd hh:mm:ss" (year-month-day hour:minutes:seconds). I tried saving this format on excel, by using custom format:
[cid:image006.jpg at 01CDF6A1.172AC960]
But every time I tried to save the file as csv: the format was lost!
I ended up downloading (free) Open Office and manipulating the data in "calc" and saving it as csv.
crmid;smcreatorid;smownerid;modifiedby;setype;description;createdtime;modifiedtime;viewedtime;status;version;presence;deleted
1001;1;1;1;ModComments;NULL;2013-01-19 20:15:00;2013-01-19 20:15:00;NULL;NULL;0;1;0
1002;1;1;1;ModComments;NULL;2013-01-19 20:15:01;2013-01-19 20:15:01;NULL;NULL;0;1;0
1003;1;1;1;ModComments;NULL;2013-01-19 20:15:02;2013-01-19 20:15:02;NULL;NULL;0;1;0
1004;1;1;1;ModComments;NULL;2013-01-19 20:15:03;2013-01-19 20:15:03;NULL;NULL;0;1;0
...
1500;1;1;1;ModComments;NULL;2013-01-19 20:15:05;2013-01-19 20:15:05;NULL;NULL;0;1;0
Finally, import your table with new data now. Remember to always skip row 1 (column names) and use ";" as column separator value.
After finishing and verifying your import, created a new comment using vTiger: The new comment should receive an id equal to your last imported id plus 1, i.e. 1501.
That's it. It is not difficult at all. You just need to take some time to prepare your data properly.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130120/03746ed9/attachment-0001.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 318062 bytes
Desc: image002.png
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130120/03746ed9/attachment-0002.png
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 365101 bytes
Desc: image004.png
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130120/03746ed9/attachment-0003.png
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.jpg
Type: image/jpeg
Size: 24596 bytes
Desc: image006.jpg
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130120/03746ed9/attachment-0001.jpg
More information about the vtigercrm-developers
mailing list