PS script to export selected columns from Access DB to CSV

This topic contains 2 replies, has 2 voices, and was last updated by  Rishabh Verma 7 months ago.

  • Author
    Posts
  • #66763

    Rishabh Verma
    Participant

    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 
    
  • #66765

    Sam Boutros
    Participant
    # 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 
    
    • #66768

      Rishabh Verma
      Participant

      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.

You must be logged in to reply to this topic.