TXT to Excel columns - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: TXT to Excel columns (/thread-28678.html) |
TXT to Excel columns - Kristenl2784 - Jul-29-2020 Hello, I have a txt file, so when I open it, everything is under 1 column. I want to convert to excel and break the data up and only use column 0, and 9. My code almost works, but the data I'm getting under column 9 isn't totally correct. Under column 9 instead of getting 34 or 222, I'm getting letters N or M from prior columns. Column 9 contains miles so it should all be numbers no letters. I believe this is happening because of the data that would be under column 1. Example of data that would appear under column 1: NYK13 SIG 222/222 A SIG 444/555 B NYK17 Due to the spaces in some of the words (SIG 222/222/ A) it's shifting the data over into other columns. When its NYK13 I'm receiving the correct data for column 9. When it's SIG 22/22/ A I'm receiving that data that would be under column 8. Is there a way to stop this from happening? Node1 = pd.read_csv('MMM.NODE', sep='\t',engine='python') Node1.to_excel('Node.xlsx', 'Sheet1',index=False) Node2 = pd.read_excel('Node.xlsx',header=None,skiprows=10,names=['A']) Node3 = Node2['A'].str.split(expand=True)[[0,9]] Node3.columns = ['Node','Mile'] Node3.to_excel('Node.xlsx',index=False) |