We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Easiest way to read from a file in Online

Hello, 

We need to update ~6000 Document (appointments) in Online with a new templateId. 
I figured the easiest way to do this is to use CRMscript and loop through an .excel/.csv containing the new information. 

In OnSite i would have used the File-Class and readLine to loop through the content, but this it not available in Online. 
Uploading the file as an attachment and reading it like this is an OK option, but it's' a little messy to manually get each value from the rows: 

Attachment a; 
a.load(57);
String rawString = a.getRaw();
String[] stringArray = rawString.split("\n");
for(Integer i = 1; stringArray.length() > i; i++){
  //Get values from row
  String[] rowValues = stringArray[i].split(",");
  for(Integer j = 0; rowValues.length() > j; j++){
    if(j.toString() == "0"){
      print("Ny verdi: " + rowValues[j]);
    }
    else if(j.toString() == "1"){
      print("Gammel verdi: " + rowValues[j]);
    }
    else if(j.toString() == "2"){
      print("Tekst: " + rowValues[j]);
    }
  }
  print("\n");
}

The output looks like this (from my dummy-file): 


Is there an easier/recommended way of doing this in Online? 

//Eivind

RE: Easiest way to read from a file in Online

I have usually done this by either saving the CSV data as a separate CRMScript, or by creating a custom screen with a TextArea field, and then pasting the CSV data into the TextArea and then processing it when Ok is pressed.

Easiest way to parse a CSV string from my experience is reading line by line and using the parseCSV function:

Integer maxLines = 1000; // If you want limit
while (maxLines-- > 0 && csvData.length() > 0) {
  String oneline = csvData.getLine();
  String fields = oneline.parseCSV(";"); // "," or ";"
  // Do stuff
}

Sverre

 

Av: Sverre Hjelm 25. nov 2019

RE: Easiest way to read from a file in Online

Hello Sverre, 

Thank you, I will try these methods out :) 

Any way to see which way is more 'efficient'? I am working in the online-environment and running scripts on 6000 rows are bound to be problematic when it gets cut because of memory-usage.
As of now ny code is only able to update ~150 rows, so I need to figure out how to make it 'quicker'. 

You suggest pasting the raw .csv-content into either a txt-field or put it in a script, by doing this i can eliminate my first lines of code at least: 

#setLanguageLevel 3;

//Get attachment containing all the documents that needs to be updated
Attachment a; 
a.load(59);

String rawString = a.getRaw();
String[] stringArray = rawString.split("\n");

//Variables needed on the code
String docId; 
String appId;
String templateId; 
String foldername;

for(Integer i = 1; stringArray.length() > i; i++){
  //Get row
  String[] rowValues = stringArray[i].split(",");
  //Get fields from the row
  for(Integer j = 0; rowValues.length() > j; j++){
    if(j.toString() == "0"){
      print("AppointmentId: " + rowValues[j] + ", ");
      appId = rowValues[j];
    }
    else if(j.toString() == "1"){
      print("Foldername: " + rowValues[j] + ", ");
      foldername = rowValues[j];
    }
    else if(j.toString() == "3"){
      print("TemplateId: " + rowValues[j] + ", ");
      templateId = rowValues[j];
    }
  }
  //Only get documentId if appointmentId exist
  if(appId != "0"){
    SearchEngine se; 
    se.addField("document.document_id");
    se.addField("document.appointment_id");
    se.addCriteria("document.appointment_id", "Equals", appId);
    if(se.select() > 0){
      docId = se.getField(0);
      print("docId: " + docId + ", ");
    }

    //Get Document Entity
    NSDocumentAgent dAgent; 
    NSDocumentEntity dEntity = dAgent.GetDocumentEntity(docId.toInteger());
    print("name: " + dEntity.GetName() + ", ");
    
    //Only update documentTemplateId if templateId != 0
    if(templateId != "0"){
      //Get DocumentTemplate
      NSListAgent listAgent;
      NSDocumentTemplate dTemplate = 
      listAgent.GetDocumentTemplate(templateId.toInteger());
      print("TemplateId was more then 0");
      dEntity.SetDocumentTemplate(dTemplate);
    }
    else{
      print("TemplateId was 0!");
    }
    //Create Map for UdefFields
    Map udf;
    udf.insert("SuperOffice:1", foldername);

    //Set docTemplate and udef
    dEntity.SetUserDefinedFields(udf);
    dEntity = dAgent.SaveDocumentEntity(dEntity);
    print("\n");
  }
}

