How to Fix SQL Server Import and Export Wizard Excel Errors
Question details
The user is encountering assembly errors and external table format issues when trying to import Excel workbooks using the SQL Server Import and Export Wizard.
- Product
- SQL Server / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing an Excel workbook into a database using the SQL Server Import and Export Wizard.
- Observed behavior
- The wizard fails to load Microsoft.DataTransformationServices.ScaleHelper and displays an error stating the external table is not in the expected format.
Verify which edition of SQL Server you are currently running (e.g., Express, Developer) and check whether you are using the 32-bit or 64-bit version of the Import and Export Wizard.
Install a SQL Server Edition with Integration Services
SQL Server Express does not include Integration Services (SSIS), which is required for the Import and Export Wizard to function fully.
The SQL Server Express edition is a lightweight database version that natively lacks the SSIS components necessary for complex data transformations. Upgrading to a supported edition is necessary.
Go to the official Microsoft SQL Server downloads page and download a supported edition, such as the free SQL Server Developer Edition.
Launch the downloaded installer and proceed to the feature selection screen.
Ensure that the 'Integration Services' checkbox is selected during the installation process to install the required components.
Match the Microsoft Access Database Engine Architecture
The 'external table is not in the expected format' error often occurs due to missing or mismatched Excel OLE DB drivers.
Try WPS Office for Seamless Excel File Management
While troubleshooting SQL Server import errors, you may need a reliable spreadsheet tool to format and clean your data before importing. WPS Office is a highly compatible, free alternative to Microsoft Office that handles complex spreadsheets effortlessly.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer.
- 2. Install the Software: Run the installer and follow the on-screen instructions to complete the setup.
- 3. Prepare Your Data: Open your Excel workbook in WPS Spreadsheet to verify, clean, and re-save your data perfectly formatted for SQL Server imports.
Frequently Asked Questions
Why does the Import and Export Wizard say 'external table is not in the expected format'?
This error typically means the wizard cannot read the Excel file structure. This usually happens because the required Microsoft Access Database Engine OLE DB provider is missing, or its bit-version (32-bit vs 64-bit) doesn't match the version of the wizard you launched.
Does SQL Server Express include Integration Services (SSIS)?
No, SQL Server Express is a free, entry-level database and does not include SQL Server Integration Services. You must upgrade to the Developer, Standard, or Enterprise edition to use full SSIS features.
How can I fix the Microsoft.DataTransformationServices.ScaleHelper error?
This assembly error usually indicates a corrupt or incomplete installation of the Import and Export components, or a missing SSIS feature. Adding SQL Server Integration Services to your installation or reinstalling the wizard typically resolves the issue.
