Sql Server Import Wizard Doesn't Support Importing From Excel Sheet With More Than 255 Columns

I use the SQL Server 2019 Import and Export wizard to import an Excel sheet (xlsx file) as source and I set the destination as a SQL Server table. In the wizard setup I setup th

Solution 1:

Excel Data Types

First of all, I am not sure that you were right when you mentioned that:

The nice thing of Excel columns are is that it knows what the datatype when importing so the wizard don't get these weird errors when importing from a csv file for tab delimited file.

Since Excel is not a database engine and one columns may contains different data types which will cause several problems when reading data especially when using OLE DB providers (used in SSIS)

• Why SSIS always gets Excel data types wrong, and how to fix it!
• Mixed data types in Excel column
• EXCEL IN SSIS: FIXING THE WRONG DATA TYPES

255 columns limitation

In addition, the 255 columns limitation is not related Microsoft Excel but it is related to the OLE DB providers (JET or ACE) even when used with other sources (Access, Flat Files ...)

• Column limitation on CSV using Microsoft Jet OLEDB
• ACE.OLEDB and 255 Column Limit

Workarounds

1. Convert Excel file into .csv file and import it using Flat File Connection Manager which doesn't relies on OLE DB provider

2. Import Data in 2 phases with specific range then Join the result tables:

You can Import the first 255 column into a table using the following SQL Command as Source:

SELECT * FROM[Sheet1$A:IT]

Then Import remaining Columns into another table and later merge both tables into one destination table using SQL.

You can refer to the following article to get some insights:

• SSIS – IMPORTING AN EXCEL FILE WITH OVER 255 COLUMNS