HOW TO: make your own scriptable export.

Classic Servoy
1

Hi,
in one of my solutions I needed to export data to certain files, with a specific field-order, and field-seperator.
Because the export-plugin won’t allow us to do scriptable export (yet), I made a method of my own.

make a method, called: createExportFile

var fieldnames = arguments[0] 	// boolean, export with ColumnNames at top
var seperator = arguments[1] 	// string, choose seperator, for example:  "\t" for TAB "," or ";"
var quotes = arguments[2] 		// boolean, ColumnNames with quotes
var columnnames = arguments[3]	// string, ColumnNames seperated by: ";"
var exportfile = "" 			//  create a text variable

// set the variable insertquotes to " or nothing
if (quotes == true) var insertquotes = '"'
else var insertquotes = "";

// split the columnames into an Array
var columnArray = columnnames.split(";")
// determine how many columns are used
var columnCount = columnArray.length

// if fieldnames is true than place the ColumnNames at top
if(fieldnames == true)
{	
	for(var x=0;x<= columnCount-1;x++)
	{
		if(x != columnCount-1) exportfile += columnArray[x] + seperator
		else exportfile += columnArray[x]
	}
// make an 'Enter'	
exportfile += "\n"
}

// loop thru the foundset
for(var i=1;i<= foundset.getSize();i++) 
{ 
	var record = foundset.getRecord(i); 
	for(var x=0;x<= columnCount-1;x++)
	{
		if(record[columnArray[x]]) 
		{
			if(x != columnCount-1) exportfile += insertquotes + record[columnArray[x]] + insertquotes + seperator
			else exportfile += insertquotes + record[columnArray[x]] + insertquotes
		}
		else 
		{
			if(x != columnCount-1) exportfile += insertquotes + seperator
			else exportfile += insertquotes
		}
	}
	exportfile += "\n"
}

//open dialog, with a suggested filename
var save = application.showFileSaveDialog('file_export.mer')
if(save != null)
{
	var success = application.writeTXTFile(save,exportfile);
	if (success)
	{
		plugins.dialogs.showInfoDialog('Info', 'The File is saved!','OK');
	}
	else
	{
		plugins.dialogs.showErrorDialog('Warning', 'The File is NOT saved because of an error!','OK');
	}
}

Here is an example how you can call the method:

//first argument: boolean, export with ColumnNames at top
//second argument: string, choose seperator, for example:  "\t" for TAB "," or ";"
//third argument: boolean, ColumnNames with quotes.
//fourth argument: string, ColumnNames seperated by: ";"
createExportFile(true,"\t",true,"customerid;companyname");

You even can use related fields, for example:

createExportFile(true,"\t",true,"customerid;companyname;company_to_adresses.adres");

The method is pretty FAST. In my example it exported 2600 records in about 4 seconds.

BTW: because I am using new functions in Servoy 2.1, above only works in this version!

ENJOY!

2

This is great stuff, Harjo!

Saves me hours of work!

Thanks,
Stef

3

Great stuff!!

It will be great if we can be able to pass the foundset object.
Also if we can have option to change the \n characters in the field to someother character, that will be very useful.

Thanks

4

faheemhameed:
Great stuff!!

It will be great if we can be able to pass the foundset object.
Also if we can have option to change the \n characters in the field to someother character, that will be very useful.

Thanks

1.) What do you mean by that? the method does only the current foundset now!
2.) Yes, change: exportfile += “\n” into something else. But why would you do that?

5

1.) What do you mean by that? the method does only the current foundset now!

I want to call this method from within a global method. So I would pass the foundset of table I want to export as follows:

createExportFile(forms.studentList.foundset, true,"\t",true,"customerid;companyname;company_to_adresses.adres");

2.) Yes, change: exportfile += “\n” into something else. But why would you do that?

I do not wanted to replace the row seperator (\n). I wanted to replace a \n character in a column’s value to someother character because it breaks the current record and makes a new record. So If export 10 records and we have a \n character in two of those records, then I get 12 records in the export instead of 10.

Say for example if you open up the exported file in the excel you would get wrong number of records.

Thanks

6

At your service! 8)

Both wishes are implemented:

make a globalmethod: createExportFile

var form = arguments[0]
var fieldnames = arguments[1]    // boolean, export with ColumnNames at top
var seperator = arguments[2]    // string, choose seperator, for example:  "\t" for TAB "," or ";"
var quotes = arguments[3]       // boolean, ColumnNames with quotes
var columnnames = arguments[4]   // string, ColumnNames seperated by: ";"
var exportfile = ""          //  create a text variable

// set the variable insertquotes to " or nothing
if (quotes == true) var insertquotes = '"'
else var insertquotes = "";

// split the columnames into an Array
var columnArray = columnnames.split(";")
// determine how many columns are used
var columnCount = columnArray.length

// if fieldnames is true than place the ColumnNames at top
if(fieldnames == true)
{   
   for(var x=0;x<= columnCount-1;x++)
   {
      if(x != columnCount-1) exportfile += columnArray[x] + seperator
      else exportfile += columnArray[x]
   }
// make an 'Enter'   
exportfile += "\n"
}

// loop thru the foundset
for(var i=1;i<= forms[form].foundset.getSize();i++)
{
   var record = forms[form].foundset.getRecord(i);
   for(var x=0;x<= columnCount-1;x++)
   {
      if(record[columnArray[x]])
      {
        utils.stringReplace(record[columnArray[x]],'\n', '') 
		if(x != columnCount-1) exportfile += insertquotes + record[columnArray[x]] + insertquotes + seperator
         else exportfile += insertquotes + record[columnArray[x]] + insertquotes
      }
      else
      {
         if(x != columnCount-1) exportfile += insertquotes + seperator
         else exportfile += insertquotes
      }
   }
   exportfile += "\n"
}

