Author Posts

July 19, 2018 at 11:43 am

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.

July 19, 2018 at 12:44 pm

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

July 19, 2018 at 1:26 pm

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