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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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:

1 2 3 4 5 6 7 8 9 10 11 12 13

>>> 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.

1 2 3 4 5

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