Excel Provider

The Excel Provider is used for importing and export data from and to XLS and XLSX files, the spreadsheet file formats chiefly used by Microsoft Excel.

The Excel Provider imports each sheet/tab as a table, and also exports each table as a sheet/tab in Excel.

Using the older XLS format may require for the ApplicationPool of the solution to run in 32-bit mode. To avoid this, and get the full benefit of running 64-bit, we strongly recommend using the newer XLSX file format.

Microsoft & TypeGuessRows

When you import data from Excel, the Microsoft-driver handling the excel data makes an educated guess as to which type of data you're trying to import. It does this by looking at the top 8 rows and then choosing a field type.

This can occasionally lead to problems when the first 8 rows of a column are not representative, such as when importing descriptions and the first 8 rows are under 255 chars.

To circumvent the default behavior, Microsoft suggests you edit the registry entry for Excel and set TypeGuessRows to 0 (no limit): 

When used as a source provider simply select a source file (Figure 2.1).

Imports from Excel may fail on

  • Cells containing none-explicit values (e.g. calculated or referenced cells)
  • Merged cells (will become NULL or empty string)
  • Data out of scope (make sure to only have data in your sheet that is part of import - avoid filters, formating, grouping, empty but included columns, tables etc).

 

About columns with numeric values

  • Excel operates with datatype double only. As thumb rule your destination column should be double as well.
    • Even though your values in Excel are valid integers, and your souce column is also integer, job execution will fail trying to convert double to integer.
    • If you insist on having a destination column of type Integer, you must manually change the source datatype from Double to Integer in the XML file that defines the job, once you have saved the job.
  • Numeric columns with empty string values are imported with a '0' value.

When used as a destination provider, enter a destination file name and select a destination folder (Figure 3.1).

When using an Excel spreadsheet as a source, each sheet/tab in your excel file (Figure 4.1)…

Figure 4.1 The tabs in your Excel spreadsheet...

..will be imported as a table (Figure 4.2).

Figure 4.2 ..are imported as tables

Please note:

  • If name of spreadsheet and name of destination table have exact matches, the source/destination mapping will be set automatically. Otherwise you must map manually.
  • Column headers are mandatory