Categories

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

Leave a Reply