导入Excel 文件数据到Access 表中，如何简单又高效插入数据

lms008

本人有个Access 的项目，有个需求是要Import Data, 导入Excel文件的数据。客户会提供Excel 格式的模板数据，从导入的Excel文件中读取数据保存到Access 对应的表中。
主要信息:
1): 在Access 中有个表(impDataColumDef) 定义好了需要导入三个文件的列名(Column Name) 和列值(Column_Item,e.g A,B,C.....)，及导入数据应保存的表名。
例如： 导入 Product Deal 数据，在(impDataColumDef 表中定义好了 表名是Product_Deal 下的列有： Column_Name---> Unit, Column_Item ---> A 等39条记录（代表39个字段）.
2):在读取导入数据前，先要校验导入的Excel 文件第一行 对应的每一列的列名是否在表(impDataColumDef)中已经定义。如果检查到是未定义的则 报出错误（Error message），退出导入的动作。
        我目前的代码已经可以实现这个导入功能和导入前的校验。 我是根据定义表(impDataColumDef)的列名和列值去查询数据判断是否存在。
从第二行开始读取数据，读取每个单元格的值去对应定义表(impDataColumDef)中对应的列名。所以插入表的SQL语句中，Column Name 和Value 就是固定的。
如： strFieldVal(0) = Trim(excelSheet.Cells(intBinRow, 1)) ’intBinRow 代表行
       'Instrument
        strFieldVal(1) = Trim(excelSheet.Cells(intBinRow, 2))
        'Year
        strFieldVal(2) = Trim(excelSheet.Cells(intBinRow, 3))
        'Period
        strFieldVal(3) = Trim(excelSheet.Cells(intBinRow, 4))
  strInsertSql = "INSERT INTO PrdDealDta ([Unit], [Instrument], [Year],[Period],[Scenario],[As_Of],[Led_Code],[Fund_Center], " & _
                     " [FTP_Seq_Typ],[Oper_Unit],[Customer],[Product_ID],[Channel],[DeptID],[Ccy],[Base_Curr],[Sum_Balance_BCE], " & _
                     " [Sum_Balance],[Sum_FTP_BCE_Amt], [Sum_FTP_Amt], [Basis_ID],[FTP_Rule],[FTP_Rate],[Interest_Rate],[Spread_Rate], " & _
                     " [Start_Date], [End_Date],[Commit_Date],[Last_Reprice_Dte], [Next_Reprice],[Price_Freq_UOM],[Reprice_Freq]," & _
                     " [Rate_Schedule],[Reset_Type],[FTP_RWC_Bal],[Status],[Term],[Term_Basis],[Instance])" & _
                     " SELECT '" & strFieldVal(0) & "', '" & strFieldVal(1) & "', " & strFieldVal(2) & ", " & strFieldVal(3) & ", " & _
                     " '" & strFieldVal(4) & "', " & strFieldVal(5) & ",'" & strFieldVal(6) & "', '" & strFieldVal(7) & "','" & strFieldVal(8) & "', " & _
                     " '" & strFieldVal(9) & "','" & strFieldVal(10) & "', '" & strFieldVal(11) & "', '" & strFieldVal(12) & "', '" & strFieldVal(13) & "', " & _
                     " '" & strFieldVal(14) & "','" & strFieldVal(15) & "', " & strFieldVal(16) & ", " & strFieldVal(17) & ", " & strFieldVal(18) & ", " & _
                     " " & strFieldVal(19) & ",'" & strFieldVal(20) & "', '" & strFieldVal(21) & "', " & strFieldVal(22) & ", " & strFieldVal(23) & ", " & _
                     " " & strFieldVal(24) & "," & strFieldVal(25) & ", " & strFieldVal(26) & ", " & strFieldVal(27) & ", " & strFieldVal(28) & ", " & _
                     " " & strFieldVal(29) & ",'" & strFieldVal(30) & "', " & strFieldVal(31) & ", '" & strFieldVal(32) & "', '" & strFieldVal(33) & "', " & _
                     " '" & strFieldVal(34) & "','" & strFieldVal(35) & "', " & strFieldVal(36) & ", '" & strFieldVal(37) & "', " & strFieldVal(38) & " "


问题是：客户想导入Excel 数据的时候，想改变原来定义好位置的列对应数据，比如原来A列是 Unit，B列是Instrument 的值； 现在调整为A列Instrument的值，B列是Unit的值。
如原来是附件中的 Product Deal. xlsx 数据才能正常导入数据库， 改变后的需求是能 Product Deal_Change.xlsx 文件的数据能正确导入到Access表中。



我想到的方法是: 在校验列名的时候，只需把读取第一行对应的列名称去数据表中查询记录，如果第一行的所有单元格（39）的值都在表中存在，则继续从第二行开始读取数据。
  Do
   1）读取（RowNum,1） RowNum 从第二行开始，取到（RowNum,1）去表(impDataColumDef) 查询到对应的列名，
   Insert into PrdDealDta(列名,...) values(（RowNum,1）....)
   2) 读取（RowNum,2）,读取第二个单元格，取到（RowNum,2）去表(impDataColumDef) 查询到对应的列名，
  Update 刚Insert 的那条记录，Update  PrdDealDta set xxxx=  （RowNum,2） where xxxx =
   3).读取（RowNum,3） ,之后的单元格一直按着步骤 2），去更新字段值。
  .....................
         Loop While （跳出循环的条件）
这种做法可以实现想要的需求，但是代码太多，而且import 的效率低下。

想问各位大神有没方法既简单又高效的把改变后的Excel 文件导入到数据库对应的表中(PrdDealDta ),只需检查导入文件的列名(Column name) 已经在表(impDataColumDef) 定义好，不需要与定义好的列值（A,B.....）的一致。


附件:

lms008

求各位老师给出建议或示例，谢谢！

marco

/*  
ACCESS与EXCEL文件的互导  
  
一、将表导出为EXCEL07-2003表：  
Select * INTO [EXCEL 8.0;DATABASE=E:\TEMP\info.XLS].info  
FROM Sheet2;  
  
二、将表导出为EXCEL2007/2010表（二进制）：  
Select * INTO [EXCEL 12.0;DATABASE=E:\TEMP\info.XLSB]. info  
FROM Sheet2;  
  
三、将表导出为EXCEL2007/2010表（XML）：  
Select * INTO [EXCEL 12.0 XML;DATABASE=E:\temp\text.xlsx].sheet1 FROM Sheet2;  
  
四、将EXCEL表导入到现有表中：  
Insert INTO ABC Select * FROM [EXCEL 12.0 XML;DATABASE=E:\temp\text.xlsx].sheet1;  
  
五、导入EXCEL表并生成新表：  
Select * INTO ABC FROM [EXCEL 12.0 XML;DATABASE=E:\temp\text.xlsx].sheet1;   
*/  

lms008

marco 发表于 2017-9-12 09:18
/*  
ACCESS与EXCEL文件的互导  
  

感谢 版主的回复。