Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator III

How to split data Row number wise

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.

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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;

View solution in original post

9 Replies
sunny_talwar

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

trdandamudi
Master II
Master II

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
Employee
Employee

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
Master III
Master III

pra_kale
Creator III
Creator III
Author

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
Employee
Employee

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

pra_kale
Creator III
Creator III
Author

Ok. Once again Thanks for Help...

lakshmanvepuri
Creator
Creator

Thanks a lot for the Solution...

 

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