Any way to tell how much memory a script uses to run? If so I can use 1 row as a 'dunny row' and figure out a better way of doing this to not bump into the memory-limit in Online. 


//Eivind

Av: Eivind Johan Fasting 12. dec 2019

RE: Easiest way to read from a file in Online

Hi Eivind,

usually when working with imports in Online, I tend to create an ExtraTable that holds the data, and a schedule task that updates x amount each minute until the ExtraTable is complete - either by giving it a flag or removing it from the table.

In this way I can get a better overview over the data that I try to import and the number of rows shouldn't be an issue if done correctly.

Av: Simen Mostuen Iversen 12. dec 2019

RE: Easiest way to read from a file in Online

Hello, 

Unfortunately there is no easy way to import my excel/.csv/whatever into an ExtraTable (?). So, I will probably bump into the same problem importing it into an ExtraTable as well. 

The only way I can think of is creating a package (with the table and info) in a local SuperOffice and exporting/importing it into Online. I am unsure if there are any restrictions on how big this package can be in Online so I haven't given it a shot.. 

Its a good suggestion though, the script will eventually fail but I can skip all previously updated rows and run it as a scheduled task every 3 minutes. Unfortunately we see its neccessary to update ~80.000 documents so it will take 'forever' to run through all of them if it's only able to handle 150 in one batch. 

thanks :) 


//Eivind

Av: Eivind Johan Fasting 12. dec 2019

RE: Easiest way to read from a file in Online

Actually, it should be possible to create a package file, containing the extra table data. It would not be limited to CRMScript limitations, but rather CGI execution timeout. It should also be quite fast.

Try creating a package containing data from an extra table to see how it should be formatted. Create similar file with your own contents and try installing it. Never tried it myself, just an idea I got right now.

Sverre

Av: Sverre Hjelm 12. dec 2019

RE: Easiest way to read from a file in Online

Hello Sverre, 

Yes, this is the solution I though of earlier as well. Hopefully there aren't any other 'restrictions' around that we are not aware of, there could be some issues importing 80.000 rows even though you confirm packages are not limited in the same way as CRMscripts. 

I will update this thread with the result, maybe its a workaround others can use in similar cases (y) 


//Eivind

Av: Eivind Johan Fasting 12. dec 2019

RE: Easiest way to read from a file in Online

Hello again, 

I'm sorry to report that the workaround to create a local package and upload it in Online unfortunately does not work.. 
When importing the rows into the table (I chose to first create the table, then the fields and then the data itself) it crashes after 'some time': 
 
This means 'only' ~4000 rows are imported into the table: 
 

There are ~80.000 rows in my local table, so as of now it looks like I have to create packages with 4000 rows each and import them one by one. To do this i have to manually create the .xml-files, as the package contains everything and does not have an option to split this up for me automatically. 

//Eivind

Av: Eivind Johan Fasting 6. jan 2020

RE: Easiest way to read from a file in Online

Hi Eivind, what does the data you want to import look like?

Are we talking of a CSV file with several columns? Could you provide an example?

I'm quite certain that I can provide a functional solution if I've got the spec.

Av: Simen Mostuen Iversen 6. jan 2020

RE: Easiest way to read from a file in Online

Hello, 

We have an .excel-file containig 80.000 rows, with 5 columns who look something like this: 
 
We need to update all 80.000 documents (found through with the appointment_id) with the information in the other 4 columns. 
Since there is no way of importing this .excel directly into a table in Online I tried splitting it into smaller 'chunks' and upload the .csv's as attachments on a request.

This is the code I used to read a .csv to update the documents connected to the appoinment: 

#setLanguageLevel 3;

