Use Excel data in query

You can use information already in your Excel workbook in a server query, by referencing the cell range from your SQL code by using a "#_R_" prefix followed by a sheetname and a starting cell.

Example with #_R_ prefix

It is possible to reference a range of data i Excel, when you contruct the SQL query. Use the "#_R_" prefix, the sheetname, and the cell name for first data (excluding the header). The SQLinExcel functionality will make a qualified guess as to the size of the data range and the data types. If other names or data types are required, there are several options to change them.

In the following example we already have a small set of data in Excel, that we want to use in a query. By typing #_R_RandomTable_A2 in the SQL statement, the data is transferred to the server and saved in the temporary table. In the example below we perform a cross join, that gives us 10 x 10 = 100 rows in total. To see the result, we insert the data in a sheet named CrossJoin (by typing "into #_T_CrossJoin" is the relevant select statement).

Options available

Setting the column name

When the "#_R_" prefix is used SQLinExcel will check if the datatypes in the columns are text or numbers. By default the columns are named corresponding to the cell content just above the first data cell. If that is not an appropriate name, it is possible to provide other names by expanding the "Read from sheet #_R_" settings in Excel.

The reference to the data set needs to be set correctly (in this case "RandomTable_A2") and the column names can be provided in the cell below, separated by commas. Using the above setting force the column names  

 

Setting the data type

If the data type used by SQLinExcel is inadequate there are two options to set the correct data type. The first is to insert a valid data type just after the column name in the sheet where the data is located. The second option is to insert a data type in the table specification just after the name.

Both approaches will give the same result. Any table specification provided (second option) will overrule other names and types specified.

 

Reference to a fixed range 

The #_R_ prefix also provides the option to refer to a fixed range, by providing the first as well as the last cell in the range to be read. This is done by typing for example #_R_RandomTable_A2_B5. Only data in the range between A2 and B5 will be sent to the SQL server.