How to Create an MS Query From Excel Tables

Query your Excel spreadsheets.

The Microsoft structured query language, or SQL, provides you with a native feature to retrieve data from an Excel spreadsheet. You can use this query syntax to select, update, insert or delete records in your Excel spreadsheet. Microsoft's SQL language is used when you want to share data across platforms. These queries are used in Excel, Access, SQL Server and ASP Web applications.

Instructions

1. Write your basic, linked query. Decide if you want to update, delete, insert or select the Excel records. The following code creates a basic query:

   select * from excel_link...[spreadsheet$]

   This code selects records from an Excel spreadsheet called "spreadsheet$." The dollar sign suffix is standard when querying Excel spreadsheets.

2. Create your Excel link. This link uses the Excel driver to connect to the sheet and return results in your Microsoft application. The following code queries a spreadsheet with the Excel driver:

   select * from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\excel.xls;Extended Properties=Excel 8.0').[spreadsheet$]

   The above code queries Excel spreadsheets from the file "c:\excel.xls."

3. Import Excel data to another table. This is beneficial for webmasters or database administrators who need to import data from the Excel file to another application such as SQL Server or Access. The following code imports the selected data to a table named "Excel_Import":

   select * into Excel_Import from opendatasource('Microsoft.Jet.OLEDB.4.0',
   'Data Source=C:\excel.xls;Extended Properties=Excel 8.0').[spreadsheet$]

References

• Microsoft: Support: How to Import Data from Excel to SQL Server