PS script to export selected columns from Access DB to CSV

Welcome Forums General PowerShell Q&A PS script to export selected columns from Access DB to CSV

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 year, 11 months ago.

  • Author
    Posts
  • #66763

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 176
    Helping Hand
    Rank: 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

      Participant
      Points: 0
      Rank: Member

      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.

The topic ‘PS script to export selected columns from Access DB to CSV’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort