Support Portal

for ProcessRobot and WinAutomation

Start a new topic

Execute SQL queries to Microsoft Excel

ADMIN

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;
waj
xlsx
(9.35 KB)

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;


Any ideas?

ADMIN

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.

Login or Signup to post a comment