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.
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)
Reply
#7
Thank you very much for your assistance, Marbelous!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy column from one existing excel file to another file mkujawsk 0 101 Apr-14-2021, 06:33 PM
Last Post: mkujawsk
  Text File Manipulation Queries? JustJeff 2 179 Apr-10-2021, 08:12 PM
Last Post: JustJeff
  Importing text file into excel spreadsheet with formatting david_dsmn 1 168 Apr-05-2021, 10:21 PM
Last Post: david_dsmn
  Add a new column when I extract each sheet in an Excel workbook as a new csv file shantanu97 0 137 Mar-24-2021, 04:56 AM
Last Post: shantanu97
  Need help with saving output into an excel file Beyondfacts 4 278 Mar-22-2021, 11:51 AM
Last Post: jefsummers
  Move file from one folder to another folder with timestamp added end of file shantanu97 0 139 Mar-22-2021, 10:59 AM
Last Post: shantanu97
  Matplotlib: How do I convert Dates from Excel to use in Matplotlib JaneTan 1 232 Mar-11-2021, 10:52 AM
Last Post: buran
  find the header location in a .bin file without reading the whole file at a time SANJIB 0 175 Mar-05-2021, 04:08 PM
Last Post: SANJIB
  Python Matplotlib: Create chart for every 4 columns in Excel file JaneTan 2 381 Feb-28-2021, 05:02 AM
Last Post: JaneTan
  Yahoo_fin, Pandas: how to convert data table structure in csv file detlefschmitt 14 938 Feb-15-2021, 12:58 PM
Last Post: detlefschmitt

Forum Jump:

User Panel Messages

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