Sep 22, 2014

Read from and write to Excel with Database components

Our current Excel components use Excel Automation to work with Excel documents. This allows us to run macros and support almost any scenario but is not ideal for server environments. If you don't need to run any macros a better option will be to use the Database components to read from and write to Excel. Here are some tips to get you going.


  1. Download and install the Microsoft Access Database Engine Redistributable. This will install the OleDb drivers required to work with Excel through a database component.
  2. Use the DatabaseRead and DatabaseWrite components when reading or writing to an Excel document.
  3. The connectionstring for reads looks like "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\temp\write.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"
  4. For writing change IMEX=1 to IMEX=3.
  5. If you do not have headers then HDR=NO.
  6. For older files you can change the Extended Properties of the connectionstring.
  7. You can get more connectionstring information here.
  8. It seems as if a blank document must be initialised with a header for every row you want to write to. Just make it a space if you don't have headers.
  9. Read from a sheet: "select * from [Sheet1$]". Remember the $.
  10. Read from a range: "select * from MyRange" or "select * from [Sheet1$A1:B10]".
  11. Insert into a sheet with headers: "insert into [Sheet1$] (id,name) values('123','John')".
  12. If you don't have header use F1, F2, F... to refer to fields e.g. "select F1, F3 from [Sheet1$]".
  13. It supports other sql clauses like UPDATE and WHERE as illustrated here.
Some more info:

Sep 19, 2014

Linx 4.0.811.3839 released

Changelog

  • Allow upload of multiple solutions to server on modern browsers
  • Add "Statement narrative" field to NedbankCPS components
  • Change HTTPXComponent to work on untrusted https url
  • Increase the displayed number of recent solutions from 5 to 20
  • Return SQL string in exception when DatabaseRead/DatabaseWrite components fail
  • Return parameter names and values in exception when StoredProcedure components fail
  • Fix bug: SendMailSmtp to address does not allow semicolon separator or separator at end
  • Fix bug: ImageSplit output files are corrupt for certain images
  • Fix bug: Compilation of recursive process call fails
  • Fix bug: Moving process to folder breaks process event references
  • Fix bug: Upload of empty configuration file causes endless loading message on server

Upload multiple solutions to LinxServer


With newer browsers (IE10 or better) you can add or update multiple solutions to LinxServer with a single upload. Drag any number of solution and config files onto the upload area. Solutions and config files are matched according to name. If they exist on the server they are treated as an update else they are added.