用powershell将accdb转换为csv

| 我正在尝试将一些Excel（.xlsx）和Access（.accdb）转换为CSV文件。我很快找到了一种使用Excel进行此操作的方法，但现在找不到关于转换.accdb文件的任何有用的文档。到目前为止，我有：

        $adOpenStatic = 3
        $adLockOptimistic = 3

        $objConnection = New-Object -com \"ADODB.Connection\"
        $objRecordSet = New-Object -com \"ADODB.Recordset\"


        $objConnection.Open(\"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \" + $Filepath)
        $objRecordset.Open(\"Select * From TableName\",$objConnection,$adOpenStatic, $adLockOptimistic)

#Here I need some way to either saveas .csv or loop through
#each row and pass to csv.

        $objRecordSet.Close()
        $objConnection.Close()

有任何想法吗？如果有人知道的话，我愿意用另一种语言（VB，Java，PHP）来做。

已邀请:

2 个回复

摊揉售

如果要坚持使用ADODB COM对象：

# loop through all records - do work on each record to convert it to CSV
$objRecordset.Open(\"Select * FROM Tablename\", $objConnection,$adOpenStatic,$adLockOptimistic)

$objRecordset.MoveFirst()

do {
# do your work to get each field and convert this item to CSV
# fields available thru: $objRecordset.Fields[\'fieldname\'].Value
$objRecordset.MoveNext()

} while ($objRecordset.EOF -eq $false)

$objRecordset.Close()

坛沤疲撑拆

如果您使用.NET而不是COM，那么会容易得多。这是一些处理Excel XLSX文件的代码

#Even /w Excel 2010 installed, needed to install ACE:
#http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
#Becareful about executing in \"right\" version x86 vs. x64
#Change these settings as needed
$filepath = \'C:\\Users\\u00\\Documents\\backupset.xlsx\'
#Comment/Uncomment connection string based on version
#Connection String for Excel 2007:
$connString = \"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`\"$filepath`\";Extended Properties=`\"Excel 12.0 Xml;HDR=YES`\";\"
#Connection String for Excel 2003:
#$connString = \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`\"$filepath`\";Extended Properties=`\"Excel 8.0;HDR=Yes;IMEX=1`\";\"
$qry = \'select * from [backupset$]\'


$conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn) 
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
$dt = new-object System.Data.dataTable 
[void]$da.fill($dt)
$conn.close()
$dt | export-csv ./test.csv -NoTypeInformation

要回复问题请先登录或注册

用powershell将accdb转换为csv

2 个回复

发起人

powershell

ms_access

csv

问题状态

用powershell将accdb转换为csv

与内容相关的链接

2 个回复

发起人

powershell

ms_access

csv

问题状态