如何将一张包含动态元数据的多个Excel文件导入一个SQL表 [英] How do I import one sheet of multiple excel files with dynamic meta data into one SQL table
问题描述
我有一个紧急问题。我找不到这个特定场景的任何答案：
将多个Excel文件，多个工作表和不同的元数据导入一个SQL表。必须将每个Excel文件的第一张表（总共6000个文件）导入到SQL表中。这些文件的所有第一张表只有一张表。
我们发现我们必须使用C＃（SSIS中的脚本任务）。 />
文件之间的元数据不同。在几个文件的列名下面以获得一个想法： - Balans（geconsolideerd）'13 - > '14 31-12-2014 31-12-2013 31-12-2012 31-12-2011 31-12-2010 ; - Balans（geconsolideerd）'07 - > '08 31-12-2008 31-12-2007 31-12-2006 - Balans（geconsolideerd）'13 - > '14 31-12-2014 31-12-2013 31 -12-2012 31-12-2011 31-12-2010 31-12-2009 31-12-2008 31-12-2007 31-12-2006 31-12-2005 31-12-2004 31-12-2003
最后，我们只对'Balans（geconsolideerd）'以及5年（当前年份 - 2年和4年前）的数据感兴趣。所以，现在是2014年 - 2010年。列中有 - >标志应该被删除，即使是超出范围的年份列，但这将是第2步。
第一步是获取所有数据将所有6000个文件的第一张表放入数据表/临时表中。
你能帮助我吗？如果您需要更多信息或者我需要澄清一些事情，请告诉我。
提前致谢。
Chelsea Disseldorp
我尝试过：
很多东西已经很不幸了。
I am having an urgent issue. I couldn't find any answers for this specific scenario:
Import multiple Excel files, with multiple sheets, and different meta data into one SQL table. The first sheet of each Excel file (6000 files in total) has to be imported into a SQL table. There will only be one table for all the first sheets of these files together.
We found out that we have to use C# (script task in SSIS).
The meta data differs between the files. Below the column names of a few files to get an idea: - Balans (geconsolideerd) '13->'14 31-12-2014 31-12-2013 31-12-2012 31-12-2011 31-12-2010; - Balans (geconsolideerd) '07->'08 31-12-2008 31-12-2007 31-12-2006 - Balans (geconsolideerd) '13->'14 31-12-2014 31-12-2013 31-12-2012 31-12-2011 31-12-2010 31-12-2009 31-12-2008 31-12-2007 31-12-2006 31-12-2005 31-12-2004 31-12-2003
At the end, we are only interested in 'Balans (geconsolideerd)' and data of 5 years (current year - 2 and the 4 years before). So, right now 2014 - 2010. The column with the -> sign should be dropped, even as the year columns which are out of the range, but this will be step 2.
The first step is to get all the data of the first sheet of all the 6000 files into a data table/ temporary table.
Can you help me? If you need more information or if I need to clarify things, please let me know as well.
Thanks in advance.
Chelsea Disseldorp
What I have tried:
A lot of things already, unfortunately.
推荐答案
将Excel信息导入数据表非常容易，如使用MS Excel（xls / xlsx）使用MDAC和Oledb [< a href =http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oledtarget =_ blanktitle =New Window> ^ 。获得原始数据后，您可以通过普通SQL操作并将相关信息写入数据库。
It is quite easy to import Excel information into a datatable as described in Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^]. Once you have the raw data you can then manipulate and write the relevant information to your database via normal SQL.
这篇关于如何将一张包含动态元数据的多个Excel文件导入一个SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
