Execute SQL queries to Microsoft Excel
Excel files consist of one or more Worksheets. A Worksheet can be treated as an SQL Table.
Take a look at the following sample queries. The relevant Excel file is attached below.
- SELECT * FROM [Students$];
Students represents a Worksheet's Name within the selected file. It needs to be followed by the $ symbol and be included in square brackets.
- SELECT [Full Name] FROM [Students$] WHERE [Student ID] =1;
Our Columns, Full Name and Student ID, need to be included in square brackets.
- INSERT INTO [Books$] ([Book Name], [Book ID]) VALUES ('The Odyssey', 4);
Books represents the file's second Worksheet.
- UPDATE [Students$] SET [Full Name] = 'Leonardo N' WHERE [Student ID] = 3;
This is interesting, but I get an error when I try to list more than one column, but not use *. In my case I want to return 3 columns from an excel spreadsheet, and no matter what I get an "Error in SQL Statement: No value given for one or more required parameters."
The query I'm trying to run looks like this:
SELECT a.[Length], a.[Width]
FROM [PRODUCT DETAILS$A6:F100] AS a
WHERE [Customer Item] IS NOT NULL;
For the attached excel file, the syntax for the SQL Statement would be:
Select [Full Name] From [Students$] Where [Student ID] = 1;
This will retrieve the name of students with ID=1.