问题描述
我正在尝试使用SSIS pkg导入excel数据，但是它正在更改数据类型并转换为带小数的其他格式.
I am trying to import excel data using SSIS pkg but it is changing data type and converting to some other format with decimal.
我从设定范围内提取数据
I pull data from a set range
示例:范围A5:AB20
我的数据A5列(M)= 1.2
My data Column A5 (M)= 1.2
SSIS导入类似120000.001237
我在SSIS中导入A5 = 1.2
I was SSIS to import A5 = 1.2
我尝试了这个，但是失败了...
I tried this but am failing...
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ABCDPrograms.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";
原始数据是从某些旧系统中导入的，我无法在其中进行更改.
Original Data is imported from some legacy system and I cannot make changes there.
直接.谢谢
推荐答案
您需要在Excel电子表格和数据库之间的数据流任务中使用派生列转换:
You need to use a Derived Column transformation in your dataflow task between the Excel spreadsheet and the database:
Excel文件，我知道您说电子表格显示1.2，但我看不到单元格中的内容，因此它看起来好像是单元格中所保存的值更大:
Excel file, I know you say your spreadsheet is showing 1.2 but I can't see what's in the cell, so it does look like the larger value is what is held in the cell:
派生的转换任务，将值(除以100000)四舍五入到小数点后一位，替换列值:
Derived transformation task, rounds the value (divided by 100000) to 1 decimal place, replacing the column value:
结果数据库条目，此列的数据类型设置为DECIMAL(8,1)或需要的许多前导数字):
Resulting database entry, this column's datatype is set to DECIMAL(8,1) or however many preceeding digits you need):
希望有帮助
这篇关于SSIS Excel数据导入-行中的混合数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！