如何避免excel定义更改数据类型 [英] How to avoid the excel defination change the datatype

问题描述

你好，我是SSIS的新手，我试图从sql server获取数据并加载到excel但是问题是当数据被加载到excel时它会自动成为一个字符串，我希望我的数据采用正确的数据类型。 />


我尝试过：



获取数据并加载到excel但是十进制字段变为字符串成为excel，

hello ,im new to SSIS and im trying to fetch data from sql server and load into excel but problem is that when data will be loaded into excel its become a string automaticaly ,i want my data in my correct datatype.

What I have tried:

fetched data and load into excel but decimal field is become string into excel ,

推荐答案

看看这个博客（免责声明 - 与我无关）：为什么SSIS总是让Excel数据类型错误，以及如何修复它！ | CONCENTRA [ ^ ]



这里讨论的还有其他选择 - 使用正确的数据导出到Excel输入 [ ^ ]

Have a look at this blog (disclaimer - nothing to do with me): Why SSIS always gets Excel data types wrong, and how to fix it! | CONCENTRA[^]

There are further alternatives discussed here - Export to Excel with correct data type[^]

这是一个如何将数据从SQL Server数据库存储到本地Excel文件保持与数据库表中相同的数据类型。



1.创建带有标题行的Excel目标文件（命名列）。



2.插入假行（标题下方的第一行）并填写它的数据遵循以下逻辑：abc表示字符串，0表示整数，0.00表示小数等。



3.在数据流中的SSIS解决方案中创建OLE DB Source（定义数据库的连接字符串）和Excel目标（定义目标文件的路径）。



4.在数据库源和Excel目标之间添加数据转换。在数据转换编辑器中，将包含文本的列设置为Unicode字符串[DT_WSTR]，并将包含十进制数的列设置为decimal [DT_DECIMAL]。现在映射数据转换步骤和Excel目标之间的列。



最后，您必须删除Excel文件中的虚拟行。重点是 - Excel将保留与您在虚拟行中提及的数据类型相同的数据类型。

This is an example how to store data from your SQL Server database to local Excel file keeping the same data type as in the database table.

1. Create your Excel destination file with a heading row (name your columns).

2. Insert "a dummy row" (the first one below the heading) and fill it with data following this logic: "abc" for string, 0 for integer, 0.00 for decimal etc.

3. In your SSIS solution in Data Flow create OLE DB Source (define a connection string to the database) and Excel destination (define a path to the destination file).

4. Add Data Conversion between database source and Excel destination. In data conversion editor set the columns that contain text to 'Unicode string [DT_WSTR]' and set the columns that contain decimal numbers to 'decimal[DT_DECIMAL]'. Now map the columns between Data conversion step and Excel destination.

Finally, you'll have to delete dummy row in your Excel file. The point is - Excel will keep the same data type as you mention in the dummy row.

这篇关于如何避免excel定义更改数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！