5

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.

enter image description here

enter image description here

In the wizard setup I setup the mapping for all the columns in Excel to the SQL Server table. After the import wizard I get no errors displayed on screen but I find not all the columns I have mapped are populated in the SQL Server table. Looking at the columns that are not imported I see they are at the end of the excel file columns (left to right) and all have null values in the db despite having values in the excel file and correctly being mapped in the wizard.

The excel file has 280 columns I map in the wizard and import

I have tried selecting SQL Server 2016 as source but the result is the same.

As a workaround I split the excel file into two separate excel files by deleting half the columns from the sheet at a time to reduce the number of columns. I then have to do 2 imports into two separate intermediary import step tables and join them afterwards via SQL queries into the master table.

I would like a method to do one import that supports 280 columns excel columns. I have not tried SSIS but I assume the result would be the same.

Is there any setting or workaround to get around this limitation?

Hadi
  • 36,233
  • 13
  • 65
  • 124
choba78
  • 361
  • 3
  • 5
  • 13
  • 4
    There's a limit of 255 columns when using the wizard. You can convert the Excel file to a .txt and then import it as a flat file. Take a look at thomassantosh's answer to this question: https://stackoverflow.com/questions/35068803/work-around-to-255-column-limit-in-sql-import-export-wizard-for-excel – digital.aaron Jul 08 '19 at 15:14
  • Yeah that is the problem. 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. Converting to txt file is an additional step with its own process and I would rather keep it one step. – choba78 Jul 08 '19 at 16:18
  • 1
    Best practice is to convert Excel into csv as digital.aaron mentioned – Hadi Jul 08 '19 at 21:19
  • @choba78 . You will eventually hate that Excel guesses data types. – KeithL Jul 09 '19 at 13:42

1 Answers1

5

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)

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 ...)

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124