Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to merge Python CSV into multiple sheet worksheets". In daily operation, I believe that many people have doubts about how to merge Python CSV into multiple sheet worksheets. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to merge Python CSV into multiple sheet worksheets". Next, please follow the editor to study!

Most of the methods on the Internet are to merge csv directly together without creating sheet tables separately.

Other answers say that CSV does not support merging into multiple sheet tables.

Online useful macro commands, I tried, but can only import one sheet table. Some also use python, most of which are useless.

Despite the difficulties, the goal was finally achieved 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 pdwriter = 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.

You then need to create a dataframe for each csv using pandas.read_csv.

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

The above code is to import 2019-04-01.csv and 2019-04-02.csv into the test.xlsx table and create two sheet worksheets for each of them.

Running

Open test.xlsx after running. The effect is as follows.

Beautification

Although the goal has been achieved, the first column is not normal. There is an extra line number.

So you still need to modify it so that the line number column is removed. The method is very simple. Add a parameter index_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, you put all the csv files you want to work with in one folder. Have python automatically read these csv files, create an Excel file, and automatically import the file name into the Excel file as sheet.

Code:

Import pandas as pdimport os newdir ='G:\ programming code\ python code\ table\\ new'list = os.listdir (newdir) # list all the directories and files under the folder writer = pd.ExcelWriter ('steps .xlsx') for i in range (0rem len (list)): data = pd.read_csv (encoding= "gbk", index_col=0) data.to_excel (writer, sheet_name= list [I]) writer.save () so far The study on "how to merge Python CSV into multiple sheet worksheets" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.