Creating the data file
|Top Previous Next|
The data file needs to be an Excel workbook. If you do not have Excel, you can use Open Office or Libre Office to create the Excel workbook. Note that the data values need to be in the first worksheet.
The format of the worksheet is to create a header row containing the field definitions, followed by the values for each respective field in subsequent rows e.g.
The field definition is made up of 2 parts. The first part provides the field name you want to upload into, and the second part defines what type of value is contained in that field. For e.g. given the above values:
•we want to upload files into the document field. We use a definition value of filecontent to indicate to Access OLE Export that we want to upload the contents of each file in that column.
•we want to upload integer values into the referenceID field. The datanumber definition value indicates that this field is an integer-type field.
•we want to upload text values into the comment field. The datatext definition value indicates that this field is a text-type field.
You can use only 1 filecontent definition in the data file. You can use as many datanumber and datatext definitions as you require.
Updating rows in a table
For updates, you will need to provide the key field values. You do this by using the keynumber and keytext definitions. E.g.
The definitions are similar to the example above, but this time we have 2 additional columns - invoicePrefix and invoiceID. We will be using these 2 columns are our key columns when we update the values in our table. The SQL statement used by Access OLE Import would be similar to this:
UPDATE table SET document = <file contents>, referenceID = <referenceID value>, comment = <comment value> WHERE invoicePrefix = <invoicePrefix value> AND invoiceID = <invoiceID value>
You can use as many keynumber and keytext definitions as you require. Note that only integer and text values are supported.
Once you have created your data file, save it with the xlsx or xls extension. Click on the Start Upload Wizard link on the main page to start the upload wizard.