Hi, I leave a note for future references. This approach is incorrect due to a bug in the excel dates. Excel incorrectly calculates days, see here for more info: https://docs.microsoft.com/en-US/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year
Please keep in mind that playing with dates is always tricky, in this case you need to reduce the dates by 2 to make it work. The output should be correct unless you need a date between 1900-Jan-01 and 1900-Mar-01.
defVar --name dateNow --type DateTime
defVar --name excelDate_n --type Numeric
setVar --name "${dateNow}" --value "1900-01-01 00:00:00" --comment "Excel standard date"
setVar --name "${excelDate_n}" --value 44305
addToDateTime --date "${dateNow}" --value ${excelDate_n} --type "Days" dateNow=value
//minus 1, because it starts with day 1
addToDateTime --date "${dateNow}" --value -2 --type "Days" dateNow=value
logMessage --message "${dateNow}" --type "Info"
------------------------------
Michele Lobina
------------------------------
Original Message:
Sent: Mon April 19, 2021 03:33 PM
From: Angelo Alves
Subject: Database connection to Excel sheet
Hello, sorry for the delay
Can you send me an excel file with the sample, for me to test? (angelo.alves@ibm.com)
To solve this date problem, we can use the code down.
defVar --name dateNow --type DateTime
defVar --name excelDate_n --type Numeric
setVar --name "${dateNow}" --value "1900-01-01 00:00:00" --comment "Excel standard date"
setVar --name "${excelDate_n}" --value 44305
addToDateTime --date "${dateNow}" --value ${excelDate_n} --type "Days" dateNow=value
//minus 1, because it starts with day 1
addToDateTime --date "${dateNow}" --value -1 --type "Days" dateNow=value
logMessage --message "${dateNow}" --type "Info"
------------------------------
Angelo Alves
Original Message:
Sent: Wed April 07, 2021 03:26 PM
From: Vinicius Marques
Subject: Database connection to Excel sheet
Thank you for the alternative using ODBC, @Angelo Alves.
I'm running into an issue though and not sure how to solve it. Some values are missing from the columns because they hold mixed types. After a quite extensive research I found that using the MIX=1 should solve it by treating it all as text, but did not manage to make it work.
Is there any way to read the content of an Excel file considering it's value type as well? The biggest problem for me are the dates, which get converted to that internal number from Excel, which is the number of days passed since 1990.
------------------------------
Vinicius Marques
Original Message:
Sent: Tue February 09, 2021 07:57 AM
From: Angelo Alves
Subject: Database connection to Excel sheet
Hi,
Also, it is possible with IBM RPA;
[script]
defVar --name conExcel --type DbConnection
defVar --name table --type DataTable
odbcConnect --connectionstring "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};FIL=Excel 12.0;DriverID=1046;DBQ=C:\\Users\\AngeloSiqueiraAlves\\Desktop\\workbook.xlsx;HDR=no;Format=xlsx;;Readonly=False" conExcel=connection
sqlExecuteReader --connection ${conExcel} --statement "Select * from [Orders$] where item=2" --timeout 00:00:30 table=value
------------------------------
Angelo Alves
Original Message:
Sent: Fri February 05, 2021 07:36 AM
From: nordine vandezande
Subject: Database connection to Excel sheet
Hi
I need to update a cell in an Excel sheet. No problem with that when using excelOpen en excelSet but since I don't know the exact row, I need to iterate through the sheet and compare the value of a cell until I find the row. However, this seems to be rather slow.
Using findTableCell first speeds the process a little but still not satisfying.
With Automation Anywhere it is possible to manipulate an Excel sheet like a database, using a OleDB-driver but that seems to be missing in IBM RPA.
Is there an alternative?
------------------------------
nordine vandezande
------------------------------