Import Excel in DataTable

[CamillaCattivel]

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.

[AnuraagPandey · Accepted solution]

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

[CamillaCattivel]

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.

[AnuraagPandey]

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

[CamillaCattivel]

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.​

[AnuraagPandey]

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

[CamillaCattivel]

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

[AnuraagPandey]

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

Regards,
Anuraag