在Excel的Excel导入导出向导中解决到255列限制 [英] Work around to 255 column limit in SQL import export wizard for excel

问题描述

我正在尝试将Excel工作表导入SQL数据库.该工作表有700多个列，但我知道最多只能有255列.在上载到数据库时，是否可以解决所有列的问题.选择Excel版本时选择了Excel 2007.

I am trying to import an excel sheet to SQL database. This sheet has 700+ columns, but i understand there is a limit of 255 columns. Is there a work around to include all the columns while uploading to database. I selected Excel 2007 while selecting excel version.

推荐答案

遗憾的是，在我在线上看到的资源中，没有一种简单的方法可以做到这一点.我尝试了上述选择"Excel 2007"的解决方案，但这对我不起作用.人们会期望Excel和SQL Server具有更紧密的集成.

Sadly, of the resources I saw online, there wasn’t an easy way to do this. I tried the above solution of choosing "Excel 2007" but that didn’t work for me. One would expect Excel and SQL Server to have tighter integration.

但是，通过转换为.txt文件，然后解决了一些截断错误，我设法加载了数据集.下面是我的用例和过程的一些细节.

However, by converting to a .txt file and then working through some of the truncation errors, I managed to load my data set. Below is some detail of my use case and process.

我有一个相当小的行数据集(〜200行)，但是有大量的列(+500列).我首先将Excel文件转换为文本，以制表符分隔"的文件(即* .txt).在通过SQL Server导入和导出向导进行加载时，我遇到了两个截断错误:一，一些列名大于128个字符，二，默认数据类型为DT_STR的某些行中的值长度更大大于50(默认的输出列宽).对于第一个错误，我只是将列重命名为更短的名称.对于第二个错误，我手动进行了长度计数，并找到了每一列的最大长度值，这使我能够隔离出哪些列会引发错误.我按照以下步骤操作:

I had a fairly small dataset row-wise (~200 rows) but with a large set of columns (+500 columns). I first converted the Excel file to a text, 'tab-delimited' file (i.e. *.txt). In loading through the SQL Server Import and Export Wizard, I faced two truncation errors: one, a few of the column names were greater than 128 characters and two, the length of values in some of the rows where the default datatype was DT_STR was greater than 50 (the default output column width). For the first error, I just renamed the column(s) to something shorter. For the second error, I manually ran a count of lengths and found the maximum length of values for each column, which allowed me to isolate which columns would throw up an error. I followed the steps below:

1)在SQL Server导入&导出向导的常规"选项卡中，我选择了平面文件源"并接受了所有正常的默认设置.

1) In the SQL Server Import & Export Wizard 'General' Tab, I selected 'Flat File source' and accepted all the normal defaults.

2)在第二个选项卡(列")中，确保选择列定界符作为选项卡".我更喜欢CSV格式，因为我的数据集中有一些带有逗号的粗文本.

2) In the second tab ('Columns'), ensure column delimiter is selected as Tab. I preferred this over a CSV format since there was some heavy text with commas in my dataset.

3)如果映射成功，则在第三个选项卡(高级")中，您应该获得所有列的默认名称及其清单.如上所述，我隔离了哪些列的值超过默认值DT_STR(50)，并将其更改为DT_TEXT.

3) If the mapping works out, in the third tab ('Advanced'), you should get a laundry list of all your columns with their natural defaults. As detailed above, I isolated which columns had values that exceeded the default of DT_STR (50) and changed that to DT_TEXT.

4)其余步骤仅指定目的地，以及是否要保存SSIS步骤.

4) The remaining steps just specify the destination, and whether you want to save the SSIS steps.

这篇关于在Excel的Excel导入导出向导中解决到255列限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！