michael_hoogen
Occasional contributor

Business adapter custom queries issue.

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?

0 Kudos
5 Replies
michael_hoogen
Occasional contributor

Re: Business adapter custom queries issue.

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

Re: Business adapter custom queries issue.

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."
0 Kudos
winvarma
Analyst

Re: Business adapter custom queries issue.

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,

0 Kudos
mfranz
Shining star

Re: Business adapter custom queries issue.

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

Softline Group is Europe's leading independent expert in Software Asset Management.