Python Forum
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

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

(Jan-07-2023, 09:13 AM)jacklee26 Wrote: 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()
Is not a error just a Warning,if look into it.
pandas.ExcelWriter they have deprecated save() so use close() instead.
Or better as they advice use writer as context manager,then no need to save or close.
with pd.ExcelWriter('out.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, 'sheet', index=False)
    df2.to_excel(writer, 'sheet1', index=False)


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

(Jan-07-2023, 01:20 PM)snippsat Wrote:
(Jan-07-2023, 09:13 AM)jacklee26 Wrote: 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()
Is not a error just a Warning,if look into it.
pandas.ExcelWriter they have deprecated save() so use close() instead.
Or better as they advice use writer as context manager,then no need to save or close.
with pd.ExcelWriter('out.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, 'sheet', index=False)
    df2.to_excel(writer, 'sheet1', index=False)

HI sorry i have one more question to ask,
why is my excel only showing one sheet(sheet1), why is sheet not showing.

I use the print
Quote:print(df1)
print(df2)

it will show 
   datetime   name department
1  20220101  James         IT
2  20220101   Test         IT
   datetime   name country
1  20220101  James      US
2  20220101   Test      US
but when opening excel only sheet1 exist, is there any way to also let sheet display.

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

(Jan-10-2023, 08:07 AM)jacklee26 Wrote: but when opening excel only sheet1 exist, is there any way to also let sheet display.
The code i did make should have both sheet and and sheet1 in out.xlsx
[Image: cPFfNw.png]
Code and with better names for sheet.
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
with pd.ExcelWriter('out.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, 'sheet1', index=False)
    df2.to_excel(writer, 'sheet2', index=False)
The input pd.txt is just copy of your text file exmple in post 1.
Output:
datetime name department
20220101 James IT
20220101 Test IT
================
datetime name country
20220101 James US
20220101 Test US
================


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

Thanks a lot,it work this time. I will try to study this.

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

hi i have some questions about the index s1 = result[:3]
what if my file have more than 3 line, if my text file as below
datetime name department
20220101 James IT
20220101 Test IT
20220101 Test IT
20220101 Test IT
20220101 Test IT
20220101 Test IT
================
datetime name country
20220101 James US
20220101 Test US
20220101 Test US
================

it seems like putting a fixed index is not a good way, is there any method without using fixed index.