# Excel import/export

**URL:** <https://forum.servoy.com/t/excel-import-export/6363>\
**Category:** Classic Servoy\
**Created:** [August 21, 2006, 2:55pm UTC](https://forum.servoy.com/t/excel-import-export/6363 "2006-08-21T14:55:25Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![nlsmith](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nlsmith](https://forum.servoy.com/u/nlsmith)\
**Post date:** [August 21, 2006, 2:55pm UTC](https://forum.servoy.com/t/excel-import-export/6363/1 "2006-08-21T14:55:25Z")

</div>

Hello Servoy pro’s

I am new to the game and I want to initiate the excel import/export functions from a form button instead of making the client sift through menus for it.

I know that I can enable/disable with a method… but can I start the actual process?

Sorry if this concept has already been covered.

-Nick-

---

<div class="post-metadata">

**Author:** ![grahamg](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@grahamg](https://forum.servoy.com/u/grahamg)\
**Post date:** [August 21, 2006, 4:24pm UTC](https://forum.servoy.com/t/excel-import-export/6363/2 "2006-08-21T16:24:16Z")

</div>

Hi Nick

A good place to start is the excellent tutorial by Harjo at Servoy Magazine

> [http://www.servoymagazine.com/home/2004/11/tip\_a\_scriptabl.html](http://www.servoymagazine.com/home/2004/11/tip_a_scriptabl.html)

This should get you going with scripted exporting and you’ll pick up some good Servoy concepts as well.

Regards

Graham Greensall  
Worxinfo Ltd

---

<div class="post-metadata">

**Author:** ![nlsmith](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nlsmith](https://forum.servoy.com/u/nlsmith)\
**Post date:** [August 21, 2006, 6:55pm UTC](https://forum.servoy.com/t/excel-import-export/6363/3 "2006-08-21T18:55:09Z")

</div>

Thanks for the pointer.  
It seems to work well since it exports without error.

The instructed code you reffered to saves the output file as .mer file type.

“.mer  
File type:  
Format for interchanging spreadsheet/database data  
Used in  
Filemaker, Excel, WordPerfect for Windows Library”

But It wont open with excel. What do I actualy need to do to view the output?  
thanks

---

<div class="post-metadata">

**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\
**Post date:** [August 21, 2006, 8:01pm UTC](https://forum.servoy.com/t/excel-import-export/6363/4 "2006-08-21T20:01:09Z")

</div>

save it as \*.xls

that also works! ![:-)]( "Smile")

---

<div class="post-metadata">

**Author:** ![nlsmith](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nlsmith](https://forum.servoy.com/u/nlsmith)\
**Post date:** [August 21, 2006, 8:46pm UTC](https://forum.servoy.com/t/excel-import-export/6363/5 "2006-08-21T20:46:59Z")

</div>

That was my initail thought, so I have already tried changing the method to assign extensions .xls and .txt. With the .xls extension excel launches but nothing opens (not even a blank worksheet). With the .txt extension I see one row of box’s and comma’s which I assume to mean that the file is not actualy saving in .txt format. I also assume the same for .xls.

I am sure you have made it work before so there must be something I am overlooking?

thanks  
-Nick-

---

<div class="post-metadata">

**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\
**Post date:** [August 21, 2006, 9:16pm UTC](https://forum.servoy.com/t/excel-import-export/6363/6 "2006-08-21T21:16:49Z")

</div>

It looks like your file is empty, or you are exporting just one row???

which method are you using?

---

<div class="post-metadata">

**Author:** ![nlsmith](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nlsmith](https://forum.servoy.com/u/nlsmith)\
**Post date:** [August 21, 2006, 9:42pm UTC](https://forum.servoy.com/t/excel-import-export/6363/7 "2006-08-21T21:42:44Z")

</div>

I thought that might be it so I did 2 exports from different size foundsets. They produced different size xls files which leads me to believe that the export files are not empty.  
the code I am using is this

Global Method “createExportFile”

```auto
var form = arguments[0] //string, formname
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.xls')
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');
   }
}

```

Global Method “startExportFile”

```auto
//first argument: give the form name
//show select,returns nothing when canceled 
var form = plugins.dialogs.showSelectDialog('Select','please select a form for export','DataSheets','Software','Equipment');

//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(form,true,"\t",true,";");

```

It doesn’t look like there is any platform specific code in the “createExportMethod” So maybe I am sending blanks but why would the export files be different sizes?  
What do you think?

Thanks  
-Nick-

---

<div class="post-metadata">

**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\
**Post date:** [August 21, 2006, 9:45pm UTC](https://forum.servoy.com/t/excel-import-export/6363/8 "2006-08-21T21:45:22Z")

</div>

just open it with a text-editor (notepad or whatever) and see!

they are just text-files

---

<div class="post-metadata">

**Author:** ![nlsmith](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nlsmith](https://forum.servoy.com/u/nlsmith)\
**Post date:** [August 21, 2006, 10:00pm UTC](https://forum.servoy.com/t/excel-import-export/6363/9 "2006-08-21T22:00:02Z")

</div>

the smaller founset export results in  
box" “box” “box” “box” “box”

the larger foundset export returns  
box" “box” “box” “box” “box” “box” “box” “box” “box” “box”

both opened in Notepad  
It looks like the box characters correlates with the number of records in the respective exports ie the first export was a 5 record foundset

-Nick-

---

<div class="post-metadata">

**Author:** ![grahamg](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@grahamg](https://forum.servoy.com/u/grahamg)\
**Post date:** [August 21, 2006, 10:07pm UTC](https://forum.servoy.com/t/excel-import-export/6363/10 "2006-08-21T22:07:38Z")

</div>

Nick

Assuming that you can see the data in a text file open Excel then choose File/Open (all text files or all readable documents depending on your OS) - you should find that Excel will recognise the data and show the popup window so that you can select the data seperator.

You could also try saving the file as a .CSV or .TXT and then try opening in Excel.

Graham Greensall  
Worxinfo Ltd

---

<div class="post-metadata">

**Author:** ![nlsmith](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nlsmith](https://forum.servoy.com/u/nlsmith)\
**Post date:** [August 21, 2006, 10:55pm UTC](https://forum.servoy.com/t/excel-import-export/6363/11 "2006-08-21T22:55:00Z")

</div>

As I mentioned above I have tried saving as a .txt file. I have also tried opening the export in notepad and textpad. My last post shows what I see when I open the exports as a text file… “box”  
I used the word box to indicate the box character. The quotation marks were also displayed.

So unfortunately we can not assume the data is there or shows up as plain text.

thanks anyway  
-Nick-

---

<div class="post-metadata">

**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\
**Post date:** [August 23, 2006, 8:45am UTC](https://forum.servoy.com/t/excel-import-export/6363/12 "2006-08-23T08:45:59Z")

</div>

The plugin that will do the logic (and in the near future even more) for you is the data plugin. More info can be found here: [Servoy Components – Plugins, Beans, Web Components, Angular Services & Servoy Developer Consulting](http://forum.it2be.com/viewforum.php?f=6).

Hope this adds a little to the discussion.

---

<div class="post-metadata">

**Author:** ![nick](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@nick](https://forum.servoy.com/u/nick)\
**Post date:** [March 29, 2007, 5:28pm UTC](https://forum.servoy.com/t/excel-import-export/6363/13 "2007-03-29T17:28:14Z")

</div>

Hi,

I’ve looked at the export script above, but what I really want to do is bring up the nice export dialog that is available from the file menu, so that the user can select and order the fields to export. It would be great to call this dialog from a button so that I can do other stuff before going to the export. I do exactly this type of thing for the sort dialog, which has the same sort of options as the export dialog and so is very useful.

Is there an equivalent call for the export dialog as there is for the sort dialogue ( ie. controller.sortDialog() ) ?

Thanks,

Nick

---

<div class="post-metadata">

**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\
**Post date:** [March 29, 2007, 8:42pm UTC](https://forum.servoy.com/t/excel-import-export/6363/14 "2007-03-29T20:42:34Z")

</div>

but you can do that, the default import/export is a plugin so you can call that one.

When you want more you shall have to wait until the summer. By that time we will have added those dialogs to the data plugin.

---

<div class="post-metadata">

**Author:** ![nick](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@nick](https://forum.servoy.com/u/nick)\
**Post date:** [March 30, 2007, 9:28am UTC](https://forum.servoy.com/t/excel-import-export/6363/15 "2007-03-30T09:28:51Z")

</div>

Sorry, I’m sure I’m being rather slow here, but how do I call the default plugin? In the plugins dir I can see textxport and excelxport but these just seem to have the functions exportEnabled and importEnabled. How do I actually call the export dialogue?

Nick

---

<div class="post-metadata">

**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\
**Post date:** [March 30, 2007, 9:46am UTC](https://forum.servoy.com/t/excel-import-export/6363/16 "2007-03-30T09:46:30Z")

</div>

Yes indeed, you can’t call the excel plugin (of Servoy) by method.  
You only can enable/disable it by method.