SQL Server导入向导不支持从超过255列的Excel工作表导入 [英] SQL Server Import Wizard doesn't support importing from excel sheet with more than 255 columns
问题描述
我使用SQL Server 2019导入和导出向导将Excel工作表(xlsx文件)作为源导入，并将目标设置为SQL Server表.
在向导设置中，我设置了Excel中所有列到SQL Server表的映射.导入向导之后，我没有在屏幕上显示任何错误，但是我发现并不是所有映射的列都填充在SQL Server表中.查看未导入的列，我发现它们位于excel文件列的末尾(从左到右)，尽管excel文件中有值并且已在向导中正确映射，但它们在db中都为空值.
我在向导中映射并导入的excel文件有280列
我尝试选择SQL Server 2016作为源，但是结果是相同的.
作为一种解决方法，我通过一次删除工作表中的一半列以减少列数，将excel文件分为两个单独的excel文件.然后，我必须将2次导入到两个单独的中间导入步骤表中，然后通过SQL查询将它们联接到主表中.
我想要一种支持280列excel列的导入方法.我没有尝试过SSIS，但我认为结果是相同的.
是否有任何设置或解决方法来解决此限制?
Excel数据类型
首先，当您提到以下内容时，我不确定您是否正确:
Excel列的优点在于，它知道导入时的数据类型，因此从制表符分隔文件的csv文件导入时，向导不会出现这些奇怪的错误.
由于Excel不是数据库引擎，并且一列可能包含不同的数据类型，因此在读取数据时尤其是在使用OLE DB提供程序(在SSIS中使用)时，会引起一些问题
255列限制
此外，255列的限制与Microsoft Excel不相关，但与OLE DB提供程序(JET或ACE)有关，即使与其他来源(Access，平面文件...)一起使用也是如此
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 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?
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 ...)
Workarounds
Convert Excel file into .csv file and import it using Flat File Connection Manager which doesn't relies on OLE DB provider
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:
这篇关于SQL Server导入向导不支持从超过255列的Excel工作表导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
