Import Csv To Xlsx Python

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 exi

Solution 1:

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

Solution 2:

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.