Scheduled lead import script

[jeffharris]

Hi,

I have a CSV file containing Leads that needs to be imported once per day. I have created a Saved Mapping entry in the Lead Import section, and manually importing my leads works. I would like to automate this process. Does anyone know the best way to submit a CSV file to SugarCRM from a script, while specifying the Saved Mapping type to use?

I have been thinking of writing a Perl script which will simulate a user submitting each part of the Import process, but this does not seem to be the most elegant solution.

Thanks,

Jeff

[kuske]

A very interesting way to import the data, really.

But I would use the mysqlimport tool, which allows a commandline import of txt files into a mysql table.
From there you could transfer the imported lead data to the leads table.

[jeffharris]

Harold,

Thanks for your reply.

mysqlimport is useful for basic imports, but not really suitable as it does not take advantage of the logic built-in to Sugar. For example, to create a lead and store their email address, you need to create an entry in the following tables: leads, email_addresses and email_addr_bean_rel (and possibly more, I still have a lot to learn about Sugar!). This is very difficult to do without writing a script to manage the process as one will need to ensure the entries in each table are linked appropriately.

So I think the best way to achieve this would be via the "official" import route, so full advantage can be taken of saved mappings etc. That way, Sugar itself is responsible for linking email addresses to leads, and leads to accounts. Does anyone know how to automate the process of submitting a file to Sugar's import routines? It does not necessarily have to be web-based - a command that must be run locally on the sugar server would also be great.

Any Sugar-internals experts reading? :-D

Thanks for reading,

Jeff

[clint]

I like the idea of scheduled import jobs, but nothing has been built yet around this. PHP can be run from the command line, so it is certainly possible to tweak the import module to read from a local file and use a default set of import options. Anybody in the community interested in taking this on as a project?

[jeffharris]

Thanks for the response Clint.

I've had a look at the relevant code portions, and believe I can put something together. Could you give me some pointers on the best way to access Sugar modules from the command line? I've looked in the docs but can't find any relevant information.

Specifically, I need to know the safest way to run a file as a particular user without specifying the password. Looking ahead, it would be nice to eventually import this into the existing scheduler logic, so batch imports can be easily set up through the UI (I only have the community version of Sugar, and therefore no scheduling/workflow, so I will be running it from cron myself).

My first thoughts would be something like this:

php -f index.php module=Import action=ScheduledImport import_module=Calls import_user=jharris

but I'm not sure if there is a secure method of running the script as a particular user.

Thanks,

Jeff

[clint]

This looks like the right approach. I think the best way to schedule this would be to create a new scheduled job. I'll poke around on that part and get back to you on the best approach.