Business adapter custom queries issue.

[michael_hoogen]

If I create a business adapter with a custom query for the purpose of importing data from an excel spreadsheet into a table in the fnmsstaging database, in the custom query how do I select data from the spreadsheet? I've tried select statements but they always return "Invalid object name 'users$'." ('users' being the name of the spreadsheet tab) Does anyone have any example code showing how to do this?

[michael_hoogen]

If I remove the excel tab and just put the column names I get "Invalid column name 'User Name'.
Invalid column name 'Team Products'."

[michael_hoogen]

if I do a select like so:
select complianceuserid from FNMSCompliance.dbo.ComplianceUser_MT where username = [users$].[User Name]
I get "The multi-part identifier "users$.User Name" could not be bound."

[winvarma]

Hi @michael_hoogen ,

issue might be with the format of the cells in the excel and i would suggest you to try to import the data from a .CSV file instead of Excel as i have observed sometimes that even though the column name doesn't contain any special characters it shows so when we are using excel in Business adapter.

Regards,

[mfranz]

Hi,

Accessing the Excel data is imho no real SQL. I usually first stage the data from any type of files into the FNMSCompliance database and then run proper SQL from there.

Best regards,

Markward

[ChrisG]

Here's an example of what a query would typically look like to extract data from all columns in the worksheet named "Users" in an Excel spreadsheet:

SELECT * FROM [Users$]