【Question】

I have a problem. I need to read data from thousands of text files and write it to one Excel file. The text data is clean and the files are structured as follows:
Folder: test
Files under it: a.txt, b.txt, c.txt, etc.
a.txt:

data A  data B  data C
Every text file is structured the same way – they contain one line only. data A, data B and data C are separated by tabs.

My requirement: Write all the text files under test folder to an Excel file. Three data items make a row and a column contains one data item.
Actually the real folder contains over ten thousand text files.

Is there a fast way to do this? Thanks.

【Answer】

Java does this by traversing all text file under the specified folder to read the data and write it to the target Excel file. The thing is that it’s rather complicated to make a program using the open source POI. esProc encapsulates POI and is integration-friendly with Java. It’s simple to do this with it. Here’s the SPL (Structured Process Language) script:

A1: Read the text files under E:\\test as a list of files.

A2: Combine data from all these files.

A3: Write data to the target xlsx file.

The asker wants to write over ten thousand files to one xlsx file. An xlsx file can hold one million rows at most, but the ten-thousand files, with, say, 10 rows each, will still exceed the Excel storage capability. It’s more suitable to write the data to a text file, which is unlimited in storage.