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
Writing them as CSV files might be a better idea
Hi @stanstilo ,
what about google spreadsheet it can hold 5 million rows.
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
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
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
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?
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
@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.
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.
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 @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.
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
You have any further queries to be discussed
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?
May I know the error you were getting
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
If I run directly without conversion, I get error: Arithmetic overflow error converting varchar to data type numeric