topic Re: Import Excel in DataTable in Product Forum

Import Excel in DataTable

CamillaCattivel — Wed, 13 Jan 2021 13:59:09 GMT

Hello friends,

I've created a datatable in my SQL Server.

I would like to connect to SQL Server and import an Excel file into the DataTable.
How to do that?
CAn you please provide me the VBO Data -SQL Server?

I don't have it?

Thank you so much,
CAmilla.

Re: Import Excel in DataTable

AnuraagPandey — Wed, 13 Jan 2021 15:55:41 GMT

Hello Camilla,

As per my understanding.
You are trying to load excel table into database. In that case excel provides an opportunity for us to connect excel and SQL server (refer screenshot).

Further reading: https://www.mssqltips.com/sqlservertip/1430/import-excel-data-into-sql-server-using-copy-and-paste/

And if you are trying to connect a database table with Blue Prism, then there exists a VBO - "BPA Object - Data - SQL Server".

You can find the same inside the folder : "VBO" inside the "Blue Prism Automate Folder".
It can very easily be imported this VBO into Blue Prism.

Using the action "Set connection" -- requires relevant fields as input (Server instance name, name of the database, userID, password) to complete the connection.

Regards,
Anuraag

Re: Import Excel in DataTable

CamillaCattivel — Wed, 13 Jan 2021 16:17:30 GMT

Thank you so much @AnuraagPandey.
i've imported the BPA Object - Data - SQL Server.

in the following fields:
Server instance name
database,
userID,
password

I've used
Server name from SQL Server Managment Studio, Database Name created, User with domain, Password
It doesn't work.

CAn you please hekp me?
CAmilla.

Re: Import Excel in DataTable

AnuraagPandey — Thu, 14 Jan 2021 08:44:03 GMT

Hello Camilla,

The image is not very clear.
But, there are few things you can check from your side.

1. Is the SQL Server already configured? You can search for "SQL Server Configuration Manager" in your system to verify this.

2. If point 1 is a yes, then is your Use case is testing / research purpose? If you are using the Express edition of the SQL Server, in that case you probably do not have
a additional security set.
Further reading: https://serverspace.io/support/help/install-and-configure-ms-sql-server-express/

3. Is Blue Prism already up and running, with a configured database? if yes.
Example
Business Object:: Data SQL Server
Action:: Set connection

Inputs:
ServerName:: "localhost/SQLExpress"
Database:: "<database name>" (As configured previously)
User:: <credentials same as SQL Server>
Password:: <credentials same as SQL Server>

You can also share the error message if you see one.

Regards,
Anuraag

Re: Import Excel in DataTable

CamillaCattivel — Thu, 14 Jan 2021 13:29:30 GMT

Thank you so much @AnuraagPandey.
I've configured the SQl Server wih SQL identification and it seems working.

Now I ask you kindly another question:
I need to Read an Excel File , Read the Collection of rows associated to it and insert the rows (without duplicates) in a Datatable inside the Database.
How can I do the insertion of rows inside the Datatable?

The collection from the excel has this columns:
Tipo fattura,
Tipo documento
Numero fattura
Data emissione

while the Datatable has these:
Tipo fattura,
Tipo documento
Numero fattura
Data emissione
PIVA Società?

CAn you help me please?
Thank you so much
CAmilla.

Re: Import Excel in DataTable

AnuraagPandey — Tue, 19 Jan 2021 19:34:18 GMT

Hello Camilla,

There can be several ways to approach this.
My suggestion would be.
Manually create a new database table to match your requirement, primary key defined, and datatype + plus assigned to each of the fields.

1. In page 1 - fetch values from excel and extract all information inside a collection stage
2. In page 2 -
Step A: START STAGE : here you can fetch the collection from previous page (this is a design choice, you can transfer data from one page to another the way you prefer)
Step B: ACTION STAGE : Data SQL server - set connection; here you can set connection with your database
Step C: ACTION STAGE : Data SQL server - Execute,
INPUT : here you can use an INSERT statement, the table (previously created) along with the fields (values will be fetched in a loop from the collection and written inside data items which will then be picked by the query)
Example:

"INSERT INTO <TABLE NAME> VALUES ('"&[itemID]&"', '"&[UserName]&"')"

OUTPUT: here you can fetch success message, just to be sure that the process worked, or capture the error.

Please let me know if this works.

Regards,
Anuraag

Re: Import Excel in DataTable

CamillaCattivel — Wed, 20 Jan 2021 08:09:52 GMT

Thanks a lot @Anuraag Pandey.
Now It seems working.
In case of problems can I write to you?
cami

Re: Import Excel in DataTable

AnuraagPandey — Wed, 20 Jan 2021 09:49:30 GMT

HI Camilla,
I am glad it worked 🙂
I am happy to help

Regards,
Anuraag