通过SSIS动态创建Excel表 [英] Dynamically Creating Excel table through SSIS
问题描述
在SSIS中使用执行SQL任务-创建的Excel文件-包含多个列和不同的数据类型.这里的问题是即使Excel表是使用Int/Money数据类型创建的，INT/Money列仍在Excel中显示为文本列
Using Execute SQL Task in SSIS - created Excel file- contains multiple columns and different data types.The issue here is INT/Money columns are displaying as a text columns in Excel even though excel table is created with Int/Money datatype
我尝试使用双精度CY数据类型，但没有解决问题.
I have tried to used double precision,CY datatypes but nothing worked out.
CREATE TABLE `Employer` (
`MEMBERSHIP NUMBER` VARCHAR(30),
`RETIREE #` VARCHAR(12),
`COPID` CHAR(6),
`PERSON LAST NAME` VARCHAR(150),
`FIRST NAME` VARCHAR(150),
`RETIREE PLAN` CHAR(15),
`PLAN NAME` CHAR(200),
`BILL GROUP` INT,
`BRANCH ID` CHAR(3),
`CONTRACT NUMBER` CHAR(5),
`PBP` CHAR(3),
`BRANCH NAME` VARCHAR(150),
`COVERAGE MONTH` DATE,
`DUE AMOUNT` INT
)
预期输出应为输入数据类型格式.
Expected output should be in input datatype format.
推荐答案
OLE DB数据类型和Excel
即使您可以使用OLE DB命令在Excel中创建表，Excel文件也不是数据库，其中的列具有数据类型. OLE DB提供程序仅读取表格数据，并且需要为每一列指定一种数据类型.因此，即使Excel具有在同一列中存储多种数据类型的能力，您也必须在处理Excel时指定列数据类型.
OLE DB data types and Excel
Even if you can create Tables in Excel using OLE DB commands, excel files are not databases, where columns have data types. The OLE DB provider read only tabular data and need to specify a data type for each column. For this reason, you must specify columns data types when handling Excel, even if excel has the ability of storing multiple data types within the same column.
使用OLE DB提供程序读取Excel的缺点之一是，如果列包含混合数据类型，则它仅读取主要数据类型并将所有剩余数据类型转换为NULL.
One of the disadvantages of using OLE DB provider to read Excel is that if a column contains mixed data types, it only reads the dominant data types and convert all remaining data types to NULL.
- Importing data from Excel having Mixed Data Types in a column (SSIS)
- Mixed data types in Excel column
另一方面，excel中有一个Cell属性，称为Number Format，用于更改值在Excel中的显示方式.您可以使用此属性将值显示为货币".
On the other hand there are a Cell property in excel called Number Format which is used to change the way values are shown in Excel. You can use this property to show values as Currency.
- Format numbers as currency
- HOW TO DISPLAY NUMBERS IN EXCEL 2010 AS CURRENCY
您可以使用两种方法来解决此问题:
You can use two approaches to solve the issue:
您可以添加脚本任务，并使用Interop.Excel.dll程序集更改整个列的数字格式.
You can add a Script Task and change the entire column Number Format using Interop.Excel.dll assembly.
- Format an Excel column (or cell) as Text in C#?
- Format Entire Excel Column Using C#
您可以创建一个Excel文件，并根据需要更改数字格式"列，并将此文件用作模板.您可以将文件复制到目标目录，然后执行数据导入阶段.
You can create an Excel file and change the columns Number Format as needed and use this file as template. You can copy the file the the destination directory, then perform data import phase.
这篇关于通过SSIS动态创建Excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
