how to read txt file, and write into excel with multiply sheet

how to read txt file, and write into excel with multiply sheet - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: how to read txt file, and write into excel with multiply sheet (/thread-39099.html)

Pages: 1 2

how to read txt file, and write into excel with multiply sheet - jacklee26 - Jan-01-2023

Hi I have faced one problem I wish to read a text file, and when reaching ===== ending, I wish to write into a new separate sheet.
So for example, If I have a text file like below this:

datetime name department
20220101 James IT
20220101 Test IT
================
datetime name country
20220101 James US
20220101 Test US
================

So the default sheet1 will write like this:
datetime name department
20220101 James IT
20220101 Test IT

sheet2 will show
datetime name country
20220101 James US
20220101 Test US

How am I able to establish this result, the =====

I have a code but this only help me to write in a single sheet, but I wish after ==== it will write into a new sheet

import openpyxl, string
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font

f = open('test.txt', 'r+')  # open text
excel = openpyxl.Workbook()
sheet = excel.worksheets
line = f.readline();  # read text
while line:
    list = line.split()  # convert
    if "=" in line:
        pass
    else:            
        print(list123)
        if list [1] == 'userID':
            sheet[0].append(list )  # write into excel
        elif list [2] == 'name':
            sheet[0].append(list )  # write into excel
        else:  
            sheet[0].append(list )  # write into excel
    
    line = f.readline()  # read next line

excel.save('result.xlsx')

I try like this will occur error

from openpyxl.workbook import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Sheet_A")
ws1.title = "Title_A" 
f = open('test.txt', 'r+')  # open text
excel = openpyxl.Workbook()
sheet = excel.worksheets
line = f.readline();  # read text
count =0
while line:
    list= line.split()  # convert
    if list[1] == 'userID':
        ws1[0].append(list)  # write into excel
    elif list[2] == 'name':
        ws1[0].append(list)  # write into excel
    else:  
        ws1.append(list)  # write into excel
    line = f.readline()
excel.save('test123.xlsx')

RE: how to read txt file, and write into excel with multiply sheet - Larz60+ - Jan-01-2023

I'd first copy the original worksheet to a new worksheet
Then modify the new worksheet
see copy worksheet

RE: how to read txt file, and write into excel with multiply sheet - gradlon93 - Jan-04-2023

Perhaps not the most efficient implementation, but an easy one:

import openpyxl

wb = openpyxl.Workbook()
sheet_counter = 1

# You should consider adopting better conventions such as these ones:
SHEET_SEPARATOR = "==="
COLUMN_SEPARATOR = '|'

with open('text.txt', 'r+') as raw_text:
    lines = raw_text.read().split(SHEET_SEPARATOR)

    for line in lines:
        rows = line.split('\n')
        ws = wb.create_sheet(f'Sheet{sheet_counter}')
        
        for row in rows:
            ws.append(row.split(COLUMN_SEPARATOR))
            wb.save('result.xlsx')

        sheet_counter += 1

    wb.save('result.xlsx')

RE: how to read txt file, and write into excel with multiply sheet - jacklee26 - Jan-05-2023

HI gradlon93,
thanks for reply

is it possible to do like the attached picture, i am still figuring it out, but still have no idea.
[attachment=2173]

RE: how to read txt file, and write into excel with multiply sheet - codinglearner - Jan-05-2023

To read a text file and write the contents into an Excel file with multiple sheets, you will need to use a programming language such as Python.

Here is an example of how you can do this using Python:

1. First, you will need to install the xlwt library, which allows you to write to an Excel file. You can install it using the following command:

pip install xlwt

2. Next, you will need to open the text file and read its contents. You can do this using the open function and the readlines method:

with open('text_file.txt', 'r') as f:
    lines = f.readlines()

3. Now you can create a new Excel file and add a new sheet for each line in the text file. To do this, you will need to use the xlwt library:

import xlwt

# Create a new Excel file
workbook = xlwt.Workbook()

# Add a new sheet for each line in the text file
for i, line in enumerate(lines):
    sheet = workbook.add_sheet(f'sheet{i}')
    sheet.write(0, 0, line)

# Save the Excel file
workbook.save('excel_file.xls')

This will create a new Excel file with multiple sheets, where each sheet contains a line from the text file.

Hope this helps you.

RE: how to read txt file, and write into excel with multiply sheet - jacklee26 - Jan-05-2023

What if i don't want to have multiply sheet, I only wants to have two sheet, when ever see the "=" after that line add into new sheet. Seem like this code will add new sheet to every line.

