无法从Excel列读取字符串值 [英] Failing to read String value from an excel column
问题描述
SSIS脚本任务仅读取excel的数值，但无法读取同一列中存在的字母数字值
SSIS script task reading only numeric values of excel but fails to read the alphanumeric values present in the same column
我尝试使用IMEX = 0，IMEX = 1和IMEX = 2.但是在我的SQL Server表中，字母数字值被生成为NULL.
I've tried using IMEX=0, IMEX=1 and IMEX=2. But the Alphanumeric values are being generated as NULL in my SQL server table.
//Excel Connection String
string ConStr;
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR +
";IMEX=0\"";
如果第1 8列的"A"值是整数(0,1,2,3,4,5,6,7)，然后在下面的行中出现字母数字(A，B，ABQX345)值，则应该照原样读取所有值，并以
If the 1st 8 column 'A' values are integer (0,1,2,3,4,5,6,7) and then comes alphanumeric (A,B,ABQX345) values in below rows, then it should read all the values as it is and insert into SQL Server table as
0,1,2,3,4,5,6,7,A,B,ABQX345
推荐答案
此问题与用于读取excel文件的OLEDB提供程序有关:由于excel不是每个列都有特定数据类型的数据库，因此OLEDB提供程序试图确定在每一列中找到的主要数据类型，并用NULL替换所有其他无法解析的数据类型.
This issue is related to the OLEDB provider used to read excel files: Since excel is not a database where each column has a specific data type, OLEDB provider tries to identify the dominant data types found in each column and replace all other data types that cannot be parsed with NULLs.
网上有很多文章讨论此问题，并提供了几种解决方法(下面列出的链接).
There are many articles found online discussing this issue and giving several workarounds (links listed below).
但是在使用SSIS多年之后，我可以说最佳实践是将excel文件转换为csv文件并使用平面文件组件读取它们.
But after using SSIS for years, I can say that best practice is to convert excel files to csv files and read them using Flat File components.
或者，如果您没有选择将excel转换为平面文件，则可以强制excel连接管理器忽略第一行的标头，然后在连接字符串中添加HDR=NO并添加IMEX=1来告知OLEDB提供程序从第一行(通常是标题-大多数情况下为所有字符串)来指定数据类型，在这种情况下，所有列均作为字符串导入，并且没有任何值被NULL代替，但是您将丢失标头和另一行(已导入标头行).
Or, if you don't have the choice to convert excel to flat files then you can force excel connection manager to ignore headers from the first row bu adding HDR=NO to the connection string and adding IMEX=1 to tell the OLEDB provider to specify data types from the first row (which is the header - all string most of the time), in this case all columns are imported as string and no values are replaced with NULLs but you will lose the headers and a additional row (header row is imported).
如果您不能忽略标题行，只需在标题行之后添加一个包含虚拟字符串值(例如:aaa)的虚拟行，然后在连接字符串中添加IMEX=1.
If you cannot ignore the header row, just add a dummy row that contains dummy string values (example: aaa) after the header row and add IMEX=1 to the connection string.
此外，最好检查以下属于SchemaMapper项目的类，为了解决此问题，我实现了上述逻辑:
In addition, it is good to check the following class which is a part of SchemaMapper project, I implemented the logic mentioned above in order to fix this problem:
有用的链接
- SSIS Excel数据导入-行中的混合数据类型
- Excel中的混合数据类型列
- 从列中具有混合数据类型(SSIS)的Excel中导入数据
- SSIS中的EXCEL:修复错误的数据类型
- IMEX = 1扩展属性在siss中
- SSIS Excel Data Import - Mixed data type in Rows
- Mixed data types in Excel column
- Importing data from Excel having Mixed Data Types in a column (SSIS)
- Why SSIS always gets Excel data types wrong, and how to fix it!
- EXCEL IN SSIS: FIXING THE WRONG DATA TYPES
- IMEX= 1 extended properties in ssis
这篇关于无法从Excel列读取字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
