Robotic Process Automation

Expand all | Collapse all

Database connection to Excel sheet

  • 1.  Database connection to Excel sheet

    Posted Fri February 05, 2021 07:37 AM
    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
    ------------------------------


  • 2.  RE: Database connection to Excel sheet

    Posted Tue February 09, 2021 07:57 AM
    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
    ------------------------------



  • 3.  RE: Database connection to Excel sheet

    Posted Tue February 09, 2021 03:16 PM
    Hi Angelo

    thanks for your reply.
    However, after replacing the filepath I cannot get the odbcConnect to work. I get the error:

    ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

    My command: odbcConnect --connectionstring "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};FIL=Excel 12.0;DriverID=1046;DBQ=C:\\Temp\\test.xlsx;HDR=no;Format=xlsx;;Readonly=False" conExcel=connection

    I verified that the ODBC-driver for Excel is installed:

    Any idea what is going wrong?





    ------------------------------
    nordine vandezande
    ------------------------------



  • 4.  RE: Database connection to Excel sheet

    Posted Tue February 09, 2021 03:23 PM
    Based on screen shots and context I think you are talking past one another. The original question was referencing Automation Anywhere, which was bundled with BAW for ~12-18 month. But IBM bought a company called WDG (I think) that has an RPA solution that has since been re-branded IBM RPA. I think the answer posted was for that production and not Automation Anywhere.

    ------------------------------
    Andrew Paier
    ------------------------------



  • 5.  RE: Database connection to Excel sheet

    Posted Tue February 09, 2021 03:43 PM

    Hi Andrew 

    I was referring to Automation Anywhere as an example where it was possible to use OleDB while this is not possible in IBM RPA (formerly know as WDG Automation). 

    Angelo is suggesting to use an ODBC driver instead which is helpful but I don't seem to get it working. 



    ------------------------------
    nordine vandezande
    ------------------------------



  • 6.  RE: Database connection to Excel sheet

    Posted Tue February 09, 2021 05:25 PM
    Hi Nordine!

    What version you are currently running?

    ------------------------------
    Jukka Juselius
    Senior Solution Architect - IBM EMEA
    WDG Automation Program Lead for EMEA
    IBM Technology - Business Automation
    ------------------------------



  • 7.  RE: Database connection to Excel sheet

    Posted Wed February 10, 2021 03:47 AM
    Hi Jukka

    I was using 20.12.0 and upgrade this morning (on my development workstation) to 20.12.3. Still the same error.

    ------------------------------
    nordine vandezande
    ------------------------------



  • 8.  RE: Database connection to Excel sheet

    Posted Wed February 10, 2021 08:57 AM
    I tested it on 3 machines and it worked. Without doing any extra configuration.

    IBM RPA 20.12.3 - SaaS
    Office 365 - Excel
    Windows 10

    You showed a screenshot in ODBC32bits, one possible cause is ODBC64bits is not configured, which is the same 64bits version of IBM RPA.

    ------------------------------
    Angelo Alves
    ------------------------------



  • 9.  RE: Database connection to Excel sheet

    Posted Fri February 19, 2021 08:13 AM
    Hi Angelo

    I can confirm that ODBC64bits solved the problem. Thanks!

    ------------------------------
    nordine vandezande
    ------------------------------



  • 10.  RE: Database connection to Excel sheet

    Posted Fri February 19, 2021 01:30 PM
    Great, thanks for the return.

    ------------------------------
    Angelo Alves
    ------------------------------



  • 11.  RE: Database connection to Excel sheet

    Posted 14 days ago
    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
    ------------------------------



  • 12.  RE: Database connection to Excel sheet

    Posted 2 days ago
    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
    ------------------------------