Going from Get-ACEData to CSV

Welcome Forums General PowerShell Q&A Going from Get-ACEData to CSV

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

 
Participant
4 months ago.

  • Author
    Posts
  • #104674

    Participant
    Points: 129
    Rank: Participant

    I am using the Get-ACEData cmdlet to extract data from a simple ACCESS database.
    I have all the required ACE modules etc. installed and confirmed working properly.
    The database I am trying to extract data from is extremely simple: 1 table only and about 5 fields. I use the statement ('Table1' is the actual name of the only table in DBName, and DBName is of type .mdb):

    Gate-ACEData -Filepath DBName -Table Table1 -Query 'Select LName,FName,DOB from Table1' | ??? I am lost !!!

    I suppose (perhaps incorrectly) that when the Get-ACEData statement is executed, it generates into the pipeline some object of type DATASET (is this = type ACCESS database?). I would like to transform it into a simple CSV file for subsequenet processing.

    But I cannot seem to get Export-CSV, ConvertTo-CSV, etc. cmdlets to work. I must be missing something. Or perhaps there is some function somewhere that can do the job. Does such a cmdlet/function exist, or do I have to "go lower" and resort to using ADO.Net to manipulate the ACCESS DB with additional PS code?

    Would be grateful for any hints, tips or advice.
    Thanks.

  • #104692

    Participant
    Points: 160
    Helping Hand
    Rank: Participant

    Datasets are a .NET object. This code assumes you've worked with datasets before. There can be multiple tables, but you are querying a single table. I have not tested the code, but hopefully this points you in the correct decision:

    $query = Get-ACEData -Filepath .\mydatabase.mdb -Table Table1 -Query 'Select LName,FName,DOB from Table1' 
    
    #Get results from the first table
    $query.Tables[0]
    

    Once you confirm you see the expected results, you need to understand that most Export commands expect that you are piping a PSObject. Select-Object will convert data into a PSObject. Try something like this:

    $query.Tables[0] | Select LName,FName,DOB | Export-CSV -Path "C:\Scripts\MyCSV.csv" -NoTypeInformation
    
    • #104699

      Participant
      Points: 129
      Rank: Participant

      Fantastic, this is very useful.
      My sincerest thanks Mr. Simmers.

The topic ‘Going from Get-ACEData to CSV’ is closed to new replies.