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

Hey, Scripting Guy! I need to do hardware inventory on all our workstations and on all our servers. I know System Center Configuration Manager can do this and a whole lot more. We actually have it budgeted for deployment next year. However, that does not help me now. We had a summer intern that went around and typed a lot of information such as make, model, and serial number into an Office Excel spreadsheet. In fact that is about all he did was go around typing information into an Office Excel spreadsheet, and it has been a big help to us. But I would like more information, and I do not have the time or the patience to type a bunch of information into a spreadsheet. What I would really like is to have the information in a database instead of an Office Excel spreadsheet. Can you figure out a way to import the data from Office Excel into Office Access?

- JS

Hi JS,

When the trumpet call split the air with the message that the minister of state was within sight and that Leonore had saved her husband, the audience of Fidelio readily appreciated they had just witnessed one of the great scenes of opera. It is a defining moment when one has arrived at perfect clarity, unity of purpose, and symmetry as Beethoven did when he wrote his only opera.

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.

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.

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.

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