OLEDB Issue

[faheemsd]

Hi ,

I'm using the SQL query in OLEDB to get the Excel data but when I read the excel data in Blue prism I'm getting the difference in Date and Time values and I'm not getting the exact Excel Data in the Blue Prism Collection

Please find the below excel screenshot and Blue prism collection Screenshot

Could you please provide the exact solution for this ?

Thank you.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

[PvD_SE]

Hi Syed,

That looks strange indeed!

Some questions:
- What does the SQL you use to extract the XL data look like? 
- What data type are the three columns in XL?
- Did you predefine the collection in BP? If so: what does it look like?

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

[faheemsd]

Hello Paul,

Thanks for your response..
 What does the SQL you use to extract the XL data look like?  - it's simple like Select * from [Sheet1$]
What data type are the three columns in XL? - 

Transaction Date

Transaction Time

Settlement Date

the above columns are generated by default from the other source(other team sharing this excel to us via email) and send to us for the validations.
Did you predefine the collection in BP? If so: what does it look like? - No, Collection in Bp is not predefined.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

[SahilChankotra]

Hi Syed, 
we can change the excel column data type first and then we can use OLEDB to read the data.
To get the exact Excel data in the Blue Prism collection, you may need to ensure that the data types and formats of the columns in the Excel sheet match the data types and formats of the columns in the Blue Prism collection. You may also need to adjust any formatting or conversion settings in the OLEDB query or Blue Prism to ensure that the data is transferred accurately.

------------------------------
Sahil Chankotra
------------------------------

[PvD_SE]

Hi Syed,

The problem seems to be that when your XL dates and time arrive in your collection, they have been converted to data type DateTime. This is probably a result of XL being stubborn - as is usual the case. 

Time for an experiment:
Experiment 1:
- Change the data type of the dates and time in your XL to Text
- Format the values of the dates and time in the cells so they look like dates and time, but still are data type Text (eg write 3/29/2023 as '3/29/2023)
- Run the SQL again
- Check the results in the collection
If this works better, you might want to test again with adjusting the SQL to force dates to be interpreted as dates and times as times. For that, you cannot do a 'select * ...' but have to specify each column and assign the data type of each column so that they will land with a correct data type in your collection.

If the above went nowhere, or is too much work, try experiment 2.
Experiment 2:
- Convert the (original) XL to a CSV
- Run the SQL again
- Check the results in the collection
If this is better, you may want to consider changing the XL to CSV in the process or having the CSV delivered instead of the XL.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

[faheemsd]

Hi Paul,

Just to give some background about the input file...we are actually receiving the input file in the CSV format and converting it to xlsx to work with OLEDB because I'm not sure about the connection string to with CSV file for OLEDB.

Could you please share the connection string for the CSV files to work with OLEDB and also please mention any dependencies that need to be installed in the server to work with the CSV files with the help of OLEDB.

Appreciate your support on this.

Thank you.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

[Mukeshh_k]

Hi SYED FAHEEM- it should be pretty straight forward if you are facing difficulties with how data is being read or stored in your collection, below are the options for CSV and MS Excel and I am not sure if CSV files are being supported by OLEDB provider.

CSV File:

First Option: to read CSV files in Blue Prism is to use the "Get CSV  as Collection" action from the "File Management" object. This action will allow you read all the CSV Data and later on you remove empty rows from collection if there are any by manipulating the collection data.

MS Excel File:
First option: would be to use a Pre-defined collection with all field kept as Text as it follows basic stuff which is If the collection field is defined as a text field, then the data returned by OLEDB will be stored in the collection field as text. The format of the text will depend on the data type of the corresponding column in the result set
If you dont wish to proceed with first option of defining a collection with predefined column format, use can use below method.
 
Second option :
Just add an action in one of your MS Excel extended VBO to format columns as Text - for your incoming Excel workbook sheet.

This Column Text formatting will ensure that all the data from collection are pasted in its in Simple Text without any auto formatting. 

1) Open MS Excel Extended VBO - Add an Action - Format to Text

Add Handle, Workbook Name, SheetName, Cell Range as input and add below code to code stage as shown in the image
Note : Cell Range for your case should be - "A:E" or "A:F" depending on how many more columns you have in the excel sheet (You can see snippets below I have passed "AI:AI" as I needed one column to be formatted as text - if not sure of range then simply pass "A:Z" for the entire sheet  and save workbook post formatting it as per defined range.

Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)

ws.Range(CellRange).NumberFormat = "@"

Once you have formatted the Columns - Continue with your OLEDB Operations and Data will be exactly how it is on the Excel sheet, let me know if you find any difficulties implementing this. 

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------

[RamónRequena_L1]

If you are receiving the Input file as CSV, you can also query this directly with OLEDB. I don't recall the exact connection string, but something like this:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & [in_CSVPath] & "; Extended Properties=""text;HDR=NO;FORMAT=Delimited"""

In case it gives you any issues, try to play with the quation marks. The key function is adding 'Format= Delimited" to your connection string.

------------------------------
Ramón Requena López
RPA Developer
Magenta Telekom
------------------------------

[michaeloneil]

Hi @SYED FAHEEM 

You can alter your script to get the values as date for the specified column e.g. SELECT TO_DATE (TRANSACTION DATE, 'DD.MON.YYYY') FROM [SHEET1$]
You might need to play around with this to get it working with the excel file but alternatively if the original file is csv but you are converting to xlsx just so you can use oledb to get the data then you could possibly just use Utility - Strings with action Get CSV as collection. It would simplify the process by removing unnecessary file conversion actions remove the need to use the oledb action.

Hope this helps 🙂

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[Babjee24]

Hi Syed,

You can also use conversions available in BP for formatting at the run time. The difference in time is because BP will convert to UTC thinking the data source is in local timing, if you add/subtract(depending on the time zone data coming from) the UTC timing difference it should be giving you the correct value.

Hoping this will be giving you a solution without any formatting changes in your input.

------------------------------
Babjee Vangipurapu
Senior RPA Developer
Wonderbotz
India
------------------------------