Import template

[Laimonas]

When making a periodic data import each week, every time we have to map every field (approx 20 fields) in the "Step 2" import window. It would be great if there would be an "Import template", which contains preselected fields mapping.

[Kyle]

If you change your import file to have the same field name headers as the object you are importing to it will auto map them. This will probably be the easiest solution.

[Laimonas]

Kyle in some cases yes, that would be a solution. But If you have in mind that I should change the field name headers in another system, which is issuing the document - I am not always in control of another system, that I could change its fields. Or if I have to change the name headers in the document every time, so it would be the same as mapping every time...

[yuri]

BTW It's possible to re-use previous import params when importing using the console command.

[yuri]

I forgot that it's possible to use params from a previous import via UI.

[Laimonas]

yuri that's great, but it works only with the last import. We have 3 or 4 different periodical documents that we import, so it would be great to have an option to choose a template for each document.

[yuri]

> but it works only with the last import

Did you check it? I don't believe it's true.

[Laimonas]

yuri ok, I think I got it: Administration -> Import -> "Import Results" (button on the top right corner) -> choose an import from the list -> "New import with the same params" (3 dots button on the top right corner)? Is it like that?

[yuri]

Yes. This should work for older imports too. Not only the last one.

[acuf]

Hi yuri

if I understood correctly what Laimonas meant, may be very usefull have possibility to save import template, I know many companies (not big) that buy lead from different suppliers, than have to import 3 or 4 or more (by day) different file with not the same informations inside ... with reimport from history is fine but not so easy to use because all of different imports populate same entity, than user can't recognise wich import select in the list

Another plus may be can give import function to regular user ... in our customer we are use to give realy few admin user to customer people (because is daungerous) but they have to be free to import data (with preconfigured template) by them self

Thank you for your great work with EspoCRM

[item]

Hello,
i do each month big, very big import ..
1 csv => +- 180000 record,
300csv => total record same as csv 1

All is without human interaction.

create a new entity ... put 2 new field :
- enum => template1, template2 and so
- file => your csv file

create a hook afterSave
if entity->isNew
if type == 1 .. create new jobs type1
if type == 2 .. create new job type 2

create Job 1 for type 1
read csv
blabla

create job 2 for type 2
read csv
blabla

https://www.php.net/manual/fr/function.fgetcsv.php

https://docs.espocrm.com/development...scheduling-job

You can just duplicate job and add new value to type enum... for all new type of csv.

2hours of work

[acuf]

Hello,
i do each month big, very big import ..
1 csv => +- 180000 record,
300csv => total record same as csv 1

All is without human interaction.

create a new entity ... put 2 new field :
- enum => template1, template2 and so
- file => your csv file

create a hook afterSave
if entity->isNew
if type == 1 .. create new jobs type1
if type == 2 .. create new job type 2

create Job 1 for type 1
read csv
blabla

create job 2 for type 2
read csv
blabla

https://www.php.net/manual/fr/function.fgetcsv.php

https://docs.espocrm.com/development...scheduling-job

You can just duplicate job and add new value to type enum... for all new type of csv.

2hours of work

Sorry for my delay in reply, thank's item
How can I implement standard field validation in my new classes?
May I have same result re-scheduling in crontab this command:

php command.php import --file=path/to/csv --params-id={import-id}

copied from this page https://docs.espocrm.com/administrat...nsole-commands
(after a first manual import of course)

[item]

Hello,

as say above :

create a new entity ... put 3 new field :
- enum = template1, template2 and so
- file = your csv file
- enum status : New, Done

create a hook afterSave
if entity->isNew
if type == 1 .. create new jobs type1
if type == 2 .. create new job type 2


so User just upload csv, select witch type .. save.
After all is done by job.
and job set status to "Done"

you just need create one Job for each template.. and in each job, you validate row/column. for sample
job1 =< template1 : firstName = row[1]

job2 => template2 : firstName = row[2]

no need "ssh"..

how is the header of your csv file(s) ?

[acuf]

Hi item sorry for the late reply, i have been busy for the last few weeks

for a customer (on other crm, that i'm thinking if it's possible migrate to espo), actualy have 5 import template from differents source:

• import_1, header is:
  • date*: date when lead was collected, may be differents date in same import
  • time*: same of above
  • status: not required for import
  • note_1*: may be useful can import this field
  • note_2*: same of above
  • request_id: not required for import
  • name: lead first name
  • surname: lead last name
  • phone: lead phone number
  • email: lead email address
  • region: region where lead lives
  • province: province where lead lives
  • city: city where lead lives
  • product*: product of interest
  • request_text*: note field, may be useful can import this field
  • request_detail*: link to external page, may be useful can import this field
