SQL Server VBO

[AartiTyagi]

Hi All, I have a requirement to perform a join query on two excels and get the data in a collection. How can i make a connection to the excel sheets using Data-SQL Server VBO?Also, is there any particular syntax for writing query in SQL server VBO? Thanks in advance. Aarti  

[AmiBarrett]

Just curious, is the data you're placing into SQL only temporary in order to facilitate the join, or does it need to persist afterwards? If the former, you could load both spreadsheets into separate collections and use Linq to join the data. Otherwise, you're looking for something like what's in the file I'm attaching (Forum blocked posting directly due to attempted injection). We have this as part of a stored procedure, where @File_ID is a GUID generated on file upload by our webserver.

[AmiBarrett]

*Sigh* Still getting blocked. Let's try to attach again.

[AmiBarrett]

Let's try an alternate approach. SQL ACE OLEDB connection strings: https://www.connectionstrings.com/ace-oledb-12-0/ Additional documentation: https://www.sqlshack.com/query-excel-data-using-sql-server-linked-servers/

[AmiBarrett]

(Sorry for the post spam) Got the file I attempted to attach into a pastebin: https://pastebin.com/FJKAzZYN

[AartiTyagi]

Hi amibarrett, Thanks for your response.  Answer to your question is , Yes i want the data in the sheets to persists afterwards also.I have gone through the links shared by you ,but things are not pretty much clear to me .  Below is the in detail explanation of my requirement: I am downloading the excel files from a browser based application and the number of records in this excel file is dynamic. I need to apply some vlookups in this sheets which refers another excel sheet.But Vlookup is taking large time to complete so i thought of using join query to fetch the data. In order to execute the join query i tried using OLEDB VBO , but no luck as it is not able to connect to two excels at the same time. Another approach i thought was connecting the excel sheet through SQL Server VBO. So i am looking for how to extract the data from excel sheets into database using bluprism. NOTE: These excel sheets have a large volume of data , nearly 1 Lakh of records. Regards, Aarti Tyagi  

[AmiBarrett]

Ultimately, your best bet is to leverage a stored procedure. If the files are at a path reachable from the SQL DB, you can feed that path in as a variable to said procedure, then use code similar to the pastebin to import the data.

[AartiTyagi]

Thanks much for your inputs amibarret. Will try this approach and let you know if it works :) Regards, Aarti