Textdata to excel

Help Activities
1

Hi all,
I have a text file with huge data around 2lakhs i need to convert .txt to .xlsx.

I am using generate data table to convert but while writing to excel I am not able to write because of the excel limit.

Can anyone help me how do I proceed with this.

Any help will be more appreciable. Thanks in advance

2

@0bb4628e217fd43ac86ac9294

Try to write the data in chunks…

Instead of writing whole data at once

Dt.AsEnumerable.chunks(50000) will give the rows as 50k and use this in for loop…first time use a write range and later use append range…to know fifference use index property in for loop index=0 write range else append

Cheers

3

@Anil_G thank you for your quick response, can you please explain me after generate data table what do I do.

4

@0bb4628e217fd43ac86ac9294

Follow these steps:

Read the Text File: Use the “Read Text File” activity to read the content of the .txt file. You’ll have a large string containing your data.

Split the Data: If the data has a consistent structure (e.g., comma-separated values), split the string into rows and columns. You can split it into a DataTable or a List of Lists, depending on your data’s structure.ie,
- Split textData into chunks (e.g., read a certain number of lines)
- Convert each chunk to a DataTable

Process Data in Chunks: Instead of loading all data into memory at once, process it in chunks. For example, read a certain number of lines from the text file, convert them to a DataTable, and then write that DataTable to an Excel file. Repeat this process until you’ve processed all data.

Write to Excel: Use the “Write Range” activity to write the DataTable to an Excel file. When writing in chunks, ensure you’re writing to different sheets in the same Excel file or different Excel files to avoid Excel’s row limitations.

5

@0bb4628e217fd43ac86ac9294

  1. For loop with dt.AsEnumerable.chunks(50000)
  2. Add a integer variable to index in for loop
  3. If condition with index=0
  4. On then side use write range with currentitem.copytodatatable
  5. On else side use append range with currentitem.copytodatatable

Cheers

6

To handle this amount of data, you are better off using specialized tools such as Python with the Pandas library. You can write a Python script that will read data from a text file and write it to an Excel file with no size restrictions. Pandas processes data efficiently and allows you to work with large amounts of information.