//Get attachment containing all the documents that needs to be updated
Attachment a; 
a.load(66);

String rawString = a.getRaw();
String[] stringArray = rawString.split("\n");

//Variables needed on the code
String docId; 
String appId;
String templateId; 
String foldername;

for(Integer i = 1; stringArray.length() > i; i++){
  //Get values from row
  String[] rowValues = stringArray[i].split(",");
  
  for(Integer j = 0; rowValues.length() > j; j++){
    if(j.toString() == "0"){
     //print("AppointmentId: " + rowValues[j] + ", ");
      appId = rowValues[j];
    }
    else if(j.toString() == "1"){
      //print("Foldername: " + rowValues[j] + ", ");
      foldername = rowValues[j];
    }
    else if(j.toString() == "3"){
      //print("TemplateId: " + rowValues[j] + ", ");
      templateId = rowValues[j];
    }
  }
  if(appId != "0"){
    SearchEngine se; 
    se.addField("document.document_id");
    se.addField("document.appointment_id");
    se.addCriteria("document.appointment_id", "Equals", appId);
    if(se.select() > 0){
      docId = se.getField(0);
      //print("docId: " + docId + ", ");
    }

    //Get Document Entity
    NSDocumentAgent dAgent; 
    NSDocumentEntity dEntity = dAgent.GetDocumentEntity(docId.toInteger());
    //print("name: " + dEntity.GetName() + ", ");
    if(templateId != "0"){
      //Get DocumentTemplate
      NSListAgent listAgent;
      NSDocumentTemplate dTemplate = listAgent.GetDocumentTemplate(templateId.toInteger());
      dEntity.SetDocumentTemplate(dTemplate);
    }
    else{
      print("TemplateId was 0!");
    }
    //Create Map for UdefFields
    Map udf;
    udf.insert("SuperOffice:1", foldername);

    //Set docTemplate and udef
    dEntity.SetUserDefinedFields(udf);
    dEntity = dAgent.SaveDocumentEntity(dEntity);
    print("\n");
  }
}

This was not able to handle many rows at a time, so it would take ages to do it this way. 

I then thought of (with Sverre giving the same suggestion) creating a database locally, import the .excel into the table through SQL management, and then export this as a package that i can then import into Online. 
This is what now turns out also have some restrictions (CGI timeout if i understand Sverre correctly). 

EDIT: 
The reason I thought importing it into a table first is so I can run a CRMscript as a scheduled task and just update ~200 each time (to prevent timeout). That if it updates 200 each 5 minutes or so it will eventually update them all without running into any restrictions

//Eivind

Av: Eivind Johan Fasting 6. jan 2020

RE: Easiest way to read from a file in Online

We have done this a couple of time using different scenarios, i belive scenario 1 would work just fine, we did exactly what you want to achive with it just a couple of month ago, but with alot more lines, so it works just fine.

The first and most important thing, dont initlize it from Service since it so often run into 60 sec limit or memory limit.
Even with the "save the last ID to an extratable" will take alot of time due to the initilization of the DocumentAgent, set it as a schedule task with the time lock and so forth.
(Might be ok if you are able to run it manually, but for a file that would take multiple hours of runtime it would cause a bit of a hazzard)

Anyway two scenarios we have built that works just fine when working with importing data to Service.

1. The console app.
Just build a simple console app that reads x number of thousand rows from your file, then sends them as a REST call to a crmscript that handles them, then returns an OK, when that OK hits the console app, it will take the next X thousand.
Works fine and have imported hundreds of thousands of lines of company data using this.
2. A more advance scenario, i dont belive you need it, but someone else might find this thread sometimes, the customer wants to send an email into service with massive amounts of data that will hit one our of the previous mentioned limits.
What we do here is the following:
a) email filter that puts them into a separate category and runs a script.
b) the script will read the base64 data from the file and send it to a azure service we built together with parameters of how many rows shall be returned before the next batch is sent as well as the callback url.
When the azure service recives the base64, it will start making rest calls similar to 1.

Works very well, but requires a bit more work.

Av: Pär Pettersson 6. jan 2020