业务情景:每天需要把20个xlsx格式文件的内容导入到数据库。
软件环境:业务用户工作台为Windows系统,Informatica服务器为CentOS系统
1、如果INFA服务器为Windows,则可以在ODBC创建用于导入xls的DNS,把xls作为数据库源导入数据。
参考 HOW TO: Use a Microsoft Excel worksheet as a source in a PowerCenter session on Windows
2、如果INFA服务器为类UNIX系统,则需要额外的Windows机器用做 Sequelink 服务器。(不懂Sequelink)
参考 HOW TO: Use a Microsoft Excel worksheet as a source in Developer Client while services are running on UNIX server
3、最终选择了批量转换xlsx格式文件为csv文件,上传到INFA服务器作为平面文件源。
参考 VB Script to convert multiple Excel files to CSV
脚本下载 链接: https://pan.baidu.com/s/1jI1I2J8 密码: jpva
' vbs脚本
Set lstArgs = WScript.Arguments
For I = 0 to lstArgs.Count - 1 ' Loop through each file
FullName = lstArgs(I)
FileName = Left(lstArgs(I), InStrRev(lstArgs(I), ".") )
' Create Excel Objects (我电脑只装了WPS)
Set objWS = CreateObject("Excel.application")
set objWB = objWS.Workbooks.Open(FullName)
objWS.application.visible=false
objWS.application.displayalerts=false
'MsgBox FileName (23为文件格式,修改为6才能被INFA识别)
objWB.SaveAs FileName & "csv", 23
objWB.SaveAs
objWS.Application.Quit
objWS.Quit
' Destroy Excel Objects
Set objWS = Nothing
set objWB = Nothing
Next
我的修改的脚本如下,另存在vbs脚本所在目录,文件格式为xlCSV。参考 ExcelApplication 另存Excel的SaveAs函数
Set lstArgs = WScript.Arguments
Set fs = CreateObject("Scripting.FileSystemObject")
CurrentPath = left(wscript.scriptfullname,instrrev(wscript.scriptfullname,"\")-1)
For I = 0 to lstArgs.Count - 1 ' Loop through each file
Source = lstArgs(I)
FileName = fs.GetFileName(lstArgs(I))
FileName = Left(FileName, InStrRev(FileName, ".") )
Target = CurrentPath & "\" & FileName & "csv"
' Create Excel Objects
Set objWS = CreateObject("Excel.application")
set objWB = objWS.Workbooks.Open(Source)
objWS.application.visible=false
objWS.application.displayalerts=false
'MsgBox FileName
objWB.SaveAs Target, 6
objWB.SaveAs
objWS.Application.Quit
objWS.Quit
' Destroy Excel Objects
Set objWS = Nothing
set objWB = Nothing
Next