How to deal with huge Excel files?

Help Studio
1

Hello UiPathers,

Am dealing with 800k rows of excel data.
Firstly, after doing some research on how to deal with huge excel files.
I decided to use Excel as a DB but I failed with the query part and it was very slow I don’t why because I excepted it to be fast. Below is the link to the issue I got with:

Secondly, I approached the problem by converting the excel file into a csv and it was working fine for the small sample data but when I tested with the 800k rows data set it misbehave and it was giving output and it was very slow.
Yet I excepted it to be fast according what I saw in this the video below:

Lastly, I as well as used the read XL activities from the marketplace but same issue.

In Conclusion, how long should the excel automation of 800k rows take on average and what’s the best way to solution for it.

Thank you.

2

Hi @Kakooza-Allan-Klaus ,

Your best bet would be to use VBA to either perform the operations you want it to perform onto the dataset, or you can use VBA to split it into smaller Workbooks which can then be read without breaching any time out.

800k → 8 files of 100k rows each can be read using the Excel or Workbook Activities.

Kind Regards,
Ashwin A.K

1 Like
3

Hello @Kakooza-Allan-Klaus
Kindly refer to this thread, it may help you

1 Like
4

How can I implement it using VB.net ?

5

Hi @Kakooza-Allan-Klaus ,

Here is an example with VBA that you can use within an Invoke VBA Activity:

Function Split_Workbook(str_source As String, int_rowStep As Integer, str_folderPath As String)

Dim wb As Workbook
Dim ShSource As Worksheet, ShDest As Worksheet
Dim rowCount As Long, rowStep As Long, rowsToAdd As Long, i As Long
    
    Set ShSource = ThisWorkbook.Worksheets(CStr(str_source))
    rowCount = ShSource.Range("A" & ShSource.Rows.Count).End(xlUp).Row
    rowStep = CInt(int_rowStep)
    
    Application.ScreenUpdating = False
    
    For i = 2 To rowCount Step rowStep
        
        rowsToAdd = i + rowStep - 1
        If rowsToAdd > rowCount Then rowsToAdd = rowCount
        
        'Set wb = Workbooks.Add
        Set wb = Workbooks.Add
        Set ShDest = wb.Worksheets(1)
                
        'Copy headers
        ShSource.Rows(1).EntireRow.Copy ShDest.Range("A1")
        
        'Copying data in a way to avoid referencing original workbook
        ShSource.Rows(i & ":" & rowsToAdd).EntireRow.Copy
        ShDest.Range("A2").PasteSpecial xlPasteValues
        ShDest.Range("A2").PasteSpecial xlPasteFormats
        
        'Deselect range
        ShDest.Range("A1").Select
        
        'Save workbook
        wb.SaveAs (CStr(str_folderPath) & CStr(str_source) & "_" & i & "-" & rowsToAdd)
        wb.Close
        
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Function

str_source → Sheet name of workbook to copy data from
int_rowStep → No. of rows you want each workbook to contain
str_folderPath → Directory to store split up workbooks

Kind Regards,
Ashwin A.K

1 Like
6

Thanks @ashwin.ashok
Am going to try it out and then revert

7

Hey buddy how can we do it in the sense that it appends instead of just a new file

It just appends to the same file

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.