Hey, Scripting Guy! How Can I Import Data into Office Access from Office Excel?

JS, you have had such a defining moment in boiling down the essence of your needs. We will be glad to be your Leonore and save you from spreadsheet prison.

The ImportSpreadSheetToAccess.ps1 script is seen here.

$Import = 0
$SpreadSheetType = 8
$TableName = "MyTest"
$FileName = "C:\Fso\Test.xls"
$HasFieldNames = $True
$Range = "A1:C29"
$access = New-Object -ComObject Access.Application
$Access.OpenCurrentDataBase("C:\fso\Test.mdb")
$Access.DoCmd.TransferSpreadSheet($Import, $SpreadSheetType, $TableName, $FileName,$HasFieldNames,$Range)
$Access.Quit()

In the ImportSpreadSheetToAccess.ps1 script, we import an Office Excel spreadsheet containing the data, such as seen here:

The first thing we do in this script is define a few variables and assign the appropriate values to them. These values will all be used when we get to the DoCmd.TransferSpreadSheet method. For now, we are simply assigning various strings and integers to some variables. In Windows PowerShell, all variables begin with a dollar sign. We will talk about these variable values in just a little bit.

$Import = 0
$SpreadSheetType = 8
$TableName = "MyTest"
$FileName = "C:\Fso\Test.xls"
$HasFieldNames = $True
$Range = "A1:C29"

We next create an instance of the Access.Application object. This is the main object we use when working with the Access automation model. The Access.Application object is documented on MSDN. To create the Access.Application object, we use the New-Object cmdlet and the ComObject parameter. The ComObject parameter tells the New-Object cmdlet that we are creating a COM object. If you are using the same kind of object you used in an old VBScript script, it is a COM object. The code that creates this object is seen here:

$access = New-Object -ComObject Access.Application

If you are not sure whether you have a COM object and do not want to see an error, use the ComObject parameter. If you actually have a .NET Framework class, the ComObject parameter will be ignored. Pretty cool, huh? You want an example? Okay. System.Random is a .NET Framework class, but as you can see here, we use New-Object and the ComObject parameter and pipe the results to Get-Member. We see the members of the System.Random class. We next assign the object to the variable $a and call the Next method, generating the next random number. This proves that Windows PowerShell ignores the ComObject parameter when working with a .NET Framework class:

PS C:\> New-Object -ComObject System.Random | Get-Member
   TypeName: System.Random
