Skip to content
Advertisement

How can i write excel columns into one column in text file?

I have an excel sheet and have huge data in many columns and I need to print those columns to follow each other in one column in a text file .. I searched a lot for a tool to do this but couldn’t find and tried to take it manually but I’m stuck in the data. Can this be done by python?

i have data like this in excel

1760    -67.4144    -51.5741
1761    -70.0035    -52.6686
1762    -82.4125    -33.0582
1763    -88.4259    -35.5613
1764    -63.6835    -38.474
1765    -90.6215    -43.0932
1766    -65.9159    -38.9343
1767    -76.7137    -42.3622
1768    -94.9792    -31.1532
1769    -71.3852    -46.629
1770    -65.8548    -47.4222

and need to write them in text file like this

1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
-67.4144
-70.0035
-82.4125
-88.4259
-63.6835
-90.6215
-65.9159
-76.7137
-94.9792
-71.3852
-65.8548
-51.5741
-52.6686
-33.0582
-35.5613
-38.474
-43.0932
-38.9343
-42.3622
-31.1532
-46.629
-47.4222

Advertisement

Answer

First run this command in terminal

pip install pandas
pip install openpyxl
pip install xlrd

Then run this in python

import pandas as pd

df = pd.read_excel('my_file.xlsx')

my_file = open('my_file.txt','w')
for data in df.columns:
    my_file.write(df[data].to_string(index=False)+'n')

my_file.close()

To remove empty Nan values from seies, use dropna

my_file.write(df[data].dropna().to_string(index=False)+'n')

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement