Excel Data Upload

The Excel Upload Module may be used by the User to upload data from an Excel sheet to a table in the database.  The Excel Upload Tab is displayed for users authorized to use the tool. To perform an Excel Data upload, click the ‘Excel Upload’ tab.

The ‘Select Template Name’ drop-down box lists all templates for which you have access permission. You can select the template to be used for the data upload.

The corresponding ‘Description’, ‘Connection’ and ‘Output Table Name’ are displayed. You will not be able to modify these values. Modifications may be done only by the Administrator.

Click on ‘Browse’ to select any Excel file from your folders. Please note that xls and xlsx formats are supported. No other formats are supported. Selection of any other file will display error.

A drop-down box will list all sheet names. Select the name of the sheet from which you want to upload the data. Please note that the sheet must be in the same format as defined in the template.

Enter the start and end row numbers from which you need to upload the data.

If the table already contains (pre-existing) data, the following buttons will be available for selection. You can select one of these buttons depending on the requirement.

• Overwrite Data:  Click this button to delete all existing records in the table and insert new records from the Excel file to the table.
• Append Data: Click this button to preserve all existing records in the table and insert new records from the Excel file to the table.
• Overwrite Data if Key Field Exists: This button will show up only if a key has been defined in the template. Selecting this button will have the following actions:
• • If a record exists in the table for the key, the data in the record in the table will be replaced by the data from the excel record.
  • If a record does not exist in the table for the key, a new record will be inserted in to the table with the data from the excel record.
• Keep Data if Key Field Exists: This button will show up only if a key has been defined in the template. Selecting this button will have the following actions:
• • If a record exists in the table for the key, the excel record will be ignored.
  • If a record does not exist in the table for the key, a new record will be inserted in to the table with the data from the excel record.

Select ‘Upload’

The following action will happen. Please note that depending on the file size, data volume and network bandwidth, this may take considerable time.

• The Excel file will be copied from your folder to the server.
• The format of the Excel sheet will be validated against the template; All deviations in the names of the columns will be logged as errors and the process will be stopped; the user can make corrections and start the process again or submit a different Excel file.
• The data from the specified rows will be validated for.
• • Numeric (only numbers allowed).
  • Date (only Excel date format allowed).
  • Not Null (if ‘spaces’ are not allowed).
• All errors will be logged and the process will be stopped, so that the user can make corrections and start the process again.
• The records will be inserted to the table if no key has been defined in the template.
• The records will be inserted or updated or ignored (based on the selection done earlier) if a key has been defined in the template.
• At the end of the update, “Data upload successful” will be displayed. The log file will contain the summary of the update (file name, number of records, etc.).

Go Back To

<< Permission Management - Author Role | Logout >>