import csv to xlsx python

Question

I'm trying to put some data from a csv file to exist excel file. my exist excel file contains images and xlrd cannot get images. I try to use xlsxwriter but it cannot append to existing xslx. the only solution I've found is to use 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 the csv data (that is a table) to a specific location in the exist xslx? I want that my table will start at K2 cell.

thanks!

pandas has an excellent `read_csv` method, and you can add data to the excelfile with [this workaround](https://stackoverflow.com/a/20221655/1562285) (I would still keep some backups of the excel before you try, since it might erase the other content on the sheet) — Maarten Fabré, Jul 11 '17 at 09:55
thanks, I took a look in the workaround and I insert my csv data to: data=pandas.read_csv("my_csv"), after that I tried to data.to_excel(writer,"Sheet1") but my data isn't in utf-8. how can I decode it? + I didn't understand where I put K2 cell to be the starting cell of my csv in the exist file — Michal, Jul 11 '17 at 10:24
Do you remember how was your solution? I have a similar issue here: https://stackoverflow.com/questions/61966833/populate-an-excel-file-from-csv-input-file-line-by-line — chan go, May 23 '20 at 20:09

score 0 · Answer 1 · answered Jul 11 '17 at 08:32

0

Use the worksheet's cell method to update a specific cell

sheet.cell(row=<row>, column=<col>, value=<val>)

It is usually a good idea to use keep_vba=True while loading workbook. Check the help page for more details.

Also check answer to this question.

answered Jul 11 '17 at 08:32

Ketan Mukadam

789
3
7

thanks, I tried this but now in cell K2 I have only one letter and not all my table.. – Michal Jul 11 '17 at 08:50

Maarten Fabré · Accepted Answer · 2017-07-12T07:44:12.623

reading the CSV

using pandas.read_csv to extract the information

import pandas as pd
df = pd.read_csv(my_filename)

Options you might need to specify

sep: which separator is used
encoding
header: Is the first row a row of labels?
index_col: is the first column an index

adding to an excel worksheet

inspired by: https://stackoverflow.com/a/20221655/1562285 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')

The startrow and startcol say where in the worksheet you want to paste your data.

This method might overwrite the previous content on this worksheet. If it does, you will have to loop over the columns and rows of the DataFrame and add them semi-manually to the worksheet

Inserting images

If you have the images to insert somewhere externally 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 did not test this, and you might need to insert this code before the writer.sheets = ...

the problem now is that my exist xlsx file contains images and in the new file they aren't there :( — Michal, Jul 11 '17 at 12:56
from the Openpyxl documentation: `openpyxl does currently not read all possible items in an Excel file so images and charts will be lost from existing files if they are opened and saved with the same name.` — Maarten Fabré, Jul 11 '17 at 14:51

import csv to xlsx python

2 Answers2

reading the CSV

adding to an excel worksheet

Inserting images

Linked