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

[snippsat]

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)

[jacklee26]

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

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.

[snippsat]

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

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
================

[jacklee26]

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

[jacklee26]

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.