Excel OLEDB connection, number format

[Jacopo]

Hi,
We are developing a robot to automate some reporting analysis and submission to different process owner.
Basically we extract data from SAP, make some mapping query in access and then extract data into excel in different report to different user.

To write in excel we are using connection string: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[path]&";Extended Properties=""Excel 12.0;HDR=YES;""" as suggested from blue prism in order to write large amount of data in excel.
Than we write a query to insert data in excel like a database. (see below the SQL code).

The issue is: we should format in some way some fields as number in order to feed some report.

We know that we can interact with excel modifying the format but we would try a fashion way maybe applying the format directly in the sql code below.
We tried different things without any solution…

Any help suggestion? Anyone already face this issue?
Thanks a lot

"INSERT INTO [input$] (column1, column2, column3, column4, column5) VALUES ('"&[collectionfield1]&"','"&[collectionfield2]&"','"&[collectionfield13]&"','"&[collectionfield4]&"','"&[collectionfield5]&"');"

 

[Anubhav247]

Hi,
We are developing a robot to automate some reporting analysis and submission to different process owner.
Basically we extract data from SAP, make some mapping query in access and then extract data into excel in different report to different user.

To write in excel we are using connection string: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[path]&";Extended Properties=""Excel 12.0;HDR=YES;""" as suggested from blue prism in order to write large amount of data in excel.
Than we write a query to insert data in excel like a database. (see below the SQL code).

The issue is: we should format in some way some fields as number in order to feed some report.

We know that we can interact with excel modifying the format but we would try a fashion way maybe applying the format directly in the sql code below.
We tried different things without any solution…

Any help suggestion? Anyone already face this issue?
Thanks a lot

"INSERT INTO [input$] (column1, column2, column3, column4, column5) VALUES ('"&[collectionfield1]&"','"&[collectionfield2]&"','"&[collectionfield13]&"','"&[collectionfield4]&"','"&[collectionfield5]&"');"

I assume the very first time when you execute your insert query in your process you have an empty excel worksheet.

If that is the case, then you can execute below query before you execute your insert query.

Create Table [input$] (column1 int,column2 int,column3 int,column4 int,column5 int)

This will set the data type to int in your excel worksheet.

Hope it helps!!