Is there a way to export mapped fields into a new data file such as csv or xls?
We’d like to create a separate file using a few specific fields from our data file. It doesn’t matter if its in the datamapper or the workflow as long as it can be done.
Thanks.
The easiest way would be to write a post-processing script in the datamapper (you create it in the closing step of the DataMapper). At that stage, you have access to the entire record set that was just extracted.
A very basic example would look something like this (this example exports all fields from each master record, but not the detail tables):
var file = openTextWriter('C:\Test\MyExport.csv');
var j = 0;
for(var i=0;i<data.records.length;i++) {
for(j=0;j<data.records[i].fields.length;j++) {
file.write('"'+ data.records[i].fields[j].toString() + '",');
}
file.write('\n');
}
file.close();
Mind you, there is absolutely no error-trapping in this example and it may not be exactly what you need, but it should get you started in the right direction.
How do you do this via workflow? In my case, the data mapper source is multiple pdf files (for example, individual invoices). I need to extract mapped fields, for example, addressee and postcode to a text file for external processing.
Well the Data Mapper is adding records to the CSV file as it processes them. So all you have to do is have a Workflow process pick up that file once all your PDF’s have gone through the data mapping task.
Cool Script, works very well. Is there any way to get all fieldnames on the first line?
This would do it:
var file = openTextWriter('C:\\Test\\MyExport.csv');
//Add titles
var colNames = Object.keys(data.records[0].fields);
file.write('"'+colNames.join('","')+'"');
file.write('\n');
// Add values
for(var i=0;i<data.records.length;i++) {
for(var j=0;j<data.records[i].fields.length;j++) {
file.write('"'+ data.records[i].fields[j].toString() + '",');
}
file.write('\n');
}
file.close();
Thank you very much I’m going to try it out!
Works perfect, thank you very much for the add on!
Is there a way not to export the ‘extradata’ column?
The last script I posted should export all fields, including the Extradata field.
yes I know, and it works perfectly. But is there a way to export all field except the extradata field?
Oh sorry! I misread the question!
var file = openTextWriter('C:\\Test\\MyExport2.csv');
//Add titles
var colNames = Object.keys(data.records[0].fields).filter(function(a){ return a !== 'ExtraData';});
file.write('"'+colNames.join('","')+'"');
file.write('\n');
// Add values
for(var i=0;i<data.records.length;i++) {
for(var j=0;j<colNames.length;j++) {
file.write('"'+ data.records[i].fields[j].toString() + '"');
if(j<colNames.length-1) file.write(',');
}
file.write('\n');
}
file.close();
This code filters out the ExtraData field (and I also removed the extra comma at the end of each line, something that I had missed in my previous script)
This is lovely I’m going to try it
Great works perfectly. Thanks a million times
One More Question… The exported file has an empty line as last line. Is there a way to only include recordlines, without an empty blank line at the end?
Just put a condition on the last file.write('\n'); that adds a new line character to all lines except the last one.
For instance:
if(i<data.records.length-1) file.write('\n');
At this stage, I would suggest you look into online JavaScript courses (there are plenty of free tutorials around the web) that would help you fulfill these kinds of basic requirements by yourself.
Sounds Logic, thanks for the add on!
Hi There, one more question, is there a way to easely enhance this script so that the output is in alphabetic order conform a record field, lets say “name”.
You must understand that these forums are not meant to replace Professional Services. We simply cannot provide custom scripts to all end-users who request them as we would quickly become swamped and would therefore not be able to work as much on improving our products.
Now to answer your question, yes it’s possible to sort the output by first storing the values in an array and then sorting that array before writing it to the CSV file. If you are familiar with JavaScript, you should be able to implement it yourself, using the previous script as a reference. Otherwise, I would advise you to contact our Professional Services team.
Yes thank you, I wasn’t asking for some one to work it out for me, I just needed a pointer. Thank you very much!