How to split data Row number wise

[pra_kale]

Hi,

I have attached one sample application in which i have imported one excel file.

If i want to split this data on the basis of number of rows like by keeping a limit of 250 in a one file. Means 1 file is get saved of 250 and another of 250 and so on.

Can you please help me how i can do this.

Thanks in advance.

[sunny_talwar]

You mean 150 rows from one file? Not sure I understand your question

[trdandamudi]

Are you trying to load the excel file and then splitting the data every 250 rows and saving back to Excel ? Is that what you want ?

[Clever_Anjos · Accepted solution]

Maybe this

A:
LOAD RowNo() as row,
PolicyNo
FROM
[test.xlsx]
(ooxml, embedded labels, table is Sheet1);

LET file=0;
LET count=NoOfRows('A');
DO WHILE count > 0 

  t:
  FIRST (250) 
  LOAD 
  PolicyNo
  resident A
  where row > $(file)*250 and row < ($(file)+1)*250;
  store t into f$(file).txt(txt);
  drop Table t;
  LET count = $(count) - 250;
  LET file = $(file) + 1;

LOOP;

[settu_periasamy]

May be check this

Paging Implementation for Straight table

[pra_kale]

Hi All,

Thanks all for your help!!

In real scenario i am having a data more than 15 to 20 lacs, it may increase also, as i want to share data in to .xlsx file i required to split the data into 5 lacs records. E.g. If my records are 20 lacs then i will get the the 5 files containing 5 lacs records in each file.

Thanks Anjos.. code is working perfectly. I have made only one change instead of .text i have put .csv and it is working perfectly. But if i want to save the file into .xlsx whether it is possible.

[Kushal_Chawda]

No you cannot store in xlsx. You can only store it in QVD, CSV or TXT

[Clever_Anjos]

Unfortunately exporting to xls(x) is not a native feature

[pra_kale]

Ok. Once again Thanks for Help...

[lakshmanvepuri]

Thanks a lot for the Solution...

I am checking for the sample for the same and found the solution is clear.