RE: how to read txt file, and write into excel with multiply sheet - codinglearner - Jan-06-2023

I am not sure but try this,

If you only want to have two sheets in the Excel file and you want to split the contents of the text file into these two sheets based on the presence of the = character, you can do the following:

1. Read the text file line by line and check each line for the = character. If the = character is present, add the line to one list, otherwise add it to another list.

2. Create a DataFrame from each list using the pandas.DataFrame() constructor, and specify the column names as the first line in each list.

3. Use the pandas.ExcelWriter() function to create a new Excel file.

4. Use the DataFrame.to_excel() method to write each DataFrame to a sheet in the Excel file.

Example:

import pandas as pd

# Read the text file and split the lines into two lists
with open('file.txt', 'r') as f:
    lines_with_equals = []
    lines_without_equals = []
    for line in f:
        if '=' in line:
            lines_with_equals.append(line)
        else:
            lines_without_equals.append(line)

# Create a DataFrame from each list and specify the column names
df1 = pd.DataFrame(lines_with_equals, columns=['Line'])
df2 = pd.DataFrame(lines_without_equals, columns=['Line'])

# Create a new Excel file
writer = pd.ExcelWriter('file.xlsx', engine='xlsxwriter')

# Write each DataFrame to a sheet in the Excel file
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')

# Save the Excel file
writer.save()

This code will read the text file line by line, split the lines into two lists based on the presence of the = character, create a DataFrame from each list, and write the DataFrames to two sheets in an Excel file.

RE: how to read txt file, and write into excel with multiply sheet - gradlon93 - Jan-06-2023

(Jan-05-2023, 01:12 AM)jacklee26 Wrote: HI gradlon93,
thanks for reply

is it possible to do like the attached picture, i am still figuring it out, but still have no idea.

The implementation that I gave you does that, no matter how many “sections” you have in your text. Also, you can easily name your sheets as you prefer, or delete the first one if you don’t need it.

If you get blank pages or blank lines that’s probably because you are using a Windows text editor that adds ascii chars at the end of line or something similar; in that case, you can easily bypass that by using a plain text editor like Notepad (on Windows) or Sublime (cross-platform).

You can also benefit a lot from setting easier-to-handle conventions if you can, because to be honest I don’t like those equal signs.

As I said, my implementation of openpyxl was not the best, but I think it’s suitable for your level. The same result can be achieved, for example, with clever use of regular expressions and list comprehension.
If you are interested I can dig further and show you what I mean by that.

Hope this helps, enjoy your coding.

RE: how to read txt file, and write into excel with multiply sheet - snippsat - Jan-07-2023

Can show a way,so output is like in the image.

import pandas as pd

with open("pd.txt") as f:
    result = [i.strip('================') for i in f]

# Clean up
s1 = result[:3]
s1 = [i.strip().split() for i in s1]
s2 = result[3:][1:-1]
s2 = [i.strip().split() for i in s2]
# To Pandas
df1 = pd.DataFrame(s1)
df1 = df1.rename(columns=df1.iloc[0]).drop(df1.index[0])
df2 = pd.DataFrame(s2)
df2 = df2.rename(columns=df2.iloc[0]).drop(df2.index[0])
# To Excel
writer = pd.ExcelWriter('out.xlsx', engine='openpyxl')
df1.to_excel(writer, 'sheet', index=False)
df2.to_excel(writer, 'sheet1', index=False)
writer.save()

RE: how to read txt file, and write into excel with multiply sheet - jacklee26 - Jan-07-2023

(Jan-07-2023, 12:10 AM)snippsat Wrote: Can show a way,so output is like in the image.

import pandas as pd

with open("pd.txt") as f:
    result = [i.strip('================') for i in f]

# Clean up
s1 = result[:3]
s1 = [i.strip().split() for i in s1]
s2 = result[3:][1:-1]
s2 = [i.strip().split() for i in s2]
# To Pandas
df1 = pd.DataFrame(s1)
df1 = df1.rename(columns=df1.iloc[0]).drop(df1.index[0])
df2 = pd.DataFrame(s2)
df2 = df2.rename(columns=df2.iloc[0]).drop(df2.index[0])
# To Excel
writer = pd.ExcelWriter('out.xlsx', engine='openpyxl')
df1.to_excel(writer, 'sheet', index=False)
df2.to_excel(writer, 'sheet1', index=False)
writer.save()

when i run it occur this error, do you know how to fix this
FutureWarning: save is not part of the public API, usage can give unexpected results and will be removed in a future version
writer.save()