Easiest way to read from a file in Online

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

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

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.

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

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

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

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

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.

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

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.

RE: Easiest way to read from a file in Online

You could use the Rest API instead of trying to force everything thru CrmScript

It might take longer and be more of a hassle to do login but it is very straight forward.

RE: Easiest way to read from a file in Online

REST could absolutely work, but one drawback is that in order to get the WebApi working in SuperOffice Online you need to register a custom app, build and test it in SOD, and get it approved in both SOD and QA before you can use the WebAPI in Online. Right?

RE: Easiest way to read from a file in Online

Or just build a CRMScript that functions as an endpoint, Frode.
In your demo environment Eivind, you could create a script that connects to the online environment CRMScript endpoint and updates data with the given attributes. Should work.

Anyways, if you have the entire CSV file (with all 80 000 rows) uploaded in External Document of your SuperOffice solution, I think it should be possible to read the entire content into one row of an extra table. Then you can have another task that reads one and one line in that row, and updates the needed documents from there and removes it from the row when done. I've done similar jobs before when certain attachments are too large for online environments, and they worked fine.

Something like this:

#setLanguageLevel 3;

// Your attachment ID
Integer attachmentId = 66;

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

String rawString = a.getRaw();

ExtraTable et = getExtraTable("y_temp_table");
et.setValue("x_text", rawString);
et.save();

And another script that goes something like this:

SearchEngine se;
se.bypassNetServer(true);
se.addField("y_temp_table.id");
se.addField("y_temp_table.x_text");
if(se.select() > 0)
{
  String thisId = se.getField(0);
  String text = se.getField(1);

  if(!text.isEmpty())
  {
    // removes the line from text
    String line = text.getLine();

    // The rest of your code
    String[] rowValues = line.split(",");
    for(Integer i = 0; rowValues.length() > i; i++)
    {
      if(i == 0)
        print("Ny verdi: " + rowValues[i]);
      else if(i == 1)
        print("Gammel verdi: " + rowValues[i]);
      else if(i == 2)
        print("Tekst: " + rowValues[i]);
    }
  }
  
  // Updates the text
  ExtraTable et = getExtraTable("y_temp_table");
  et.load(thisId.toInteger());
  et.setValue("x_text", text);
  et.save();
}

It's not beautiful, but often works fine. Of course doesn't work if uploading the entire document times out.

RE: Easiest way to read from a file in Online

Hey guys, 

Thank you for all suggestions and inputs on this! 
The easiest way looks to be to have a script in my local SuperOffice (with a table containing all the rows) that call a custom REST endpoint (crmscript) in Online.

I'm not sure how happy SuperOffice is with me doing 80.000 POST to a spesific script but it sounds like the best scenario in my case to be able to see which appointment has been updated etc. 

//Eivind

RE: Easiest way to read from a file in Online

No need to do 80k calls, just group together like 5k rows in each post and you´ll be fine :)

Regards

RE: Easiest way to read from a file in Online

Hm, good point. 

Based on your experience, should i use .setOption("parameters", json) instead of .setValue("something", json)?
It looks like using setOption was created 'for my scenario', and simply create a json-struct that i send as 'parameters'. 

Not sure how to fetch 'parameters' in Online, when using setValue i get it with getCgiVariable.. 
Edit: Without testing I guess getCgiContent() is the correct way to get the body/json from the POST. 

Thank you for your input/feedback so far :) 

//Eivind

RE: Easiest way to read from a file in Online

Hi

Not sure why Simens suggestion is not possible (and easiest) to use, maybe I have misunderstood the initial problem.

"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."

Create a Screen where you can upload an attachment (csv), then add attachment_id to an extratable. Create a scheduled script that loads the attachment, and go though each line and update appointment. Maybe use SearchEngine to update instead of NS-class if possible, to be able to handle more lines every time the scheduled script is run. Every time the script is executed, you can update the extratable with info on line handled, so you can pick up where it left next time, and when its done mark it complete.

RE: Easiest way to read from a file in Online

Hello, 

I ended up importing the excel-list into a local database and post x number of rows to a script in Online, and set it up as a scheduled task. 

I set Simens suggestion as the solution but many inputs could be a viable solution. 

//Eivind

RE: Easiest way to read from a file in Online

Wohooo! I won :)