具有日期值的 Excel 单元格无法通过 SSIS 正确导入
我有一个由外部自动化流程生成的 Excel '97 电子表格，我想通过 SSIS 将其导入到 SQL [2008 R2] 表中。我正在导入的列在某些单元格中包含文本值，在其他单元格中包含日期值。为了连接到 Excel 数据，我有一个“Excel Source”数据流源，其中 OpenRowset 属性已设置为特定列范围：Sheet1$A1:A100。我直接在 Excel 源之后添加了一个网格数据查看器，以便我可以查看“原始”结果。
执行包时，我查看数据查看器结果，所有包含日期的单元格都显示为 NULL。奇怪的是，文本字段和数字字段表现良好并在数据查看器中正确显示。如果我在日期值前面放置一个单引号（撇号），以便将日期视为文本，则可以正确导入。如果我右键单击 Excel 中的日期字段之一并转到“设置单元格格式...”，它们都会显示为“日期”。
这些 Excel 文件是由我无法控制的过程自动生成的，并且我无法手动编辑每个文件以使它们正确导入。在 Excel 源的外部列部分中，该列显示的数据类型为“Unicode 字符串 [DT_WSTR]”，因此我认为它只会将其作为文本导入。
我非常感谢任何关于如何正确导入这些日期值的建议。先感谢您！
I have an Excel '97 spreadsheet that is generated by an external automated process that I would like to import into a SQL [2008 R2] table via SSIS. The column I am importing contains text values in some cells and date values in other cells. To connect to the Excel data, I have an "Excel Source" Data Flow Source, where the OpenRowset property has been set to a specific column range: Sheet1$A1:A100. I have added a grid Data Viewer directly after the Excel Source so that I can view the "raw" results.
When the package is executed, I review the Data Viewer results and all of the cells that contained a date come through as NULLs. Oddly, the text fields and number fields come through fine and show up correctly in the Data Viewer. If I put a single quotation mark (apostrophe) in front of the date value so that it treats the date as text, it imports properly. If I right-click on one of the date fields in Excel and go to "Format Cells...", they are all displayed as "Date".
These Excel files are being generated automatically by a process that I can't control, and I can't manually edit each file to get them to import properly. Within the External Columns portion of the Excel Source, the column is shown with a DataType of "Unicode string [DT_WSTR]", so I would think it would just import it as text.
I would GREATLY appreciate any suggestions on how I might get these date values to import properly. Thank you in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我不知道SSIS中是否有一个“好”的方法来处理这种情况，但我可以想到一些或多或少丑陋的想法。为了增加黑客性：
我认为#1是最好的长期解决方案，但听起来它不会很快发生。也就是说，将电子表格保存为文本至少会使 SSIS 可以轻松处理它，并且这可能足以克服眼前的障碍。
I don't know if there is a "good" way to handle this situation in SSIS, but I can think of a few more-or-less ugly ideas. In order of increasing hackiness:
I think #1 is the best long-term solution, but it doesn't sound like it'll happen quickly. That said, saving the spreadsheet as text will at least make it something SSIS can handle easily, and that might be enough to get over the immediate hurdle.