How to copy csv contents and paste to Excel sheet?

Question

I am using python v3 xlwings library to interact with MS Excel. I have a csv file ToCopy.csv and would like to copy the entire contents of this cvs file into an Excel file.

import xlwings as xw

Book_name = 'C:/Temp/ExcelBook.xlsm'
sheet_name = 'SheetName' #paste into this sheet

wb = xw.Book(Book_name)
sht = wb.sheets[sheet_name]

I looked at xlwings documentation but am still at a loss on how to get started. Can someone give me a head-start?

I am open to using other methods which are not based on xlwings library. However, my Excel file is password-protected and I know xlwings can handle password-protected Excel files. I am not sure about other methods.

Answer 1

So from the documentation http://docs.xlwings.org/en/stable/quickstart.html

Reading/writing values to/from ranges is as easy as:

>>> sht.range('A1').value = 'Foo 1'
>>> sht.range('A1').value

'Foo 1' There are many convenience features available, e.g. Range expanding:

>>> sht.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
>>> sht.range('A1').expand().value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

In the first example, a value is written to location A1. In the second, value are being written A1, B1, C1 and A2, B2, C2

So you probably want to do something like

for row,line in enumerate(inputcsv):
  myrange = 'A'+row
  mydata = line.split(',')
  sht.range(myrange).value = mydata

Other options:

1. Excel will import csv directly - import manually
2. VBA Macro in excel to import file, trigger via xlwing

Answer 2

Since you write that you've tried xlsxwriter this may not work, but have you considered openpyxl?

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

df = pd.read_csv('ToCopy.csv', header=None, index_col=None)
wb = load_workbook(filename='C:/Temp/ExcelBook.xlsm')
ws = wb.create_sheet()
ws.title = 'ToCopy'

for r in dataframe_to_rows(df, index=False, header=False):
    ws.append(r)

wb.save('C:/Temp/ExcelBook.xlsm')

Answer 3

Try to open the csv and save as xlsx directly:

import xlwings as xw
wb = xw.Book('C:\source.csv')  # Connect to an existing file
# wb = xw.Book(r'C:\Users\hhsg\Desktop\target.xlsx')  # On Windows: use raw strings to escape backslashes
wb.save('C:\target.xlsx')
# wb.save(r'C:\target.xlsx')  # On Windows: use raw strings to escape backslashes