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.
Used as a source provider
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)
Numeric columns with empty string values are imported with a '0' value.
Used as a destination provider
When used as a destination provider, enter a destination file name and select a destination folder (Figure 3.1).
Which tables & columns?
When using an Excel spreadsheet as a source, each sheet/tab in your excel file (Figure 4.1)…
..will be imported as a table (Figure 4.2).
- The name of spreadsheet and name of destination table must be exact matches, or the spreadsheet is ignored.
- Column headers are mandatory