Access Bulk Load data
We have already seen how to load individual records into an Access Table. Sometime we require the ability to add multiple records. We can easily adapt the way we use our Add-AccessRecord function to accommodate a bulk load scenario.
Lets create a csv file with the information
FirstName,LastName,DOB
Dave,Brown,15/06/1982
Jo,Black,27/07/1982
Alex,White,30/06/1982
Bill,Smith,17/07/1982
We can then write a script to read the csv file and add the data
| 001
002
003
004
005
006
007
008
009
010
011
| Import-Module AccessFunctions
$db = Open-AccessDatabase -name test03.mdb -path c:\test
Import-Csv -Path c:\test\names.csv | foreach {
$value = " ""$($_.FirstName)"", ""$($_.LastName)"", ""$($_.DOB)"" "
$value
Add-AccessRecord -connection $db -table test1 -values $value
}
Get-AccessData -sql "select * from test1" -connection $db -grid
Close-AccessDatabase $db
Remove-Module AccessFunctions |
Start by importing the accessfunctions module and open the database.
use Import-Csv ro read the data file. Pipe the data into a foreach where we concatenate the values to give a single string. I’m using string substitution to achieve this. Notice the use of “” round the data values. This is to ensure that we get a each value wrapped as “value” when it is passed into the function.
Once we have added the data we can use the Get-AccessData function to check our data has been inserted.
Final actions are to close the database and remove the module