SSIS Excel数据导入-行中的混合数据类型 [英] SSIS Excel Data Import - Mixed data type in Rows

问题描述

我正在尝试使用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数据导入-行中的混合数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！