Python Forum
how to read txt file, and write into excel with multiply sheet
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
how to read txt file, and write into excel with multiply sheet
#1
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')
Reply
#2
I'd first copy the original worksheet to a new worksheet
Then modify the new worksheet
see copy worksheet
Reply
#3
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')
jacklee26 likes this post
Reply
#4
Photo 
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.
   
Reply
#5
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.
jacklee26 likes this post
Reply
#6
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.
Reply
#7
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.
jacklee26 likes this post
Reply
#8
(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.
Reply
#9
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()
jacklee26 likes this post
Reply
#10
(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()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python openyxl not updating Excel file MrBean12 1 250 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Last record in file doesn't write to newline gonksoup 3 364 Jan-22-2024, 12:56 PM
Last Post: deanhystad
  Recommended way to read/create PDF file? Winfried 3 2,784 Nov-26-2023, 07:51 AM
Last Post: Pedroski55
  write to csv file problem jacksfrustration 11 1,373 Nov-09-2023, 01:56 PM
Last Post: deanhystad
  python Read each xlsx file and write it into csv with pipe delimiter mg24 4 1,312 Nov-09-2023, 10:56 AM
Last Post: mg24
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 755 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,696 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
Question Special Characters read-write Prisonfeed 1 582 Sep-17-2023, 08:26 PM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020