Importing Pastel customer info into vTiger

We recently had to import some masterfile information from Pastel into vTiger for a client. Since a lot of SA companies use Pastel I thought I would write "how to" to help others to use vTiger and get a state of the art, open source, CRM system up and running.

This "how to" assumes a clean vTiger database i.e. a fresh vTiger install. It would not be hard to get the scripts to workwith a database that already has data. The scripts would need to be adjusted for the next crmentity sequence number.

Import Pastel CSV file into staging table.

I am not sure how the csv file was exported from Pastel, other than the export function is built into Pastels, so it may be that your file will differ. The file provided to us had 44 columns. The first thing to do is open the file in a text editor to get a feel for the data.

In our case it all looked ok, except for the perinnial commas and apostrophes in customer names. To prepare the file for import into MySQL I opened it in Open Office Calc. During the import specify that the text fields are delimited with quotes (") and the field delimiter is the comma(,).

Once imported the file must then be exported. Basically this means saving the file as a CSV file. File=>Save As=>customer.csv. Select the file extension "csv" from the file extension drop down box and, importantly, select the "edit filter" check box. Click "Save". Open Office prompt you to make sure you really mean CSV format, click "yes".

You will now be presented with a dialog box asking you for the delimiters to use. I use the | character as a field delimiter, leave the text delimeter field empty. The | character is unlikely to be in any of the fields so by choosing this character you ensure no hidden instances of your delimiter appear in the fields. This can be a real problem if you choose the comma as a field delimiter or single quotes as a text delimiter.

All of this is to get your file ready to import into a staging table on mysql. The next step is to create the staging table. When you open the file in Open Office count the number of columns. Then create a table with that number of columns and make all their data types varchar (255).

Here is the one I created.

CREATE TABLE `temptable` (
`col1` VARCHAR(255) ,
`col2` VARCHAR(255) ,
`col3` VARCHAR(255) ,
`col4` VARCHAR(255) ,
`col5` VARCHAR(255) ,
`col6` VARCHAR(255) ,
`col7` VARCHAR(255) ,
`col8` VARCHAR(255) ,
`col9` VARCHAR(255) ,
`col10` VARCHAR(255) ,
`col11` VARCHAR(255) ,
`col12` VARCHAR(255) ,
`col13` VARCHAR(255) ,
`col14` VARCHAR(255) ,
`col15` VARCHAR(255) ,
`col16` VARCHAR(255) ,
`col17` VARCHAR(255) ,
`col18` VARCHAR(255) ,
`col19` VARCHAR(255) ,
`col20` VARCHAR(255) ,
`col21` VARCHAR(255) ,
`col22` VARCHAR(255) ,
`col23` VARCHAR(255) ,
`col24` VARCHAR(255) ,
`col25` VARCHAR(255) ,
`col26` VARCHAR(255) ,
`col27` VARCHAR(255) ,
`col28` VARCHAR(255) ,
`col29` VARCHAR(255) ,
`col30` VARCHAR(255) ,
`col31` VARCHAR(255) ,
`col32` VARCHAR(255) ,
`col33` VARCHAR(255) ,
`col34` VARCHAR(255) ,
`col35` VARCHAR(255) ,
`col36` VARCHAR(255) ,
`col37` VARCHAR(255) ,
`col38` VARCHAR(255) ,
`col39` VARCHAR(255) ,
`col40` VARCHAR(255) ,
`col41` VARCHAR(255) ,
`col42` VARCHAR(255) ,
`col43` VARCHAR(255) ,
`col44` VARCHAR(255) 
);

Next you import your clean csv file as follows. Open mysql query browser or the command line and run

load data infile '/home/user/Desktop/customer.csv' into table temptable fields 
 terminated by '|' lines terminated by '\n';

In the case of the file I had there were duplicate entries. So to clean this up I created another staging table with the same structure as temptable above and called it temptable1. The I ran

insert into temptable1 select distinct * from temptable;

This effectively deduped (de-duplicated) the data. The staging table is finally ready for importing into vTiger.

Importing data into vTiger Accounts and Contact Tables

Every entry in vTiger requires a crmentity key. So we need to create some keys for our data before we import. To do this alter the temptable1 as follows:

ALTER TABLE `vtigercrm502`.`temptable1` ADD COLUMN `id` INT  NOT NULL 
AUTO_INCREMENT AFTER `col44`,
ADD COLUMN `id2` INT  NOT NULL DEFAULT 0 AFTER `id`,
ADD PRIMARY KEY(`id`);
update temptable set id2=id 

It turned out that I din't need the second ID columns (ID2). It was there in case I needed to start the ID column from a number other than 1 which would be the case if there were existing data in the vTiger database.

The following tables need to be updated for account import:

• vtiger_crmentity,
• vtiger_account,
• vtiger_accountbillads,
• vtiger_accountscf,
• vtiger_accountshipads, snf
• vtiger_tracker

Note: I mapped the columns from the source table to the target by inspection. It is a relatively straight forward mapping. We didn't have the source files columns names so we just had to use our common sense to do the mapping.

Also you will notice date time stamps appearing in various inserts. These should reflec the time you do the insert.

/* ****************** Update of accounts tables ***** */

INSERT INTO `vtiger_crmentity` select id2,1,1,0,'Accounts','','007-05-06 11:47:58',
'2007-05-06 1:47:58',NULL,NULL,0,1,0 from temptable;

INSERT INTO `vtiger_account`   select id2,col1,NULL,'--None--','--None--',0,'-None--',
'','','',col8,'',col37,'','','',NULL,'0','0' from temptable;

INSERT INTO `vtiger_accountbillads`  select id2,col4,col5,'South  Africa','',
concat(trim(col3),',',trim(col4),',',trim(col5)),'' from temptable;

INSERT INTO `vtiger_accountscf` select id2 from temptable;

INSERT INTO `vtiger_accountshipads`  select id2,col16,'','South Africa','','',
concat(trim(col15),',',trim(col16),',',trim(col17),',',trim(col18)) from temptable;

INSERT INTO `vtiger_tracker`  select NULL,1,'Accounts',id2,col1 from temptable;

The following tables need to be updated for contacts:

• vtiger_crmentity,
• vtiger_attachments,
• vtiger_contactdetails, = this table links the contact to an account
• vtiger_contactaddress,
• vtiger_contactscfs,
• vtiger_contactsubdetails,
  
• vtiger_customerdetails

/*-- 
--   ******************* Update for contact details *********************
--*/
Select @idmax:=max(id2) from temptable;
INSERT INTO `vtiger_crmentity` select id+@idmax,1,1,0,'Contacts','',
'2007-05-06 13:18:05','2007-05-06 13:18:05',NULL,NULL,0,1,0  from temptable;

INSERT INTO `vtiger_attachments` select id+@idmax,'',NULL,NULL,NULL 
from temptable;

INSERT INTO `vtiger_contactdetails` select id+@idmax,id2,'--None--',col10,'','',
col8,col36,','','','',NULL,NULL,NULL,NULL,'','0','0','Dollars','','0','0' from temptable;

INSERT INTO `vtiger_contactaddress`  select id+@idmax,col4,'','South Africa','','','','','',col5,
'','','' from temptable;

INSERT INTO `vtiger_contactscf` select id+@idmax from temptable;

INSERT INTO `vtiger_contactsubdetails`  select id+@idmax,'',col9,'','',NULL,0,0,
'--None--' from temptable;

INSERT INTO `vtiger_customerdetails` select id+@idmax,'0','2007-05-06',
'2008-05-06' from temptable;

Lastly you need to update the vtiger_crmentity_seq table. This table holds the next number for inserts into most of the vTiger tables. It should match the id of the last record you insert.

INSERT INTO `vtiger_crmentity_seq`  select max(id)+@idmax from temptable;