How to import datatable into Excel without Loop using VB

▲
0
▼

I am trying to export a SQL table result into Excel using blue prism. Currently it is done by exporting SQL results to collection and then collection to Excel. This is taking a longer time (10 mins) to export ~20K records. This is because the VBO uses for each loop.

I am trying to use some customized VB code to load the collection into excel using bulk update.

Can anyone help me on this?

I tried the below code but its not working

' Get to the cell
Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)
Dim origin As Object = ws.Range(cellref, cellref)
Dim cell As Object = origin
Dim colInd As Integer = 0, rowCount As Integer, rowInd As Integer = 0 ' Offsets from the origin cell

' Deal with the column names first
If includecolnames Then
    For Each col As DataColumn In Collection.Columns
        Try
            cell = origin.Offset(rowInd, colInd)
        Catch ex As Exception ' Hit the edge.
            Exit For
        End Try
        SetProperty(cell, "Value", col.ColumnName)
        colInd += 1
    Next
    rowInd += 1
End If

rowCount = Collection.Rows.Count
xlRange = ws.Range(cellref & ":H" & rowCount)
xlRange.Value = Collection

Error that I am getting is: Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

vb.net
datatable
blueprism
asked on Stack Overflow Oct 17, 2019 by Divakar Ragupathy

3 Answers

▲
2
▼

I think that you're looking for this function of WorkSheet:

ws.SetRangeValues(RowIndex, ColumnIndex, Array(, ))

Just build two-dimensional array in memory based on your SQL data and call this once. It will fill the data from selected RowIndex and ColumnIndex all at once much faster then iterative way.

answered on Stack Overflow Oct 17, 2019 by PavlinII
▲
0
▼

the way youre approaching this seems not very efficient, grabbing results from one place to put into another and then into another. here are two other alternatives you can try?

Automate command through CMD

sqlcmd -S . -d AzureDemo50 -E -s, -W -Q "SELECT * FROM dbo04.ExcelTest" > ExcelTest.csv

something like this more info (https://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm)

here you can run a command through cmd line with blue prism and then push the value where you want it to go without having to put the memory in blue prism at all.

Use a db object like OLEDB to parse a command straight into the DB to push the values into excel.

in both of these options the data never gets into Blueprism so of note, you dont get the opportunity to save, alter etc on the data in blueprism however it is much faster to operate.

test: made a db with 20k rows 5 columns data export with above cmd took 1.7 seconds to execute pluse extra 1/2 seconds for blueprism running it so 3-5 seconds total with smaller memory allocation that your current implementation.

is this something like you were looking for?

answered on Stack Overflow Oct 19, 2019 by Dexter Whelan
▲
0
▼

Thanks for your answers. I have added a new page called Write Collection - Fast and added code stage in Blue Prism MS Excel VBO. Pasted the below code

Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)
Dim sqlCon As New ADODB.Connection
Dim recordSet As New ADODB.Recordset
Dim iCol As Integer

    sqlCon = New ADODB.Connection
    sqlCon.ConnectionString = "driver={SQL Server};server=xxx\SQLEXPRESS;uid=zzzz;pwd=yyyy;database=testData"
    sqlCon.ConnectionTimeout = 30
    sqlCon.Open


    recordSet.Open (SQL, sqlCon)

    For iCol = 0 To recordSet.Fields.Count - 1
        ws.Cells(1, iCol + 1).Value = recordSet.Fields(iCol).Name
    Next

    ws.Range("A2").CopyFromRecordset(recordSet)

    recordSet.Close
    sqlCon.Close
answered on Stack Overflow Oct 20, 2019 by Divakar Ragupathy

User contributions licensed under CC BY-SA 3.0