{"id":14041,"date":"2011-05-04T00:01:00","date_gmt":"2011-05-04T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/05\/04\/use-ace-drivers-and-powershell-to-talk-to-access-and-excel\/"},"modified":"2011-05-04T00:01:00","modified_gmt":"2011-05-04T00:01:00","slug":"use-ace-drivers-and-powershell-to-talk-to-access-and-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-ace-drivers-and-powershell-to-talk-to-access-and-excel\/","title":{"rendered":"Use ACE Drivers and PowerShell to Talk to Access and Excel"},"content":{"rendered":"

Summary<\/b>: Quit using deprecated JET drivers to talk to Microsoft Access and Excel. Learn how to use Windows PowerShell and ACE Drivers.<\/p>\n

\"Hey,  Hey, Scripting Guy! How do I get data out of Microsoft Access and Excel files and into SQL Server by using Windows PowerShell?<\/p>\n

—MC<\/p>\n

\"Hey, Hello MC, Microsoft Scripting Guy, Ed Wilson, is here. We are at the center of SQL Week in honor of SQLRally<\/a> next week, and our guest blogger today is Chad Miller. <\/p>\n

Chad Miller (Blog<\/a>|Twitter<\/a>) is a SQL Server DBA and the senior manager of database administration at Raymond James Financial. In his spare time, he is the project coordinator and developer of the CodePlex project SQL Server PowerShell Extensions (SQLPSX<\/a>). Chad leads the Tampa Powershell User Group<\/a> and is a frequent speaker at IT Camps, SQL Saturdays, and Code Camps.<\/p>\n

Microsoft Access and Excel have been called the desktop database, and companies have a lot of data stored in them. It is inventible that you will need to load some of these files into a SQL Server database. <\/p>\n

Note<\/b>: There are a variety of tools provided by Microsoft to assist in migrating your data into a SQL Server database. In addition Windows Azure, Microsoft Access Services, and Microsoft Excel Services for SharePoint provide alternative solutions, which should be considered as possible strategies. Other solutions may be better suited than the Windows PowerShell scripts demonstrated in this blog post.<\/p>\n

Connectivity<\/b><\/h3>\n

Connectivity to a data source is accomplished through ODBC drivers or OLE DB Providers. When we connect to a data source, we first need to determine which connectivity components to use. A Windows operating system includes the ODBC driver and OLE DB Providers for connecting to a variety of data sources, including SQL Server. However, newer Microsoft Office connectivity components are not included with the operating system. Instead, Microsoft Office connectivity components are installed with Microsoft Office or alternatively through something called the Access Control Entry (ACE) driver. <\/p>\n

One of the cool things about ACE is first, its name. But more importantly, ACE is completely free, and it even includes a 64-bit version. <\/p>\n

Important<\/b>: ensure that you have the ACE driver\/provider installed on your machine or else the Windows PowerShell scripts demonstrated in this post will not work! Before you proceed, go to Microsoft Access Database Engine 2010 Redistributable<\/a>, and download AccessDatabaseEngine.exe or AccessDatabaseEngine_x64.exe, depending on your operating system. The ACE drivers are supported by Windows 7; Windows Server 2003 R2, 32-bit x86; Windows Server 2003 R2, x64 editions; Windows Server 2008 R2; Windows Server 2008 with Service Pack 2; Windows Vista with Service Pack 1; and Windows XP with Service Pack 3.<\/p>\n

When you you have ACE drivers, there is no reason to use the old deprecated JET drivers—even for older versions of Microsoft Access and Excel. A common mistake I see, even with seasoned developers, is to drop to JET for .mdb and .xls files when you don’t need to. I have made this mistake myself past.<\/p>\n

I found a helpful blog post on MSDN<\/a> from the CSS SQL Server Engineers that talks about different data providers and discusses a migration strategy. <\/p>\n

Installing the ACE Module<\/b><\/h3>\n

Having addressed the issue of connectivity, we can now look at a simple Windows PowerShell module that provides a function over the ACE driver, which I call ACE. <\/p>\n

To install the ACE module, perform the following steps. <\/p>\n

    \n
  1. Create a folder named ACE under Documents\\WindowsPowerShell\\Modules.<\/li>\n
  2. Save the ACE script from the Scripting Guys Script Repository<\/a> as ACE.psm1 in your Documents\\WindowsPowerShell\\Modules\\ACE folder. This is shown in the following image. <\/li>\n<\/ol>\n

    \"Image<\/a> <\/span><\/p>\n

    To use the module, you need to import the module into your current Windows PowerShell session. This is accomplished by running the following command.<\/p>\n

    \n

    Import-module ace<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    The ace module consists of one exported function called Get-ACEData<\/b>. To see Help including examples of usage, run the following command.<\/p>\n

    \n

    Help Get-ACEData –full<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    Using the ACE Module<\/b><\/h3>\n

    Now that the ACE module is installed and ready to use, let us look at couple of examples.<\/p>\n

    Getting Excel Data<\/b><\/h4>\n

    I am going to use an Excel file called backup.xlsx as shown here.<\/p>\n

    \"Image<\/a><\/p>\n

    First, let us list the worksheets that are part of the backup.xlsx spreadsheet.<\/p>\n

    \n

    PS C:\\Users\\u00\\bin> Get-ACEData -FilePath .\\backupset.xlsx -TableListOnly<\/span><\/span><\/span><\/p>\n

    TABLE_CATALOG : <\/span><\/span><\/span><\/p>\n

    TABLE_SCHEMA  <\/span>: <\/span><\/span><\/span><\/p>\n

    TABLE_NAME    <\/span>: Sheet1$<\/span><\/span><\/span><\/p>\n

    TABLE_TYPE    <\/span>: TABLE<\/span><\/span><\/span><\/p>\n

    TABLE_GUID    <\/span>: <\/span><\/span><\/span><\/p>\n

    DESCRIPTION   <\/span>: <\/span><\/span><\/span><\/p>\n

    TABLE_PROPID  <\/span>: <\/span><\/span><\/span><\/p>\n

    DATE_CREATED  <\/span>: 4\/19\/2011 9:41:11 PM<\/span><\/span><\/span><\/p>\n

    DATE_MODIFIED : 4\/19\/2011 9:41:11 PM<\/span><\/span><\/span><\/p>\n

    TABLE_CATALOG : <\/span><\/span><\/span><\/p>\n

    TABLE_SCHEMA  <\/span>: <\/span><\/span><\/span><\/p>\n

    TABLE_NAME    <\/span>: Sheet2$<\/span><\/span><\/span><\/p>\n

    TABLE_TYPE    <\/span>: TABLE<\/span><\/span><\/span><\/p>\n

    TABLE_GUID    <\/span>: <\/span><\/span><\/span><\/p>\n

    DESCRIPTION   <\/span>: <\/span><\/span><\/span><\/p>\n

    TABLE_PROPID  <\/span>: <\/span><\/span><\/span><\/p>\n

    DATE_CREATED  <\/span>: 4\/19\/2011 9:41:11 PM<\/span><\/span><\/span><\/p>\n

    DATE_MODIFIED : 4\/19\/2011 9:41:11 PM<\/span><\/span><\/span><\/p>\n

    TABLE_CATALOG : <\/span><\/span><\/span><\/p>\n

    TABLE_SCHEMA  <\/span>: <\/span><\/span><\/span><\/p>\n

    TABLE_NAME    <\/span>: Sheet3$<\/span><\/span><\/span><\/p>\n

    …<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    To shorten the output, I will pipe the results to Select-Object<\/b> and specify the TABLE_NAME property only.<\/p>\n

    \n

    PS C:\\Users\\u00\\bin> Get-ACEData -FilePath .\\backupset.xlsx -TableListOnly | Select-Object TABLE_NAME<\/span><\/span><\/span><\/p>\n

    TABLE_NAME<\/span>                                                                                                                                        <\/span><\/span><\/span><\/span><\/span><\/p>\n

    ———-                               <\/span><\/span>                                                                                                         <\/span><\/span><\/span><\/span><\/span><\/p>\n

    Sheet1$<\/span>                                                                                                                                           <\/span><\/span><\/span><\/span><\/span><\/p>\n

    Sheet2$<\/span>                                                                                                                                           <\/span><\/span><\/span><\/span><\/span><\/p>\n

    Sheet3$ <\/span><\/span><\/span>   <\/span><\/span><\/span><\/span><\/span><\/p><\/blockquote>\n

    We can see that the three worksheets that make up the spreadsheet are returned. Next, let us return the data. To return all data from every worksheet, run the following command.<\/p>\n

    \n

    $ds= Get-ACEData -FilePath .\\backupset.xlsx<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    If we look at the $ds<\/b> variable through Get-Member<\/b>, we see that the function returns the DataSet shown here.<\/p>\n

    \"Image<\/a><\/p>\n

    This DataSet is made up of a collection of DataTables. To see how many DataTables, run the following command.<\/p>\n

    \n

    $ds.Tables.count<\/span><\/span><\/span><\/p>\n

    3<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    To see the DataTable names, run the following command. <\/p>\n

    \n

    $ds.Tables | Select TableName<\/span><\/span><\/span><\/p>\n

    TableName<\/span><\/span><\/span><\/p>\n

    ———<\/span><\/span><\/span><\/p>\n

    Sheet1$<\/span><\/span><\/span><\/p>\n

    Sheet2$<\/span><\/span><\/span><\/p>\n

    Sheet3$<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    If we want look at the data, we would run:<\/p>\n

    \n

    $ds.Tables<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    To see only the first DataTable, specify the collection index or the name of the DataTable, for example:<\/p>\n

    \n

    $ds.Tables[0] or $ds.Tables[“Sheet1$”]<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    If you want to return only the first and second worksheets, we can specify either the Table<\/b> or the Query<\/b> parameter as shown here.<\/p>\n

    \n

    $ds= Get-ACEData -FilePath .\\backupset.xlsx -Table ‘Sheet1$’,’Sheet2$’<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    To restrict the rows that are returned from a worksheet, use the Query<\/b> parameter as shown here.<\/p>\n

    \n

    $ds = Get-ACEData -FilePath .\\backupset.xlsx -Query “Select * FROM [Sheet1$] WHERE [dbname] = ‘db1′”<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    The ACE driver supports .xls (Excel 2003) and . xlsx (Excel 2007 and higher) files. <\/p>\n

    Next, we will query a Microsoft Access file.<\/p>\n

    Getting Access Data<\/b><\/h3>\n

    I am going to use the sample Northwind Microsoft Access database, which is available from File>>New>>Sample Templates as shown in the following image.<\/p>\n

    \"Image<\/a><\/p>\n

    Just as I did with the Excel file, we will get a list of tables. However, Microsoft Access has different table types, including views and system tables, which we’ll need to filter out and load into a variable as shown here.<\/p>\n

    \n

    $tables = Get-ACEData -FilePath .\\Northwind.accdb -TableListOnly | where {$_.TABLE_TYPE -eq ‘TABLE’ } | Select -ExpandProperty TABLE_NAME<\/span><\/span><\/span><\/p>\n

    $tables<\/span><\/span><\/span><\/p>\n

    Customers<\/span><\/span><\/span><\/p>\n

    Employee Privileges<\/span><\/span><\/span><\/p>\n

    Employees<\/span><\/span><\/span><\/p>\n

    Inventory Transaction Types<\/span><\/span><\/span><\/p>\n

    Inventory Transactions<\/span><\/span><\/span><\/p>\n

    Invoices<\/span><\/span><\/span><\/p>\n

    Order Details<\/span><\/span><\/span><\/p>\n

    Order Details Status<\/span><\/span><\/span><\/p>\n

    Orders<\/span><\/span><\/span><\/p>\n

    Orders Status<\/span><\/span><\/span><\/p>\n

    Orders Tax Status<\/span><\/span><\/span><\/p>\n

    Privileges<\/span><\/span><\/span><\/p>\n

    Products<\/span><\/span><\/span><\/p>\n

    Purchase Order Details<\/span><\/span><\/span><\/p>\n

    Purchase Order Status<\/span><\/span><\/span><\/p>\n

    Purchase Orders<\/span><\/span><\/span><\/p>\n

    Sales Reports<\/span><\/span><\/span><\/p>\n

    Shippers<\/span><\/span><\/span><\/p>\n

    Strings<\/span><\/span><\/span><\/p>\n

    Suppliers<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    To load all of the regular tables contained in the Northwind Microsoft Access file, run the following command.<\/p>\n

    \n

    $ds = Get-ACEData -FilePath .\\Northwind.accdb -Table $tables<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

    To see the number of tables in the DataSet, run:<\/p>\n

    \n

    $ds.Tables.Count<\/span><\/span><\/span><\/p>\n

    20<\/span><\/span><\/span><\/p><\/blockquote>\n

    Like in the Excel example, we can specify queries by using the Query<\/b> parameter or specific tables with the Table<\/b> parameter. The ACE Driver supports .mdb (Access 2003) and .accdb (Access 2007 or higher) formats.<\/p>\n

    Importing Microsoft Office Data<\/b><\/h3>\n

    At this point, we have retrieved data from Excel and Access files. Next we will look at loading this data into a SQL Server database. I am going to use the free, SQL Server Express version. If you do not have a SQL Server instance available, you can download and install SQL Server Express<\/a>. I am also going create a database called hsgb<\/b> in SQL Server Management Studio as shown in the following image.<\/p>\n

    \"Image<\/a><\/p>\n

    Setup<\/b><\/h3>\n

    In a previous Hey! Scripting Guy blog titled Use PowerShell to Collect Server Data and Write to SQL<\/a>, I demonstrated three functions that I will use to load and query the data in SQL Server. Download each script from the following locations:<\/p>\n