RaqForum 41 No.
598 View  • 
     ...

* How to Split a Large Excel File

 
split(3) excel(29)

Sometimes in real-world businesses you need to split a large Excel file into multiple smaller files. VBA can do this but it produces extremely complicated code that is prone to trigger memory overflow.

No more painful coding and memory problems if you choose to do it with esProc. To split a big Excel file into smaller files that has 1500 rows each, for example, we just need the following script:

A

B

D

1

=file("dt.xlsx").xlsimport@t()

/ Open the Excel file and   treat row 1 as headers

2

for A1.group((#-1)\1500)

/ Retrieve data iteratively,   1500 rows each time

3

=file(#A2/".xlsx").xlsexport(A2)

/ Generate small Excels   whose names are loop numbers

 

You may need to split the file by grouping it by column at times. For instance, the headers of the Excel file are col1, col2… and rows are already sorted by col1 (sorting can be done in advance in Excel), and you want to do the splitting by col1. The esProc gets it done in a simple way:

A

B

D

1

=file("dt.xlsx").xlsimport@t()

2

for A1.group(col1)

/Group and split by col1   iteratively

3

=file(A2.col1/".xlsx").xlsexport@t(A2)

/Subfile names are group   names

It’s still easy to achieve the two requirements at the same time with esProc, that is, splitting by col1 and each subfile contains 1500 rows at most:

A

B

C

D

1

=file("dt.xlsx").xlsimport@t()

2

for A1.group(col1)

/Group by col1 iteratively

3

for A2.group((#-1)\1500)

/Get 1500 rows each time   iteratively

4

=file(B3.col1/#B3/".xlsx").xlsexport@t(B3)

/Subfile names are group   name plus loop number

 

esProc is a scripting language that allows execution on a desktop IDE and display data in cells. It’s convenient to debug an iterative algorithm written with esProc.

 

More examples of handling Excel files (including those exceeding memory capacity) can be found below:

To parse and export an Excel file in SPL, see Parsing and Exporting Excel Data in SPL.
Read HERE to find an easy way to merge and summarize Excel files.

esProc handles text files and Excel files same ways, you know how to deal with one if you have already learnt the method of handling the other.

More examples about structured text processing can be found in Structured Text Computations with esProc.esProc is the file processor tool that is ready to use, simple to configure and powerful to process. Read Data File Processor.

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.

split(3) excel(29)
 
 •  598 View  •