+- 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: write to excel will be empty in column (/thread-27850.html)
write to excel will be empty in column - jacklee26 - Jun-24-2020
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 ''?
RE: write to excel will be empty in column - Larz60+ - Jun-24-2020
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
>>>
RE: write to excel will be empty in column - jacklee26 - Jun-26-2020
do you know how to write == ======= ======= ======= to excel
RE: write to excel will be empty in column - Larz60+ - Jun-26-2020
I rarely output any data to excel, usually use pandas, or some other format.
Please provide a sample data_out.txt file.
RE: write to excel will be empty in column - jacklee26 - Jun-26-2020
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
RE: write to excel will be empty in column - snippsat - Jun-26-2020
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 491111112102RE: write to excel will be empty in column - jacklee26 - Jun-26-2020
hi snippsat,
Can we also add === into excel
RE: write to excel will be empty in column - snippsat - Jun-27-2020
(Jun-26-2020, 11:35 PM)jacklee26 Wrote: Can we also add === into excelCan 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