Teach you how to merge Python CSV into multiple sheet worksheets

Time:2022-6-17

target

Combine multiple CSV files into one excel file and multiple sheet worksheets.

preface

Most methods on the Internet are to use CSVDirect ConsolidationTogether, the sheet tables are not created separately.
Other answers say that CSV does not support merging into multiple sheet tables.

Teach you how to merge Python CSV into multiple sheet worksheets

There are macro commands on the Internet. I tried, but I can onlyImport a sheet table。 Python is also useful. Most of them are useless.

Despite many difficulties, we finally achieved our goal by using the pandas library.

 

start

The following code uses two CSV files with data. (2019-04-01.csv and 2019-04-02.csv)

import pandas as pd
writer = pd.ExcelWriter('test.xlsx')
data1 = pd.read_csv("2019-04-01.csv", encoding="gbk")
data2 = pd.read_csv("2019-04-02.csv", encoding="gbk")
data1.to_excel(writer,sheet_name='2019-04-01')
data2.to_excel(writer,sheet_name='2019-04-02')
writer.save()

The first step is to import the pandas library.

Then you need to use pandas read_ CSV create one for each CSVdataframe

With dataframe, you can turn it into a table in Excel. Last save.

The above code is 2019-04-01 CSV and 2019-04-02 CSV import to test In the xlsx table, create two sheet worksheets, 2019-04-01 and 2019-04-02, respectively.

 

function

Open test xlsx。 The effect is as follows.

Teach you how to merge Python CSV into multiple sheet worksheets

 

beautify

Although the goal has been achieved, the first column is abnormal. One more columnLine number

Teach you how to merge Python CSV into multiple sheet worksheets

So it needs to be modified to remove the row number column. The method is very simple. Add a parameterindex_col=0

data1 = pd.read_csv("2019-04-01.csv", encoding="gbk",index_col=0)
data2 = pd.read_csv("2019-04-02.csv", encoding="gbk",index_col=0)

Delete the test xlsx。 Run it again. The effect is as follows:

Teach you how to merge Python CSV into multiple sheet worksheets

Perfect solution!

 

supplement

In more cases, we don’t want to enter file names one by one. Instead, all the CSV files to be processedPut in a folder。 Let Python automatically read these CSV files and create an excel file, andAutomatically use file name as sheetImport into an excel file.

code:

import pandas as pd

This is the end of this article about merging Python CSV into multiple sheet worksheets. For more information about merging Python CSV into sheet worksheets, please search for previous articles on developepper or continue to browse the following articles. I hope you will support developepper in the future!