Keep decimal precision when PowerShell Access.Application Transfertext exports Number column to CSV file

Question

I'm exporting data from Access tables to CSV files using powershell on a Windows 2012 R2 Server. I'd like to do this without modifying the Access database schema and without writing any VBA code in the access database.

The powershell script boils down to something like this:

$Table = 'tblUsers'
$Filepath = 'C:\tblUsers.csv'
$Acc = New-Object -Com Access.Application
$Acc.OpenCurrentDataBase($DBFilePath)
$Acc.DoCmd.Transfertext(2, [Type]::Missing, $Table, $Filepath, 1)

One of the tables has a column with a Number field where the Field Size is Double, Format is Percent, and Decimal Places are set to 2. The field contains values like 100%, 50%, and 87.5%. When I run this command the output in the CSV for a 100% value is ,1.00, while the output for 87.5% is ,0.87,. It seems that something in the process is dropping significant digits of precision for these columns.

Is there a way in the script to ensure I get all the significant digits of every column in the database? Are there other kinds of columns to be worried about?

There are 2 solutions I know that have drawbacks:

If it is at all possible, I want a new answer instead of these 2. These 2 answers have significant drawbacks.

1. From this forum thread: Change the language settings on the computer so it shows more (up to 9) decimals. I don't want the whole computer decimal setting to have to change and I don't want to just have a new limit at 9 instead of 2 - what if a number in the database has 15 significant digits after the decimal point?
2. Use a query to specify the data and cast the number to a string. This has the drawback of requiring me to review the definition of each column and write queries for every table. I'm exporting 30+ tables with hundreds of columns so this would be too much work.

Thermophone

I cannot reproduce after defining a numeric column with exact types as you mention. and exporting with same arguments. CSV data maintains two decimal points for all rows. Be sure to check output in a text editor (not Excel which can apply formatting on open). – Jocelin

@Jocelin thanks for your work to reproduce the issue. I'm inspecting the resulting CSV files using grep on a Mac and Linux machines. Are you on Windows 2012? And If you go into the control panel to change the language settings so numbers have 2 decimal places do you still get all the digits in the output? – Thermophone

Possible duplicate of How do I keep the necessary decimal places when using the DoCmd.TransferText command to export a table to a .csv? – Pickel

I don't think this is a duplicate since it's about PowerShell and not about VBA, but maybe I just don't know enough about PowerShell and VBA to know that the same technique can be applied in Powershell? – Thermophone

Maybe this link helps Use powershell to create queries in MS-Access – Kweisui

"Is there a way in the script to ensure I get all the significant digits of every column in the database?" Yes: don't convert everything to text. Use a database connection as shown here: PowerShell and MS Access database. The properties in the datatable(s) will be in their native type format. – Valval

It would most helpful to share a copy of your table to check exactly how it fails...just strip sensitive data . – Natalianatalie

The answer is the same as the suggested duplicate, though, you need to write your own exporter if you don't want the drawbacks. I can provide a sample one, however, you'd need to be explicit about how you want floating-point numbers to be handled because these don't really have a concept of significant digits. – Preconception

Answer 1

This code should export the results without any modifications to the Access table or computer settings.

1) It uses Microsoft.Jet.OLEDB.4.0 driver

2) The only limitation is that it must be run via PowerShell x86, due to this limitation. Alternativly, you can install the driver for 64-bit (link in the post).

This access table (tblFruit) has 3 fields:

• ID - AutoNumber
• Fruit - ShortText
• Percent - Number, Field Size: Double, Format: Percent, Decimal Places: 2.

$csvPath = 'C:\temp\output.csv'
$dbPath = 'C:\temp\database1.mdb'
$strQuery = "SELECT * from tblFruit"

$strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dbPath"

$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
$connection.ConnectionString = $strConn 
$command = $connection.CreateCommand()
$command.CommandText = $strQuery
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset)
$dataset.Tables[0] | export-csv $csvPath -NoTypeInformation
$connection.Close()

Output:

"ID","Fruit","Percent"
"1","Apple","0.0142"
"2","Orange","0.3412"
"3","Banana","0.8715"

Lubin