Set Job Infos

[ssime]

Hi,

My CSV files have about 298 fields each. The fields are not always in the same order but they will always contain all the required fields for the templates. Some of the fields set the names of the data mapping configuration file, template and output presets to use dynamically. There are a couple of other fields which set the job ID, Batch ID, and Output Folder location.

for performance reasons, as the CSV contains tens of thousands and sometimes hundreds of thousands of records, I need a way to set these job infos without Executing Data Mapping with the “Output Records in Metadata” option.

sample csv1

dmName;jobId;batchId;templateName…

sample csv2

batcthId;templateName;…;jobId;…dmName;…

how can i set these job infos?

many thanks

Steve

[Sharne]

Hi Steve,

Why not use the All In One plugin. it is known to be faster and especially faster than output records in meta. Especially if you are not going to use meta. If you are sorting or filtering etc. then you just set the data mapping config and template tabs and leave the Output Creation to none and use the Create Job and Create Output after it. The Set Job Info plugin would obviously be before the All In One.

Regards,

S

[ssime]

Hello Sharne.

Appreciate you have taken the time to respond but unfortunately this doesn’t answer my question.

I have gone through other threads and often find that some users post responses for the sake of doing so without really addressing the question.

Obviously, I can’t use the set job infos plug-in here to extract the information as described in my original question. Any other ideas?

[Sharne]

I have gone through other threads and often find that some users post responses for the sake of doing so without really addressing the question.

Unfortunately I did misread your post, apologies but been a douche is not going to help you on any forum.

[Rod]

This is a tricky one… One solution would be to treat the incoming CSV as a database and connect to it via odbc or use the ADO Recordset Object to query it.

Since the workflow convert the incoming file to a job file with a dat format, you will need to add the “dat” extension to the list of disabled extensions in your windows registry. See: https://support.microsoft.com/en-gb/help/245407/you-receive-can-t-update–database-or-object-is-read-only-error-messag

Once this is done, you can then use the ADO Recorset Object to query the file and extract only the fields you are after.

So first add the following local variables to your process: dmName, jobId , batchId , templateName , outputFolder, numRecords

Then add a Run Script action (JavaScript) and use the following script (Edit as you see fit):

var fso = new ActiveXObject("Scripting.FileSystemObject");

var conn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
rs.CursorLocation = 3;
rs.CursorType = 3;
rs.LockType = 1;

var dbloc = Watch.ExpandString("%t").replace(/\\/, "/");
var src = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbloc + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited(;)\"";
var jobFile = Watch.ExpandString("%f");

createSchema(jobFile);

conn.Open(src);

var strQuery = "SELECT \"dmName\",\"jobId\",\"batchId\",\"templateName\",\"outputFolder\" FROM " + jobFile;
/* Check https://support.microsoft.com/en-gb/help/245407/you-receive-can-t-update--database-or-object-is-read-only-error-messag
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions (32 bit systems) add dat extension
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions (64 bit systems) add dat extension
*/
rs.Open(strQuery,conn);

Watch.SetVariable("dmName",new String(rs.Fields.Item("dmName")));
Watch.SetVariable("jobId", new String(rs.Fields.Item("jobId")));
Watch.SetVariable("batchId",new String(rs.Fields.Item("batchId")));
Watch.SetVariable("templateName",new String(rs.Fields.Item("templateName")));
Watch.SetVariable("outputFolder",new String(rs.Fields.Item("outputFolder")));
Watch.SetVariable("numRecords", rs.RecordCount);

rs.Close();
conn.Close();
deleteSchema(jobFile);

/*===============================================================================================
create csv schema in job folder
==========================================================*/
function createSchema(jobFile){
    var s = fso.CreateTextFile(dbloc + "/Schema.ini", true);
    s.writeline("[" + jobFile + "]" + "\n" + "Format=Delimited(;)" );
    s.Close();
}
/*===============================================================================================
delete csv schema from job joldder
==========================================================*/
function deleteSchema(jobFile){
        fso.DeleteFile(dbloc + "/Schema.ini");
}

[ssime]

Hello Rod, thank you very much for your help with this. The example works perfectly. I only had to edit the SELECT statement for the TOP 1.