SriLakshmi
New Member
I have a requirement to identify first empty column out of the filtered records.
Excel 1= 7k rows and 1k columns
Upon applying filter criteria it will be max of 3 to 4 rows with 1000 columns.
For 1 employee If am not able to find empty columns for filtered records(3 to 4) from excel 1 then need to do the same find operation in 8 different excels immediately for same record before picking up the next record from i/p excel.
For second recrod again need to start find Operation from 3k.xlsx ....9k.xlsx
Excels= 3k.xlsx, 4k.xlsx....9k.xlsx.
Followed approaches:
Tried using
a) action:get collection as offset
b) OLEDB in code stage and also the OLEDB vbo to get excel data into collection
but after reading 2 to 3 excels am getting system out of memory exception.
c) tried to pass a query to get filtered records from excel itself.
d) created an action stage to call garbage collector explicitly "gc.collect" after reading excel data into collection.
But still system out of memory exception after reading 2excels
Here comes my queries:
1. OLEDB has a limitation to give only first 255 columns, but i need to get/all the columns for my select query statement.
How to get all 1000 columns using select query after opening OLEDB connection?
2. Am currently using
a. open OLEDB connection
b. execute open query and update query
c. Close OLEDB connection
If i repeat steps from a to c for each and every record from my i/p sheet.will there be any issue with performance?
3. At a time can i open multiple 8 OLEDB connections(conn1, conn2,conn3....conn8) with all 8 diff excels andperform select and update query and close that respective connection(after the update).
fyi...excel needs to be updated,so that my next record from i/p excel should always refer updated excel sheet.
4. If the above approach is not correct to use multiple OLEDB connection, then suggest me any appropriate solution.
which doesn't cause system out of memory exception.
Excel 1= 7k rows and 1k columns
Upon applying filter criteria it will be max of 3 to 4 rows with 1000 columns.
For 1 employee If am not able to find empty columns for filtered records(3 to 4) from excel 1 then need to do the same find operation in 8 different excels immediately for same record before picking up the next record from i/p excel.
For second recrod again need to start find Operation from 3k.xlsx ....9k.xlsx
Excels= 3k.xlsx, 4k.xlsx....9k.xlsx.
Followed approaches:
Tried using
a) action:get collection as offset
b) OLEDB in code stage and also the OLEDB vbo to get excel data into collection
but after reading 2 to 3 excels am getting system out of memory exception.
c) tried to pass a query to get filtered records from excel itself.
d) created an action stage to call garbage collector explicitly "gc.collect" after reading excel data into collection.
But still system out of memory exception after reading 2excels
Here comes my queries:
1. OLEDB has a limitation to give only first 255 columns, but i need to get/all the columns for my select query statement.
How to get all 1000 columns using select query after opening OLEDB connection?
2. Am currently using
a. open OLEDB connection
b. execute open query and update query
c. Close OLEDB connection
If i repeat steps from a to c for each and every record from my i/p sheet.will there be any issue with performance?
3. At a time can i open multiple 8 OLEDB connections(conn1, conn2,conn3....conn8) with all 8 diff excels andperform select and update query and close that respective connection(after the update).
fyi...excel needs to be updated,so that my next record from i/p excel should always refer updated excel sheet.
4. If the above approach is not correct to use multiple OLEDB connection, then suggest me any appropriate solution.
which doesn't cause system out of memory exception.