Import csv into xlsx python
I am trying to put some data from a csv file into an excel file. my existing excel file contains images and xlrd cannot get images. I am trying to use xlsxwriter but it cannot join existing xslx. the only solution I have found is using openpyxl.
import openpyxl
xfile = openpyxl.load_workbook('my_exist_file')
sheet = xfile.get_sheet_by_name('Sheet1')
with open("my_csv", 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
-here is my problem-
how can I write csv data (this is a table) to a specific location in an existing xslx? I want my table to start at cell K2.
thank!
source to share
reading CSV
using pandas.read_csv
to extract information
import pandas as pd
df = pd.read_csv(my_filename)
Parameters that may be required to specify
- sep: separator used
- Encoding
- header: Is the first line a label string?
- index_col: first column index
adding to excel table
inspired by: fooobar.com/questions/103008 / ...
check the pandas.to_excel
documentation for other possible options
book = load_workbook(old_filename)
sheet_name = 'Sheet1'
with pd.ExcelWriter(new_filename, engine='openpyxl') as writer:
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name=sheet_name, startrow=1, startcol=10, engine='openpyxl')
In the start and start words, indicate where on the sheet you want to insert your data.
This method can overwrite the previous content on this sheet. If this happens, you will have to iterate over the columns and rows of the DataFrame and add them by gender manually to the worksheet
Inserting images
If you have images to insert somewhere outside you can use the code from the documentation
from openpyxl.drawing.image import Image
ws = book['sheet_name_for_images']
ws['A1'] = 'You should see three logos below'
img = Image('logo.png')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
I have not tested this and you may need to insert this code before writer.sheets = ...
source to share
Use table cell method to update a specific cell
sheet.cell(row=<row>, column=<col>, value=<val>)
It is usually recommended to use keep_vba=True
when loading a book. Learn more about the help page .
Also check the answer to this question.
source to share