Import from Excel (.XLS)

By Kateryna Sytnyk

November 03, 2009

Import from XLS

For a start you need to create an html form which takes XLS file.

  1. Put Detail (No Query) Action to the OWS configuration and put into him html form:

<input class="frmfile" name="frmImportFile">

<input type="button"">{ACTION,Save,Import,,Message,False}" id="frmSubmit" value="Import">

Button should call Message Action. You can add validation functionality id you need.



2. Put Message Action to the configuration with Event Type and Event Value which you using in the onclick event of frmSubmit button. Next steps will be into Message Action.


3. At this step we need to transfer file to the server. For this you need to put File Action.


4. At this stage we have a data source in the server. We need to connect to him and read the data. For this put Query Action with next settings:

  • Connection: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[path,A][frmImportFile.Name,Form];Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
    Attention: You must write full physical path to the XLS file on hard drive of server in Data Source. For example “C:\inetpub\wwwroot\Site\Portals\0\ImportContent\Excel.xls”. Other way - use [COALESCE,portalpath,System,{MAPPATH}] token.
    If it doesn’t work make sure that correctly installed Excel ODBC driver. 
  • Query: SELECT * FROM [Sheet1$];
    This expression show all data from Sheet1 of Excel. You can change it if you need.


5. Final step is insert data to the database. Put Query under previous “Import” Query. Write into him INSERT expression what you need. Values takes using expression [ColumnName,Excel_Import] where ColumnName is header name of excel sheet and Excel_Import is name of Import Query.


6. This is it. For additional usability you can create some verification for have possibility to show errors. For this you may use expressions “'[INSERT.isSuccessful,Action]'='False'” and “[INSERT.Error,Action]”.

Average ( Ratings):
Want to help out?

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA •

Bookmark & Share Bookmark and Share