Name        MemberType Definition
----        ---------- ----------
Equals      Method     System.Boolean Equals(Object obj)
GetHashCode Method     System.Int32 GetHashCode()
GetType     Method     System.Type GetType()
Next        Method     System.Int32 Next(), System.Int32 Next(Int32 minValue, Int32 maxValue), System.Int32 Next(Int...
NextBytes   Method     System.Void NextBytes(Byte[] buffer)
NextDouble  Method     System.Double NextDouble()
ToString    Method     System.String ToString()
PS C:\> $a = New-Object -ComObject System.Random
PS C:\> $a.Next()
1273012407

After have created the Access.Application object, we use the OpenCurrentDataBase method to open the database (no surprise there):

$Access.OpenCurrentDataBase("C:\fso\Test.mdb")

After we have opened the database, we use the DoCmd object. The DoCmd object is a remarkable object that has the ability to perform a large number of different commands. The methods are seen in Table 1. More information about the DoCmd object can be found on MSDN.

The TransferSpreadSheet method from the DoCmd object has a signature that accepts seven optional arguments. The argument names, data types, and descriptions are seen in Table 2.

var tableId = “EIEAE”; sortedCol[tableId] = null; sortFactor[tableId] = 1; initialSort[tableId] = true;

Table 2 TransferSpreadSheet Method arguments
Name	Data Type	Description
TransferType	AcDataTransferType	The type of transfer you want to make. The default value is acImport.
SpreadsheetType	AcSpreadSheetType	The type of spreadsheet to import from, export to, or link to.
TableName	Variant	A string expression that is the name of the Microsoft Office Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Office Access select query whose results you want to export to a spreadsheet.
FileName	Variant	A string expression that’s the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNames	Variant	Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet, no matter what you enter for this argument.
Range	Variant	A string expression that’s a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
UseOA	Variant	This argument is not supported.

The TransferSpreadSheet method signature is seen here:

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

The first argument is the TransferType argument, which can take three potential values. These values need to be an instance of the AcDataTransferType enumeration. These enumeration values are seen in Table 3. In our script, we use the variable named $import and gave it the value of 1. From Table 3 we can tell that a value of 1 means the data is exported.

var tableId = “EBHAE”; sortedCol[tableId] = null; sortFactor[tableId] = 1; initialSort[tableId] = true;

Table 3 TransferSpreadSheet method TransferType enumeration values
Name	Value	Description
acExport	1	The data is exported.
acImport	0	(Default) The data is imported.
acLink	2	The database is linked to the specified data source.

The second argument is the Spreadsheet argument. There are 10 different enumerations listed. but only 8 different values. You will also notice from Table 4 that the Office 2007 formatted spreadsheet is not a supported format. This is why our spreadsheet is an .xls file and was saved in Office Excel 2000 format.

var tableId = “EPIAE”; sortedCol[tableId] = null; sortFactor[tableId] = 1; initialSort[tableId] = true;

Table 4 TransferSpreadSheet method SpreadsheetType enumeration values
Name	Value	Description
acSpreadsheetTypeExcel3	0	Microsoft Excel 3.0 format
acSpreadsheetTypeExcel4	6	Microsoft Excel 4.0 format
acSpreadsheetTypeExcel5	5	Microsoft Excel 5.0 format
acSpreadsheetTypeExcel7	5	Microsoft Excel 95 format
acSpreadsheetTypeExcel8	8	Microsoft Excel 97 format
acSpreadsheetTypeExcel9	8	Microsoft Excel 2000 format
acSpreadsheetTypeLotusWJ2	4	Japanese version only
acSpreadsheetTypeLotusWK1	2	Lotus 1-2-3 WK1 format
acSpreadsheetTypeLotusWK3	3	Lotus 1-2-3 WK3 format
acSpreadsheetTypeLotusWK4	7	Lotus 1-2-3 WK4 format

We could have used the Enumerations directly by first loading the Access Interop assembly. To do this in Windows PowerShell 1.0, we can use the LoadWithPartialName static method from the Reflection.Assembly .NET Framework class. After we have loaded the Interop assembly, we can create the two enumeration classes as seen here:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Access") | Out-Null
$AcDataTransferType = "Microsoft.Office.Interop.Access.AcDataTransferType" -as [type]
$AcSpreadSheetType = "Microsoft.Office.Interop.Access.AcSpreadSheetType" -as [type]

If we had done that, we could then call the TransferSpreadSheet method and used the enumeration values directly; the TransferSpreadSheet method call would have looked like the following:

$Access.DoCmd.TransferSpreadSheet($AcDataTransferType::acImport,
$AcSpreadSheetType::AcSpreadSheetTypeExcel9, $TableName, $FileName,$HasFieldNames,$Range)

Instead, for simplicity’s sake, we used the methodology more in keeping with the VBScript version of this script. The TableName is the table name in the Office Access database, and the FileName argument is the path and name of the Office Excel spreadsheet to import. The HasFieldNames argument is a Boolean value that indicates whether or not the first row of the Office Excel spreadsheet contains the field names. The range is used to specify the range in the Office Excel spreadsheet to import. The first number indicates the upper left cell reference, and the second number is the lower right reference. The method call is shown here:

$Access.DoCmd.TransferSpreadSheet($Import, $SpreadSheetType, $TableName, $FileName,$HasFieldNames,$Range)

If you have an autonumber field in your database, make sure you do not try to import a row from the Office Excel spreadsheet with the same field name or you will get an error. Also pay attention to the data types expected by the fields in the Office Access database, and make sure that your import data matches up. For instance, you will get an error trying to import a string into a field that expects a date type.

After we have imported the spreadsheet, we use the Quit method to close out Access.Application object. This is a very important step that will prevent multiple copies of Office Access from running:

$Access.Quit()

The completed Office Access database with the newly imported data is seen here:

Well, JS, this concludes writing to an Office Access database. It also concludes our Office Access Database Week articles. Join us tomorrow for Quick-Hits Friday. 

Ed Wilson and Craig Liebendorfer, Scripting Guys

ScriptingGuy1

Follow