Trying to use an Excel data source for a SmartConnect 21 map, however the data does not start in row 1, the data actually starts in row three. When I attempt to create an Excel file data source, I select the sheet and attempt to preview and I receive an error that says “An error occurred retrieving the preview data: ColumnName is required when it is part of a data table.” Is there a way I can tell SmartConnect that the data does not start in row one?
You can actually create an ODBC connection to get the data from a specific selection of cells. My Excel file looks like this, the headers for the file do not start until row three, and the data actually starts on row 4.
If you’d rather watch a video on how this is done, click here.
Here is what the error looks like when I attempt to setup a standard Excel data source
In order to create a data source to read this file, you will need to use an ODBC connection instead of selecting Excel File source.
1. Make note of the sheet name where the data is contained and the range of cells where the data it stored. In my screenshot below, my sheet is named “Inventory List” and my header row starts in B3 and goes to L28
2. Create a new Data Source and pick “Bulk” as the type, and then select ” ODBC Query Data Source”.
3. Give the data source a name, and then for the instance select “File Data Source” and then click on the Connection string and pick the appropriate version of Excel and select the Excel file. In my screenshot I have named it “My new data source” and I have selected the Excel 2019 file format.
4. In the “ODBC Microsoft Excel Setup” window, click the “Select Workbook” button and browse to the location of the Excel file.
5. Hit OK on the 2 windows after you have selected the Excel file. Then you need to write an Excel ODBC query. In my case I want to select all the data from my “Inventory List” sheet and I want to select cells B3 to L28. The syntax of the query is “select * from [Sheet Name$Startcell:Endcell]. So in my example my ODBC query would look like this:
SELECT * from [Inventory List$B3:L28]
My screen looks like this after I type the select query
6. Preview the data to verify you are seeing the header row and the data rows only
7. Now you can use this data source in a SmartConnect map without issues. Create a new integration and pick “Bulk” as the data source type, and select the data source you just created and then select a key field. You will be able to use this data source in a SmartConnect map now.