• import_2, header is:
  • date_time*: timestamp when lead was collected, may be differents datetime in same import
  • province: province where lead lives
  • province_abbreviation: province code where lead lives
  • country: country where lead lives
  • country_abbreviation: country code where lead lives
  • mobile_phone: lead mobile phone number
  • sms_answer*: answer received from lead
  • sms_text*: sms sent to lead
  • name: lead first name
  • surname: lead last name
  • creation: datetime when sms sent to lead
  • ip: ip adrress, not required for import
  • order_id: not required for import
  • tl_id_database: not required for import
  • product*: product of interest
• import_3, header is:
  • name: lead first name and last name
  • email: lead email address
  • phone: lead phone number
  • message*: message received from lead
  • date*: date when lead wrote to company, may be differents date in same import
  • product*: product of interest
• import_4, header is:
  • request_text*: message received from lead
  • email: lead email address
  • name: lead first name and last name
  • phone: lead phone number (with some character to remove, for example: "p:+39011020202" "p:" may be dangerous in phone field?!)
  • date*: date when lead wrote to company, may be differents date in same import
  • product*: product of interest
• import_5, header is:
  • name: lead first name and last name
  • city: city where lead lives
  • phone: lead phone number
  • date*: date when lead was collected, may be differents date in same import
  • product*: product of interest

fields with * may be useful to save in entity linked to lead (call, task or new one) becaus same lead can contact in different time and different way customer company.

I hope I was clear enough, thank you in advance

[item]

Hi,

first create a new entity say "Coucou" with 3 new fields
- file : the file. : the csv
- type : enum : ['importType1', 'importType2' .... ] user must select the good choose !
- status : enum : ['new', 'done']. // readOnly


so there, user can create a record and put the csv.
on hook afterSave :
https://docs.espocrm.com/development...scheduling-job

create the adequate job ! with the id of the record
$entityManager->createEntity('Job', [ 'serviceName' => 'MyImport', 'methodName' => 'myImport5', 'data' => (object) [ 'key1' => 'value1', 'key2' => 'value2', ], 'executeTime' => date('Y-m-d H:i:s'), // you can delay execution by setting a later time 'queue' => 'q0', // available queues are listed below ]);

and on any job (as i see 5 type of import .. )

and here a sample for import_5

<?php

$idOfCoucou = $data->id;

$coucou = $em->getRepository('Coucou')
->where([
'id' => $idOfCoucou
])
->findOne();

if ($coucou->get('status') !== 'New') return;

if(!file_exists($fileName) || !is_readable($fileName)) exit;

if (($handle = fopen( $fileName, "r")) !== false)
{
$em = $this->getEntityManager();

$header = fgetcsv($handle);

// import_5, header is:
// name: lead first name and last name
// city: city where lead lives
// phone: lead phone number
// date*: date when lead was collected, may be differents date in same import
// product*: product of interest

while (($data = fgetcsv($handle, 2000, ",")) !== false) {
//$data = mb_convert_encoding($data, 'ISO-8859-1','ISO-8859-1');
$name = $data[0] ; // need to split firstName $lastName !
$firstName = '' ; // to split from $name
$lastName = '' ; // to split from $name
$addressCity = $data[1];
$phoneNumber = preg_replace( '/[^0-9]/', '', $data[2] ); // only digit for phoneNumber
$date = $data[3]; // maybe format to dataBase date type
$product = $data[4];
$idOfOther = $data[5]; // is better if you can have id of other software

$lead = $em->getRepository('Lead')
->where([
'firstName' => $firstName,
'lastName' => $lastName,
])
->findOne();
if (!$lead->get('id'))
{
$lead = $em->createEntity('Lead', [
'firstName' => $firstName,
'lastName' => $lastName,
'addressCity' => $addressCity,
'phoneNumber' => $phoneNumber,
'date' => $date,
'idOfOther' => $idOfOther,
'assignedUserId' => '1'
'teamsIds' => ['idOfOneTeams'],
]);
}else{ // LEAD EXIST WITH SAME firstName, lastName .. update some data ?
$lead->set([
'date' => $date,
]);
$em->saveEntity($lead);
}
}
}

// when import is finish set status to done

$coucou->set(['status' => 'done'])
$em->saveEntity($coucou);

all is here : https://docs.espocrm.com/development...scheduling-job

https://docs.espocrm.com/development/hooks/

You must verify : encoding, date format and
verify if good header else run another job .. can be in cascade so only good job will work.

but try to have the id of other environement.. as i have say "idOfOther"... you will certainly have need in a day.

[acuf]

Thank you item I'll work on it