6

I have a text file that contains data like this. It is is just a small example, but the real one is pretty similar.

I am wondering how to display such data in an "Excel Table" like this using Python?

d_kennetz
  • 5,219
  • 5
  • 21
  • 44
Anqi
  • 125
  • 1
  • 1
  • 8
  • 2
    Do you need python? You can just rename your text file to csv and open it in excel. It will render as your desired output. – AnkDasCo Aug 13 '18 at 18:54
  • basically this is a duplicate of https://stackoverflow.com/questions/17684610/python-convert-csv-to-xlsx – gregory Aug 13 '18 at 19:23

4 Answers4

16

The pandas library is wonderful for reading csv files (which is the file content in the image you linked). You can read in a csv or a txt file using the pandas library and output this to excel in 3 simple lines.

import pandas as pd

df = pd.read_csv('input.csv') # if your file is comma separated

or if your file is tab delimited '\t':

df = pd.read_csv('input.csv', sep='\t')

To save to excel file add the following:

df.to_excel('output.xlsx', 'Sheet1')

complete code:

import pandas as pd
df = pd.read_csv('input.csv')
# can replace with:
# df = pd.read_csv('input.tsv', sep='\t') for tab delimited
df.to_excel('output.xlsx', 'Sheet1')

This will explicitly keep the index, so if your input file was:

A,B,C
1,2,3
4,5,6
7,8,9

Your output excel would look like this:

index excel example

You can see your data has been shifted one column and your index axis has been kept. If you do not want this index column (because you have not assigned your df an index so it has the arbitrary one provided by pandas):

df.to_excel('output.xlsx', 'Sheet1', index=False)

Your output will look like:

output excel no index

Here you can see the index has been dropped from the excel file.

d_kennetz
  • 5,219
  • 5
  • 21
  • 44
  • thank you for the knowledge. In the output file, the values seemed are not numbers, (Excel offers an option I clicked the cell "Convert to Number"). Any solution? – Mark K Feb 15 '19 at 08:18
  • In my case, it print all the data in one column even after i added a comma in the text file. – Ali Barani Aug 12 '19 at 12:45
  • @d_kennetz My values are storing in my text file in this way 0,0,0.0,0.0 and 1,1,1.0,1.0, and so on. – Ali Barani Aug 12 '19 at 23:08
  • what is `Sheet1`? – new Q Open Wid Sep 10 '19 at 21:36
  • @zixuan The name of your sheet. Make it whatever you want. – d_kennetz Sep 10 '19 at 21:38
  • I tried this code ```import pandas as pd df = pd.read_csv('MICnew.txt') df.to_excel('output.xlsx', 'Sheet1')``` But I get this error - UnicodeDecodeError: 'utf-8' codec can't decode byte 0x96 in position 2715: invalid start byte – Shri Nov 18 '21 at 06:26
  • hi @CodeBot, it means you have some encoding errors (weird characters). They may be hidden, or not. I'd encourage you to check out [this question](https://stackoverflow.com/questions/45529507/unicodedecodeerror-utf-8-codec-cant-decode-byte-0x96-in-position-35-invalid) , or upgrade to pandas >= 1.3.0 which has an option for handling encoding errors. – d_kennetz Nov 18 '21 at 11:44
  • @d_kennetz Yes Thanks, I got that later from a community member in my original [query here](https://stackoverflow.com/questions/69999080/replace-an-arrow-character-repeating-headers-and-blank-lines-in-text-file-and-p/70001731?noredirect=1#comment123793297_70001731). – Shri Nov 19 '21 at 10:31
2

You do not need python! Just rename your text file to CSV and voila, you get your desired output :)

If you want to rename using python then - You can use os.rename function

os.rename(src, dst)

Where src is the source file and dst is the destination file

AnkDasCo
  • 1,439
  • 11
  • 16
  • 5
    This solution is misguiding. At least with regard to the actual question. This is not a file conversion. This is a "hint" to excel, to open a text file. – M.Rau Aug 13 '18 at 19:15
  • Unfortunately, **so many web sites** use this as a crutch. The one that bothers me the most is PayPal - HUGE company and they can't seem to bother to provide true Excel files instead of CSV. – manassehkatz-Moving 2 Codidact Aug 13 '18 at 19:19
1

XLWT

I use the XLWT library. It produces native Excel files, which is much better than simply importing text files as CSV files. It is a bit of work, but provides most key Excel features, including setting column widths, cell colors, cell formatting, etc.

Community
  • 1
  • 1
manassehkatz-Moving 2 Codidact
  • 2,906
  • 2
  • 15
  • 25
1

saving this is:

df.to_excel("testfile.xlsx")
M.Rau
  • 764
  • 5
  • 10