Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
TXT to Excel columns
#1
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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to properly format rows and columns in excel data from parsed .txt blocks jh67 7 1,798 Dec-12-2022, 08:22 PM
Last Post: jh67
  How to keep columns header on excel without change after export data to excel file? ahmedbarbary 0 1,129 May-03-2022, 05:46 PM
Last Post: ahmedbarbary
  Python Matplotlib: Create chart for every 4 columns in Excel file JaneTan 2 2,732 Feb-28-2021, 05:02 AM
Last Post: JaneTan

Forum Jump:

User Panel Messages

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