cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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?

(5) Replies
If I remove the excel tab and just put the column names I get "Invalid column name 'User Name'.
Invalid column name 'Team Products'."
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."

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
By Level 17 Champion
Level 17 Champion

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

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$]

 

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)