How to paste a CSV file into an Excel file using Pandas

background

Pasting a CSV file with VBA in Excel is tedious because you have to write a lot of code. I tried to find out if there was an easy way to write in Python.

Purpose

Paste the CSV contents on the specified sheet of Excel.

Install xlwings

Python has several modules for working with Excel. Perhaps the most famous is Openpyxl. However, although Openpyxl has a high processing speed, it has many problems (format collapse, etc.), so This time I would like to use xlwings.

If xlwings is not installed, install it with the following command.

$ pip install xlwings

code

Since it seems that it will be used frequently in the future, I made it a function. After reading the CSV file with Pandas, it is pasted in cell A1 of the specified sheet. With VBA, it is necessary to read CSV line by line and output it, With Pandas, it's very simple to write.

import pandas as pd
import xlwings as xw

def csv_to_sheet(wb_name, ws_name, csv_name, save_flg=True, quit_flg=True):
    """\
Function description:
A function that copies the contents of CSV to a specified sheet

Argument description:
        wb_name: workbook name
        ws_name: Sheet name
        csv_name: CSV file name
        save_flg: Overwrite is performed by default.
        quit_flg: Default is closed
    """

    df = pd.read_csv(csv_name,encoding='cp932', index_col=0) #Read CSV file with DataFrame

    wb = xw.Book(wb_name) #Creating a workbook object
    wss = wb.sheets
    ws = wss[ws_name] #Creating a worksheet object

    ws.range('A1').value = df #Paste DataFrame

    #save_Overwrite if flg is True
    if save_flg:
        wb.save(path=None)

    #quit_Close Excel if flg is True
    if quit_flg:
        app = xw.apps.active  
        app.quit() #Exit Excel

How to Use

Please change the Excel file, sheet name, and CSV file to any ones you like. If you do not want to overwrite, set save_flg = False as an argument. If you want to continue editing without closing the file, pass quit_flg = False.

wb_name = 'Excel file name.xlsx'
ws_name = 'Sheet name'
csv_name = 'CSV file'
csv_to_sheet(wb_name, ws_name, csv_name)

Recommended Posts

How to paste a CSV file into an Excel file using Pandas
[Python] How to output a pandas table to an excel file
How to turn a .py file into an .exe file
Try to operate an Excel file using Python (Pandas / XlsxWriter) ①
Try to operate an Excel file using Python (Pandas / XlsxWriter) ②
Paste the image into an excel file using Python's openpyxl
How to read an Excel file (.xlsx) with Pandas [Python]
How to create a CSV dummy file containing Japanese using Faker
How to read a CSV file with Python 2/3
[Python] How to read excel file with pandas
[Python] How to read a csv file (read_csv method of pandas module)
How to convert JSON file to CSV file with Python Pandas
How to convert Json file to CSV format or EXCEL format
Every time I try to read a csv file using pandas, I get a numpy error.
How to create a config file
How to make a string into an array or an array into a string in Python
How to combine all CSVs in a folder into one CSV
How to format a table using Pandas apply, pivot and swaplevel
[Python] How to store a csv file as one-dimensional array data
[Python] How to convert db file to csv
How to draw a graph using Matplotlib
How to convert Python to an exe file
How to install a package using a repository
[Python] How to scrape a local html file and output it as CSV using Beautiful Soup
Create a dataframe from excel using pandas
How to read CSV files in Pandas
Download Pandas DataFrame as a CSV file
How to put a line number at the beginning of a CSV file
How to code a drone using image recognition
How to create a JSON file in Python
I tried reading a CSV file using Python
How to upload to a shared drive using pydrive
How to uninstall a module installed using setup.py
How to read a file in a different directory
[Introduction to Pandas] Read a csv file without a column name and give it a column name
Save an array of numpy to a wav file using the wave module
[Pandas] How to check duplicates and delete duplicates in a table (equivalent to deleting duplicates in Excel)
How to write a GUI using the maya command
How to use Pandas 2
How to set up a Python environment using pyenv
Use Pandas to write only the specified lines of the data frame to an excel file
How to scrape horse racing data using pandas read_html
Backtrader How to import an indicator from another file
Read CSV file: pandas
How to hold a hands-on seminar using Jupyter using docker
[Python] You can save an object to a file by using the pickle module.
How to output CSV of multi-line header with pandas
How to make a Python package using VS Code
How to convert a mel spectrogram back to a wav file
[Python] A memo to write CSV vertically with Pandas
How to deploy a Go application to an ECS instance
How to save a table scraped by python to csv
Type after reading an excel file with pandas read_excel
Python script to create a JSON file from a CSV file
How to execute a command using subprocess in Python
Combine multiple Excel files loaded using pandas into one
How to use NUITKA-Utilities hinted-compilation to easily create an executable file from a Python script
How to create an instance of a particular class from dict using __new__ () in python
How to put a hyperlink to "file: // hogehoge" with sphinx-> pdf
How to convert an array to a dictionary with Python [Application]
How to run a Python file at a Windows 10 command prompt