Python Forum
Convert Excel file to Text file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Convert Excel file to Text file
#1
Dear Python Community:

I am a new Python student trying to learn more about this incredible programming language. I hope you could help me teach how to convert Excel file (.xlsx) to Text file (.txt) using a Python code.

This is not a high priority request, so please take as much time as you need. Your help will make my daily job much easier.

If you have any questions, please respond to this thread.
Reply
#2
what have you tried so far?
We're glad to help, but won't write it for you without an effort on your part.
Reply
#3
I see that. Thanks.

Here is the code:

import pandas as pd

df = pd.read_excel('C:\\Users\\path\\filename.xlsx', sheet_name='Sheet1', index=0)
with open('C:\\path\\filename.txt') as outfile:
	pd.to_string(outfile)
I am getting this error as a result:

Quote:Traceback (most recent call last):
File "C:\Users\file\filename.py", line 5, in <module>
pd.to_string(outfile)
File "C:\Users\file\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\__init__.py", line 263, in __getattr__
raise AttributeError(f"module 'pandas' has no attribute '{name}'")
AttributeError: module 'pandas' has no attribute 'to_string'
[Finished in 0.9s]
Reply
#4
pd is what you called pandas, your dataframe that you want to store as text is called df so you need df.to_string().

import pandas as pd
 
df = pd.read_excel('C:\\Users\\path\\filename.xlsx', sheet_name='Sheet1', index=0)
with open('C:\\path\\filename.txt', 'w') as outfile:
    df.to_string(outfile)
This will create a text file but it will also have the pandas index numbers and the column headers. You can turn those off.

Since you only said excel to text we can't really tell what you need. Excel has lots of metadata for formatting, multiple sheets, etc. Converting it to pure text would give you one long string with all the data in the cells but no format or tabular separators (commas, tabs, spaces, linefeeds, etc.) so your columns and rows would be lost. That's probably NOT what you want so let us know more specifics if you can't figure it out by reading some docs...

EDIT: I also added the write flag ('w') to the open file. It might default to that but being explicit is always a good idea in python code.
"So, brave knights, if you do doubt your courage or your strength, come no further, for death awaits you all with nasty, big, pointy teeth!" - Tim the Enchanter
Reply
#5
Hi @Marbelous,

Thank you very much for your assistance! I highly appreciate that! Thumbs Up

Your Python code was successful at converting an Excel file to text. Here is the text output I am getting when the code is run:

Output:
E-mail Address First Name Last Name 0 [email protected] John Smith 1 [email protected] Jane Anderson
However, I am looking for the following output:

Output:
E-mail Address First Name Last Name [email protected] John Smith [email protected] Jane Anderson
Is there a way to change the code to get the output I am looking for?

My excel file has only one sheet labelled as Page 1 and the data looks like this:

Output:
Column 1 Column 2 Column 3 Row 1 E-mail Address First Name Last Name Row 2 [email protected] John Smith Row 3 [email protected] Jane Anderson
Please let me know if you have any questions.
Reply
#6
The extra column is the pandas indexes I mentioned. You can turn it off with the index attribute of the df.to_string() method:

df.to_string(outfile, index=False)
"So, brave knights, if you do doubt your courage or your strength, come no further, for death awaits you all with nasty, big, pointy teeth!" - Tim the Enchanter
Reply
#7
Thank you very much for your assistance, Marbelous!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python openyxl not updating Excel file MrBean12 1 252 Mar-03-2024, 12:16 AM
Last Post: MrBean12
Question [SOLVED] Correct way to convert file from cp-1252 to utf-8? Winfried 8 547 Feb-29-2024, 12:30 AM
Last Post: Winfried
  Copy Paste excel files based on the first letters of the file name Viento 2 349 Feb-07-2024, 12:24 PM
Last Post: Viento
  file open "file not found error" shanoger 8 946 Dec-14-2023, 08:03 AM
Last Post: shanoger
  Search Excel File with a list of values huzzug 4 1,148 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 756 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  Replace a text/word in docx file using Python Devan 4 2,864 Oct-17-2023, 06:03 PM
Last Post: Devan
  Need to replace a string with a file (HTML file) tester_V 1 699 Aug-30-2023, 03:42 AM
Last Post: Larz60+
  How can I change the uuid name of a file to his original file? MaddoxMB 2 874 Jul-17-2023, 10:15 PM
Last Post: Pedroski55
  Convert File to Data URL michaelnicol 3 1,083 Jul-08-2023, 11:35 AM
Last Post: DeaD_EyE

Forum Jump:

User Panel Messages

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