Converting accdb to csv with powershell

Question

I am trying to convert some excel (.xlsx) and Access (.accdb) to CSV files.

I quickly found a way to do this with Excel but now I cannot find any helpful documentation on converting .accdb files.

So far I have:

        $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()

Any Ideas?

I would be willing to do this with another language (VB, Java, PHP) if anyone knows a way.

Chad Miller · Accepted Answer · 2011-04-19 02:54:29Z

3

If you use .NET rather than COM it's a lot easier. Here's some code to handle the Excel XLSX files

#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

answered Apr 19, 2011 at 2:54

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Boullion Over a year ago

I already have a very easy way of converting Excel to CSV using an "Excel.saveas" property of an excel object. Could this also handle Access obects? (.accdb or .mdb)?

Chad Miller Over a year ago

Yes -- Just change the connection string and query. MS Access uses the ACE provider also. See link to MS Access Connection string format: connectionstrings.com/access-2007

Daniel Richnak · Accepted Answer · 2011-04-19 17:56:14Z

1

If you want to stick with ADODB COM object:

# 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()

answered Apr 19, 2011 at 17:56

Daniel Richnak

1,6249 silver badges12 bronze badges

Collectives™ on Stack Overflow

Converting accdb to csv with powershell

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related