//open dialog, with a suggested filename
var save = application.showFileSaveDialog('file_export.mer')
if(save != null)
{
   var success = application.writeTXTFile(save,exportfile);
   if (success)
   {
      plugins.dialogs.showInfoDialog('Info', 'The File is saved!','OK');
   }
   else
   {
      plugins.dialogs.showErrorDialog('Warning', 'The File is NOT saved because of an error!','OK');
   }
}

make a global method: startExportFile:

//first argument: give the form name
//second argument: boolean, export with ColumnNames at top
//third argument: string, choose seperator, for example:  "\t" for TAB "," or ";"
//fourth argument: boolean, ColumnNames with quotes.
//fifth argument: string, ColumnNames seperated by: ";"

createExportFile('companies_list',true,"\t",true,"customerid;companyname");

ENJOY!

7

Great work!! Nice and quick.

I am using this script for my export now.

Thanks a lot.

8

Harjo,

I’ve been using this all over the place and it’s been working great. Thanks again for posting it.

I am trying to refine it a bit so that I can make it format dates correctly as it exports them. I need to identify the type of each dataprovider specified in the columnArray, bearing in mind that the dataproviders might come from relations, and not necessarily from the base table of the form. Through much fiddling with JSTable and JSColumn objects I could probably get there, but I was hoping to find a getDataproviderType() function. That would have made it very easy. But I don’t see such a function.

Any suggestions? Am I missing something?

9

I like to have more control over the format of the fields so here’s what I’m using (the code is pretty old but works fine in servoy 4.x):

function sintpro_inout_exportHtmlFile2()
{
	// Export in HTML format
	// Es.: sintpro_inout_exportHtmlFile('articoli_tabella','id;marca;modello;prezzo','ID;Brand;Model;Price','num;txt;txt;num2','export.html');
	// Format keywords: num, num2, val4, val2, data, dataora, boolean
	
	var formName = arguments[0]			// string, form name
	var columnnames = arguments[1]		// string, ColumnNames seperated by: ";"
	var fieldnames = arguments[2]		// string, FielnaNames separated by ";" 
	var formats = arguments[3]			// string, Format keywords seperated by: ";"
	var nomeFile = arguments[4]			// File name
	
	var exportfile = '<html><head><style type="text/css"> .text {font-family: Verdana; font-size: 10pt;}</style></head><body class="text"><table>'
	
	// Split the columnames into an Array 
	var columnArray = columnnames.split(';')
	// Split the fieldnames into an Array 
	var fieldnamesArray = fieldnames.split(';')
	// Split the Formats into an Array 
	var formatsArray = formats.split(';')
	// Determine how many columns are used 
	var columnCount = columnArray.length 
	
	// If fieldnames is not null than place the Field Names at top 
	if(fieldnames) 
	{
		// Apro la riga
		exportfile += '<tr>'	 
		for(var x=0;x<= columnCount-1;x++) 
		{ 
			exportfile += '<td><b>' + fieldnamesArray[x] + '</b></td>'
		} 
		// Chiudo la riga    
		exportfile += '</tr>' 
	} 
	
	// Loop thru the foundset 
	for(var i=1;i<= forms[formName].foundset.getSize();i++) 
	{
		exportfile += '<tr>'
		
		var record = forms[formName].foundset.getRecord(i); 
		for(var x=0;x<= columnCount-1;x++) 
		{  
			exportfile += '<td>' +
			globals.sintpro_inout_formattaValore(record[columnArray[x]],formatsArray[x]);
			+ '</td>'
		}     
		exportfile += '</tr>' 
	} 
	
	exportfile += '</table></body></html>' 
	
	// Open dialog, with a suggested filename 
	var save = plugins.file.showFileSaveDialog(nomeFile)
	
	if(save != null) 
	{ 
		var success = plugins.file.writeTXTFile(save,exportfile)
		if (success) 
		{ 
			plugins.dialogs.showInfoDialog('Info', 'File '+ save + ' esportato con successo.','OK'); 
		} 
		else 
		{ 
			plugins.dialogs.showErrorDialog('Attenzione!', 'Errore! Il file non è stato esportato','OK'); 
		} 
	}
}

And this is the method to format the fields:

function sintpro_inout_formattaValore()
{
	// format the output based on the supplied format keyword
	// num, num2, val4, val2, data, dataora, boolean
	
	var input = arguments[0]	// value to format
	var formato = arguments[1]	// format keyword
	// Default to no format
	var output = input
	
	switch(formato)
	{
		case 'num':
		output = utils.numberFormat(input, '0.#');
		break;
		
		case 'num2':
		output = utils.numberFormat(input, '0.00');
		break;
		
		case 'val4':
		output = utils.numberFormat(input, '¤0.0000');
		break;
		
		case 'val2':
		output = utils.numberFormat(input, '¤0.00');
		break;
		
		case 'data':
		output = utils.dateFormat(input,'dd-MM-yyyy'); 
		break;
		
		case 'dataora':
		output = utils.dateFormat(input,'dd-MM-yyyy HH:mm:ss');
		break;
		
		case 'boolean':
			if (input > 0)
			{
				output = 'Y';
			}
			else
			{
				output = 'N';
			}
		break;
	}
	
	return output;
}