examples/02-Csv-To-Access.ps1
|
# 02 - Import any CSV into a new Access table. The column types are worked out from the data (whole numbers, # decimals, dates, yes/no, text), and every row goes in with ONE bulk write - thousands of rows in seconds. # ./02-Csv-To-Access.ps1 -Csv ./sales.csv -Database ./Sales.accdb -Table Sales # With no -Csv it imports a sample: the processes running on this computer. param([string] $Csv = '', [string] $Database = '', [string] $Table = 'Imported') Import-Module kAccess $Folder = Join-Path ([IO.Path]::GetTempPath()) 'kAccess-examples' New-Item $Folder -ItemType Directory -Force | Out-Null if ($Csv -eq '') { $Csv = Join-Path $Folder 'processes.csv' Get-Process | Select-Object Name, Id, @{ n = 'MemoryMB'; e = { [math]::Round($_.WorkingSet64 / 1MB, 1) } }, @{ n = 'Started'; e = { try { $_.StartTime } catch { $null } } } | Export-Csv $Csv $Table = 'Processes' } if ($Database -eq '') { $Database = Join-Path $Folder 'Imported.accdb' } $Data = @(Import-Csv $Csv) if ($Data.Count -eq 0) { throw "$Csv has no rows" } # The narrowest Access type that holds every value of a column. function Get-ColumnType([object[]] $Values) { $Present = @($Values | Where-Object { $_ -ne '' -and $null -ne $_ }) if ($Present.Count -eq 0) { return 'TEXT' } [long] $L = 0; [double] $D = 0; [datetime] $T = [datetime]::MinValue if (@($Present | Where-Object { -not [long]::TryParse($_, [ref] $L) }).Count -eq 0) { return 'LONG' } if (@($Present | Where-Object { -not [double]::TryParse($_, [Globalization.NumberStyles]::Float, [cultureinfo]::InvariantCulture, [ref] $D) }).Count -eq 0) { return 'DOUBLE' } if (@($Present | Where-Object { $_ -notin 'True', 'False' }).Count -eq 0) { return 'BOOLEAN' } if (@($Present | Where-Object { -not [datetime]::TryParse($_, [ref] $T) }).Count -eq 0) { return 'SHORT_DATE_TIME' } if (@($Present | Where-Object { $_.Length -gt 255 }).Count -gt 0) { return 'MEMO' } return 'TEXT' } $Names = $Data[0].PSObject.Properties.Name $Types = @{} $Columns = @(@{ Name = 'RowId'; Type = 'LONG'; AutoNumber = $true }) foreach ($Name in $Names) { $Types[$Name] = Get-ColumnType @($Data | ForEach-Object { $_.$Name }) $Columns += @{ Name = $Name; Type = $Types[$Name] } } if (-not (Test-Path $Database)) { New-kAccessDatabase $Database | Out-Null } New-kAccessTable $Database $Table -Column $Columns -Index @{ Name = 'PrimaryKey'; Columns = @('RowId'); PrimaryKey = $true } $Watch = [Diagnostics.Stopwatch]::StartNew() $Data | ForEach-Object { $Row = @{} foreach ($Name in $Names) { $Text = $_.$Name if ($Text -eq '' -or $null -eq $Text) { continue } $Row[$Name] = switch ($Types[$Name]) { 'LONG' { [long] $Text } 'DOUBLE' { [double]::Parse($Text, [cultureinfo]::InvariantCulture) } 'BOOLEAN' { $Text -eq 'True' } 'SHORT_DATE_TIME' { [datetime] $Text } default { $Text } } } $Row } | Add-kAccessRow $Database $Table "{0} rows imported into {1} in {2:n1} s" -f $Data.Count, $Table, $Watch.Elapsed.TotalSeconds Get-kAccessTable $Database $Table | Select-Object -ExpandProperty Columns | Format-Table Name, Type |