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.
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.
beautify
Although the goal has been achieved, the first column is abnormal. One more columnLine number。
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:
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!