Import a CSV every month that update Opportunities.

SuiteCRM Forum - English Language Developer Collaboration
1

Hey everyone,

I’m actually trying to import a CSV file every 1st of the month to update my opportunities.

My opportunities are like this:

I’ve got 12 fields, 1 for every month, So the first of march I’ll import the revenue for February etc…

To start I’ve done a custom scheduled task like here (post of ewanmcrobert):

https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/11672-importing-a-csv-file-every-week

This helped too : https://docs.suitecrm.com/developer/scheduled-tasks/

So now I need to write the function. The hard thing is that I will need to make a research in my CSV for every line of the opportunities’s name to find the ID, to update the correct line of my opportunities module.

Tried to look at /SuiteCRM/modules/Import/ to copy some code, but there is so much file and code that I don’t really know where to start ahah.

This is a “basic” question, I think I’m not the only one who will have this need. I’ll update here how I’ve done it for everyone who’s interested.

2

Thanks for moving this conversation here.

I should warn you that the chat room on Gitter is really not for technical support, I am afraid. If you go there with issues in the format “please help me, I have a problem”, you’re in the wrong place. The Forums are the correct place for that.

That chat room is just meant for developers who have previously contributed to SuiteCRM on Github, and the discussions revolve around things Like PR’s, tests, documentation, and coding issues in Core product. It’s public, of course, and everybody’s welcome there, of course, but it needs to stay on topic, I am sure you understand.

About your current thread here, as a preliminary question, I wonder if you really want a Scheduled task? Maybe a custom Entrypoint would be better? I am saying this because if we need to move the CSV file into your server manually, you might as well just run the entrypoint when after you put it there. You have the additional advantage of being able to output results to screen more easily.

The sample I meant to show you is this importer here:

https://gist.github.com/pgorod/3ce7868a290df0ef4f8c9de74f12a6cd#file-import_script-php-L203

it’s too much for what you need, but if you check the beginning of that function on line 203, you can copy bits like:

  • scanning a directory for each CSV file there
  • opening the file and reading all the contents at once into memory, in a nice PHP associative array
  • examples of Bean usage creating records, relationships, security groups assignments, etc.
3

Thansk a lot @PGR as always for helping me !

About the scheduled task or custom Entrypoint, I dont really care. I was thinking the sscheduled task was my only option.

In fact My needs is to import a CSV file when im not here, for example, putting a CSV file during the week, import it during the night or in the weekend to not disturb my users.

I will learn here how to make it : https://docs.suitecrm.com/developer/entry-points/

I will come back after :slight_smile:

Have a good monday !

4

Hey @PGR.

Thanks a lot for your file, it help me a lot ! but I thing it have a kind of bug or its maybe my CSV file ?

My CSV only got 2 lines: the first wich define my column (name, date ect…) and a second test line with test value.

On line 265 it say my file got 2 lines, due to the array_shift before, the first line is deleted so It should be 1…

Cause of this bug in the foreach (juste after line 265) it loop 2 time, I except only 1 time cause there is only 1 test line.

I’ve try a lot of thing, idk what im doing wrong, Ive even tested with your original code, just changed the path, It say 2 line too.

Have a wonderful weekend !

5

It’s hard to debug from a distance, you will have to figure it out yourself…

Is your CSV file the proper format? Check it in Notepad++

Then see if it’s picking up the $campos variable correctly (BTM, “campos” means “Fields”, I should have translated that to English). Uncomment line 266 to see it on screen.

6

Hey @PGR ,

Thanks for the reply.

Notepad++ say its UTF8 WITH BOM, tryed to put it in UTF8, changed nothing…

Tryed to see the encoding of the file with PHP with this command :

echo mb_detect_encoding(file_get_contents($FichierOuvert));

Its every time say its in ASCII ahah… Even if I change the encoding with notepad++ and if I take another file it say its ASCII, so maybe the piece of code dont work.

And yes, the campos work, super piece of code I can access every fields, its work great. In fact everything work but due to this shitty bug it loop one more time.

But pretty sure your right, its an encoding bug, will continue my research to find a solution !

7

To not take too much time I’ve fixed it by adding a “line counter” during fgetcsv()

				
$NombreLigne = 0;
while (($data[] = fgetcsv($FichierOuvert, 1024, ';')) !== FALSE) { $NombreLigne = $NombreLigne +1; }

And after array_shift() I do $NombreLigne = $NombreLigne -1; to get the good number of line :slight_smile:

I’ve got now an other question, I’ve checked on internet and in the doc. Im looking for a way to know If a bean exist in the database (to not import an already existing line.)

For now I use retrieve_by_string_fields() If its null the line doesnt exist, if not It already exist… But I dont know if the good way to do it, cant really find help on internet.

Thanks fo everything !

8

My function GetOrCreateSimpleContact does exactly that. There are several ways to check the database, the one you mention is one of them, you should be ok.

1 Like
9

The code is now done. Last thing is to make it work every first of month.

Do you know where I have to code it ? Cause for now I have to reload the page with the good url (/index.php?entryPoint=ImportAutoCSV) to execute it.

10

Look for documentation on Custom Schedulers, you can make your code run from there.