How to handle large files from database

Help Forum
1

Hi everyone, please what’s te best way to store large entries from database cause the entries are above 1,000,000 and couldn’t be contained or stored in excel file.Cheers

2

Hi Stanstilo,

Writing them as CSV files might be a better idea

3

Hi @stanstilo ,

what about google spreadsheet it can hold 5 million rows.

Regards,
Kirankumar.

4

thanks for your PM. Maybe you elaborate more on your case. As you mentioned it is already stored within a database, so the processing and processing result is to handle, right? In general we try to do the processing as close as possible on the source. So, we would try get it done with e.g. SQL

5

Alright ppr, the issue was that due to the large volume, we generated the entries as text file but we need to write the entries from text file back to excel files and perform some operations using the column headers but the entries were more than the excel sheets.Do you have a better way to handle that.If we are to do it.Or a better way to convert the text files to datatable with example.Thanks

6

which operations are to do / were implemented? It looks like the excel/csv approach pulls out data only for the purpose of processing. Can you help us and elaborate more on this? Thanks for support

7

I would like to pool the large data, then read the column name “ledger_no” and check entries having similar ledgger_no if true, move those entries to another sheet, but the entries are much and were written to a text file cause of large volume.How do I handle this please?

8

Give a try on doing this check within a SQL statement e.g. group by. The resulting datable can be split on chunks e.g. each X rows segment will be written out into csv or Excel

1 Like
9

@ppr,pls share sample code. I also used generate data table and got this error: Generate Data Table: Can not generate data table because the input file is too big or contains too many rows or columns.

10

Hi

First let’s take on processing those records
If it is fetched from sql database and if it is done with uipath itself then the output will be a DATATABLE

And all manipulation can be done in that DATATABLE itself before writing to excel

Now for the excel limit MS Excel itself is limited to 1,048,576 rows by 16,384 columns. While I never seen a worksheet with so many columns, it’s not that uncommon to have many rows, even close to the limit.

As you know, the UiPath Studio comes by default with two sources for Excel activities:

For large excel files, I recommend you to use the App Integration Excel activities (those that require an Excel Application Scope).
The activities from Workbook might throw exceptions when working on large excel files. You should always go for the App Integration → Excel activities when working with large files. Internally, they are using different libraries to access the content of the excel file, hence the difference in behavior.
The excel activities in Workbook can be faster when working with small files and the biggest advantage is that they do not require Microsoft Excel to be installed in the system.

As for testing with an sheet with 600k records, while the Excel activities took around 5 minutes to complete, the Workbook ones kept on running till 20 minutes after which I cancelled.

Cheers @stanstilo

1 Like
11

Cheers @Palaniyappan, while also working on tconverting text files to datatable, I got this error:Can not generate data table because the input file is too big or contains too many rows or column.

12

This happens
That’s why it good to hold those values in a database server and fetch them with uipath database query activities so that the manipulation becomes easy with that DATATABLE itself

Cheers @stanstilo

13

Thanks Palaniyappan

1 Like
14

Glad

You have any further queries to be discussed
@stanstilo

15

As you suggested on working directly with the sql source, I ran this query but got wrong output.Can you point out any error SELECT * FROM [FlourDB].[dbo].[transaction] where convert(decimal, Amount) = 33.50, this query didn’t return data equals 33.50 but on the database, there’s 33.50, please any error here?

16

Fine
May I know the error you were getting
@stanstilo

17

it didn’t throw error but after executing te query the output result was empty but when I check the database I can see the column where Amount = 33.50, but if I run the select query this query SELECT * FROM [FlourDB].[dbo].[transaction] where convert(decimal, Amount) = 33.50, I get null

18

You there?

19

If I run directly without conversion, I get error: Arithmetic overflow error converting varchar to data type numeric

20

Hello Stanley,
Here I have some examples with CSV and Datatables maybe this will help:

Thanks,
Cristian

2 Likes