Today we'll look at how to use Python to work with Excel files, and working with Excel files is often used in the job, and Python takes this into account, and Python itself has its own CSV module.
1. Read the CSV file in Python :
CSV is a comma delimiter format generally we use the EXECL generated format is XLS and xlsx directly renamed to CSV will be error:
Error:line contains NULL byte
Insun Solution: The cause of the error is to rename the execl file with the suffix xls to csv as normal. If you save as a CSV file, you won't get an error.
For example, we have a CSV file:
#!/usr/bin/env python # -*- coding:utf-8 -*- import csv with open ( ‘egg.csv‘ , ‘rb‘ ) as f: reader = csv.reader(f) for row in reader: print row |
Print out a list like this
[' A ', ' 1 ', ' 1 ', ' 1 ']
[' A ', ' 2 ', ' 2 ', ' 2 ']
[' B ', ' 3 ', ' 3 ', ' 3 ']
[' B ', ' 4 ', ' 4 ', ' 4 ']
[' B ', ' 5 ', ' 5 ', ' 5 ']
[' B ', ' 6 ', ' 6 ', ' 6 ']
[' C ', ' 7 ', ' 7 ', ' 7 ']
[' C ', ' 8 ', ' 8 ', ' 8 ']
[' C ', ' 9 ', ' 9 ', ' 9 ']
[' C ', ' 10 ', ' 10 ', ' 10 ']
[' d ', ' 11 ', ' 11 ', ' 11 ']
[' E ', ' 12 ', ' 12 ', ' 12 ']
[' E ', ' 13 ', ' 13 ', ' 13 ']
[' E ', ' 14 ', ' 14 ', ' 14 ']
2. Write and generate CSV in Python
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import csv
with
open
(
‘egg2.csv‘
,
‘wb‘
) as csvfile:
spamwriter
= csv.writer(csvfile, delimiter
=
‘ ‘
,quotechar
=
‘|‘
, quoting
=
csv.QUOTE_MINIMAL)
spamwriter.writerow([
‘a‘
,
‘1‘
,
‘1‘
,
‘2‘
,
‘2‘
])
spamwriter.writerow([
‘b‘
,
‘3‘
,
‘3‘
,
‘6‘
,
‘4‘
])
spamwriter.writerow([
‘c‘
,
‘7‘
,
‘7‘
,
‘10‘
,
‘4‘
])
spamwriter.writerow([
‘d‘
,
‘11‘
,
‘11‘
,
‘11‘
,
‘1‘
])
spamwriter.writerow([
‘e‘
,
‘12‘
,
‘12‘
,
‘14‘
,
‘3‘
])
|
It's stored in a column that's not the same as the one we were supposed to have in 5 columns.
Using Python's CSV to generate Excel-compatible CSV files is primarily the dialect= ' Excel ' when creating a writer's parameters
The code is modified to:
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import
csv
with
open
(
‘egg2.csv‘
,
‘wb‘
) as csvfile:
spamwriter
=
csv.writer(csvfile,dialect
=
‘excel‘
)
spamwriter.writerow([
‘a‘
,
‘1‘
,
‘1‘
,
‘2‘
,
‘2‘
])
spamwriter.writerow([
‘b‘
,
‘3‘
,
‘3‘
,
‘6‘
,
‘4‘
])
spamwriter.writerow([
‘c‘
,
‘7‘
,
‘7‘
,
‘10‘
,
‘4‘
])
spamwriter.writerow([
‘d‘
,
‘11‘
,
‘11‘
,
‘11‘
,
‘1‘
])
spamwriter.writerow([
‘e‘
,
‘12‘
,
‘12‘
,
‘14‘
,
‘3‘
])
Python reads and generates Excel files