Author Posts

March 20, 2017 at 6:27 am

I am having multiple columns in Access DB. I want 2 columns from there exported to CSV. I can see them in console screen but unable to output them as CSV. Code i am using is :

$adOpenStatic = 3
$adLockOptimistic = 3
$conn=New-Object -com "ADODB.Connection"
$rs = New-Object -com "ADODB.Recordset"
$conn.Open('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\risha\Desktop\PowerShell DataBase\dba.mdb;Persist Security Info=True;')
$rs.Open("SELECT * FROM inclearing",$conn,$adOpenStatic,$adLockOptimistic)
$rs.MoveFirst();
do
{ 
$rs.Fields.Item("Column1").value
$rs.Fields.Item("Column9").value
$rs.MoveNext();
}until($rs.EOF -eq $true)
$conn.Close
$rs.Close

I tried this also :

$rs.Open("SELECT Column1,Column9 FROM inclearing",$conn,$adOpenStatic,$adLockOptimistic) | Export-Csv -Path C:\Users\risha\Desktop\test1.csv -Encoding "UTF8" -NoT 

March 20, 2017 at 8:16 am

# Requires PS x86 or ISE x86 NOT x64

$DBFilePath = 'C:\Users\risha\Desktop\PowerShell DataBase\dba.mdb'
$adOpenStatic = 3
$adLockOptimistic = 3

$conn=New-Object -com "ADODB.Connection"
$rs = New-Object -com "ADODB.Recordset"
$conn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$DBFilePath;Persist Security Info=True;")
$rs.Open('SELECT * FROM inclearing',$conn,$adOpenStatic,$adLockOptimistic)
$rs.MoveFirst()
$myOuput = While ( ! $rs.EOF ) {
    New-Object -TypeName PSObject -Property @{
        column1 = $rs.Fields.Item('column1').Value
        column9 = $rs.Fields.Item('column9').Value
    }
    $rs.MoveNext()
}
$conn.Close
$rs.Close

$myOuput | FT -a 
$myOuput | Out-GridView
$myOuput | Export-Csv .\inclearing.csv -notype 

March 20, 2017 at 9:02 am

Thank you so much Sir. It worked as expected.
But it worked with PowerShell x64 as mine MS-office is also x64. The x86 ISE was giving errors.