write to excel will be empty in column

[jacklee26]

i have a question about reading a text file, but in the text file occur many empty space.
My text file data_out.txt:
no mac mta MAC ETH MAC
=== =========== =========== ===========
2 7719B1822090 7719B1822091 7719B1822092
3 749111111210 491111112101 491111112102

My code:

import openpyxl,string 
f=open(r'D:\test\data_out.txt','r+') #open text 
#########if load excel file ########################
#excel=openpyxl.load_workbook(r'D:\\test\\test.xlsx') #open excel 
#excel=openpyxl.load_workbook(r'D:\\test\\test.xlsx') #open excel 
excel = openpyxl.Workbook() 
sheet=excel.worksheets 
line=f.readline(); #read text 

while line: 
    list=[] 
    list = line.split(sep=' ') #convert, 
    print(list)
    for i in range(0, len(list)): # remove space 
        
        #list[i] = list[i].strip('\n') 
        list[i] = list[i].strip('\n')
        #print(list[i])
    sheet[0].append(list) #wrire into excel 
    
    line=f.readline() #read next line 
    print(line)
excel.save(r'D:\test\test.xlsx')

When it write to my excel file, the column on c3, c4, c5,c6, d3,d4,d5,d6 will be empty.
How can i let G3 move to c3, and G4 move to c4.
is there any way

it will print
['no', '', '', '', '', '', '', '', '', '', '', 'mac', '', '', '', '', '', '', '', '', 'mta', 'MAC', '', '', '', '', '', '', '', '', '', 'ETH', 'MAC', '\n']
['===', '', '', '', '', '===========', '', '', '', '', '', '===========', '', '', '', '', '===========', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '\n']
['2', '\t7719B1822090', '', '', '', '', '7719B1822091', '', '', '', '', '7719B1822092\n']
['3', '\t749111111210', '', '', '', '', '491111112101', '', '', '', '', '491111112102\n']
['\n']


how to remove the ''?

[Larz60+]

one thing I notice immediately that will get you in trouble:
never name a list list.
that overwrites python's list
example:

>>> z = ('a','b','c')
>>> # following is legal
>>> zz = list(z)
>>> zz
['a', 'b', 'c']
>>> # following is taboo
>>> list = [1,2,3]
>>> z = ('a','b','c')
>>> zz = list(z)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: 'list' object is not callable
>>>

[jacklee26]

do you know how to write == ======= ======= ======= to excel

[Larz60+]

I rarely output any data to excel, usually use pandas, or some other format.
Please provide a sample data_out.txt file.

[jacklee26]

Below is data_out.txt file

no mac mta_MAC ETH_MAC
=== =========== =========== ===========
2 7719B1822090 7719B1822091 7719B1822092
3 749111111210 491111112101 491111112102

if not add === =======, it will write to excel, but if add it open excel will pop formula problem
i think maybe xcel think === it is formula

[snippsat]

Pandas read it fine,just need to remove first row with ===.
Guess i could get to work fine in openpyx to,but not looked into that now.

import pandas as pd

df = pd.read_clipboard() # Copy of your data
df = df.drop(df.index[0])
df.to_excel('mac.xlsx', index=False)

Output:
no	mac             mta_MAC         ETH_MAC
2	7719B1822090	7719B1822091	7719B1822092
3	749111111210	491111112101	491111112102

[jacklee26]

hi snippsat,
Can we also add === into excel

[snippsat]

Can we also add === into excel

Can leave it in bye comment out line 4,but in excel it will now be 0.

Output:
no	mac             mta_MAC         ETH_MAC
0                  0               0               0 
2	7719B1822090	7719B1822091	7719B1822092
3	749111111210	491111112101	491111112102