CODEWITHSUNDEEP
powershellms-accessexport-to-csv
greggles
greggles

Reputation: 2185

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

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.

Upvotes: 6

Views: 902

Answers (1)

EylM
EylM

Reputation: 6103

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.

Table Data

$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"

Upvotes: 1

Related Questions