How to import table and its content from an Excel spreadsheet into a database

 Browse Space
Added by Kendy Yus, last edited by Kendy Yus on Nov 26, 2008  (view change)

Labels:

Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.

For the full reference of the Spreadsheet Util DSC related to this article, please click here.

Background

You want to import a table and its content from an Excel spreadsheet into a database, allowing the mapping of each individual spreadsheet table column into a database table column. Currently Avoka has a DSC component called CSV Import with operation to import a CSV into a database. One simple way would be to open up the Excel spreadsheet file and save it as a CSV file, then use the CSV Import to import the CSV into the database table.

Alternatively,  use another DSC called Spreadsheet Util DSC with operation Convert Spreadsheet to CSV to extract the table and its content from a spreadsheet into a CSV document. Then feed in the CSV document into the CSV Import for the database importation.

This article will outline both approaches to the scenario.

Importing a CSV File From Save As of an Excel Spreadsheet into Database

  1. Open up the Excel Spreadsheet using spreadsheet editor application
  2. Save-as the spreadsheet as a CSV by selecting the "Save as type" to CSV (Comma Delimited) (Note: make sure that you are saving the right sheet of your spreadsheet file if it contains multiple sheet.)
  3. Use the CSV Import DSC - Import a CSV file into SQL operation to import the CSV and map its content into a database table (You need to install and activate CSV Import DSC using the "Components" view in your Workbench, if you haven't already done so.)
    1. Dragging the "Import a Csv File into SQL" operation of "CsvImport" service from the Service View into your process workflow design
    2. Specify the input properties of the step in the process in the Properties view, and click on "..." button on the Input Settings section (The Output Settings (optional) allow you to store the result of the CSV importation to different process variables to give indication whether there are any records in the CSV that has failed to be imported.)
    3. Input Settings Dialog - CSV Input Settings: point the CSV file as the Input Document as well as Design Time Template (Design Time template is used to define table column mappings later for one-off design purpose); specify the delimiter as ',' (coma) and whether there is a header; Select "Dynamic Insert/Update" if you want to perform update instead of insert if duplicate records are found
    4. Input Settings Dialog - Datasource: Specify the JNDI datasource for the database connection
    5. Input Settings Dialog - Csv to Sql Mappings: Specify column mappings from CSV to database (Note: you need to click on "Load Table" button to load the design time template csv provided earlier to determine what are the columns that are available.); specify the key column if dynamic insert/update was selected earlier
    6. Click on "OK" when done.
       
  4. You can now execute the process to import the CSV into the database when activated.

Importing an Excel Spreadsheet into Database

  1. Read the spreadsheet file into a Document variable in the process using File Utilities - Read Document operation from "Foundation" category
  2. Use the Spreadsheet Util DSC - Convert Spreadsheet to CSV operation (You need to install and activate Spreadsheet Util DSC using the "Components" view in your Workbench, if you haven't already done so.)
    1. Dragging the "Convert Spreadsheet to CSV" operation of "Spreadsheet Utilities" service from the Service View into your process linking from the previous step
    2. Specify the process variable that holds the spreadsheet document, the name of the sheet that contains the table, the location (0-based) of the header and table content, and then the process variable that will hold the result CSV document in the Properties view
  3. Use File Utilities - Write Document operation from Foundation category to write the CSV document into file system for later reference by the CSV Import
  4. Repeat Step 3 onwards of the previous section on Import a CSV file into a database
  5. The process is now ready to be executed once activated
  6. Here is how the process might look like:
     

If you want to have a go yourself with importing an Excel spreadsheet into a database in a workflow, the CSV Import DSC is currently available for download from our website http://www.avoka.com/avoka/escomponents.shtml, and Spreadsheet Util DSC will also be made available shortly.

We would appreciate any feedback or suggestions if you find there are other operations you would like to have for your workflow with regards to manipulating Excel spreadsheet in a workflow.