Servoy Forum

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

Code: Select all

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:

Code: Select all

//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:

Code: Select all

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!

This is great stuff, Harjo!

Saves me hours of work!

Thanks,
Stef

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

faheemhameed wrote: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?

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:

Code: Select all

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

At your service!

Both wishes are implemented:

make a globalmethod: createExportFile

Code: Select all

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:

Code: Select all

//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!

Great work!! Nice and quick.

I am using this script for my export now.


Thanks a lot.

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?

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):

Code: Select all

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:

Code: Select all

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;
}