Follow
Lucid Gen
  • Marketing
    • Facebook
    • Google
    • Zalo
    • Content marketing
    • Email Marketing
    • Marketing tools
    • SEO
  • Website
    • WordPress
  • Technology
    • Computer
    • Phone
  • Life
No Result
View All Result
Lucid Gen
  • Marketing
    • Facebook
    • Google
    • Zalo
    • Content marketing
    • Email Marketing
    • Marketing tools
    • SEO
  • Website
    • WordPress
  • Technology
    • Computer
    • Phone
  • Life
No Result
View All Result
No Result
View All Result
Lucid Gen

Lucid Gen › Technology › Computer › How to merge multiple Excel CSV files into one

How to merge multiple Excel CSV files into one

Đánh giá bài viết
06/08/2021
4
Cách gộp nhiều file Excel & CSV thành 1 sheet

Cách gộp nhiều file Excel & CSV thành 1 sheet

122
SHARES
12.2k
VIEWS
Share to Facebook

Merge multiple Excel files into one and multiple CSV files into one file only take 1 minute when you have mastered following the instructions of this article. This article Lucid Gen has separate instructions for each operating system (Windows and macOS). And yet, I also add a bonus trick to help you combine the total data by horizontal and vertical titles. Let’s follow along!

Related posts

  • How to increase mouse tracking speed Mac
  • How to download Power BI for Mac and Windows
  • How to uninstall apps on Mac completely
Table of contents
  1. Merge multiple Excel files into one
  2. Merge multiple CSV files into one on Mac
  3. Merge Excel and CSV files online
  4. How to combine data by column (extra bonus)
  5. Epilogue

Merge multiple Excel files into one

Please follow the instructions below to merge multiple Excel or CSV files into one. This method only applies to Windows computers.

Step 1: You put all Excel or CSV files into a folder like this. Remember that the folder cannot contain other types of files.

Put all Excel or CSV files that need to be merged into one folder
Put all Excel or CSV files that need to be merged into one folder

Step 2: You open the Excel software and click in this order.

Data > New Query > From File > From Folder

Merge multiple Excel files into 1 - Merge CSV files - Merge Excel files online - Import data from From Folder
Import data from From Folder

Step 3: You click the Browse. Select the folder containing the Excel or CSV files you want to merge.

Click Browse... and select the folder containing the Excel or CSV files to merge
Click Browse… and select the folder containing the Excel or CSV files to merge

Step 4: You click Combine & Load.

How to merge multiple Excel CSV files into one - Click Combine & Load
Click Combine & Load

Step 5: Click OK to merge multiple Excel files into one.

How to merge multiple Excel CSV files into one - Click OK to proceed to merge multiple Excel files into one
Click OK to proceed to merge multiple Excel files into one

This is the result after merging the Excel file, you need to turn off the Workbook table on the right and delete the extra column (column A), and you’re done.

How to merge multiple Excel CSV files into one - Results after merging Excel files
Results after merging Excel files

How to handle the error cannot be merged due to hidden files in the directory:

As I said in step 1, the folder must not contain other files to avoid errors. In case your folder has hidden files, you can handle it in 2 ways:

  1. You create a new folder, and copy files over to the new folder.
  2. You open the option to view hidden files according to the instructions below and delete the hidden file.

In the menu of the folder, you click View and select Options.

How to merge multiple Excel CSV files into one - Click View and select Options
Click View and select Options

The Folder Options window appears, click the View tab, then turn on Show hidden files, folders, and drives and then click Apply and OK.

How to merge multiple Excel CSV files into one - Turn on Show hidden files, folders, and drives
Turn on Show hidden files, folders, and drives

Merge multiple CSV files into one on Mac

Currently on Mac or macOS, there is only a way to merge multiple CSV files into one, but the XLSX file is not. To merge CSV files, follow these instructions:

Step 1: You put all the CSV files into a folder, then you click the wheel button and select Copy “folder name” as a pathname.

Merge multiple CSV files into one on Mac - Click the wheel button and select Copy "folder name" as pathname
Click the wheel button and select Copy “folder name” as a pathname

Step 2: You press Command and Space and then find and open the Terminal application available on the device.

Merge multiple CSV files into one on Mac - Open the Terminal app
Open the Terminal app

Step 3: Type the command below into Terminal.

pwd
Merge multiple CSV files into one on Mac -  Type pwd into Terminal
Type pwd into Terminal

Step 4: Type cd and paste the directory path that we copied in step 1.

cd [Đường dẫn thư mục chứa các file lúc nãy đã sao chép]
Merge multiple CSV files into one on Mac - Type cd with the directory path containing the CSV file to merge
Type cd with the directory path containing the CSV file to merge

Step 5: Enter the command below into Terminal to merge multiple CSV files into one. Note you can rename lucidgen.csv to tencuaban.csv

cat *.csv >lucidgen.csv
Merge multiple CSV files into one on Mac - Type the command merge multiple CSV files into one
Type the command merge multiple CSV files into one

Very professional like IT people, right? After a few lines of command, the CSV file is already merged.

Merge multiple CSV files into one on Mac - The merged CSV file will be in the folder
The merged CSV file will be in the folder

How to remove redundant headlines after merging:

Since this method merges the file’s entire contents, it will not remove the header line in the file. You can get rid of the superfluous headlines according to your instructions.

First, you select the columns and then Filter them (Data > Filter). Then you click the Filter button in any column. As shown below, I circled the small button with the down arrow.

Merge multiple CSV files into one on Mac - Select the columns then Filter it (Data > Filter)
Please select the columns, then Filter them (Data > Filter)

The Filter window appears. You filter out the redundant title line and then drag it to the row of numbers, right-click and delete it.

Merge multiple CSV files into one on Mac - Filter out redundant headlines and delete them xóa
Filter out redundant headlines and delete them xóa

This is the final and most complete result of merging multiple CSV files on Mac.

Merge multiple CSV files into one on Mac - Final result when merging CSV files on Mac
The final result when merging CSV files on Mac

Merge Excel and CSV files online

If you want to make it even simpler, you can use online tools to merge Excel and CSV files. However, if the file has Vietnamese data, it is very easy to error into strange characters.

  • Merge Excel files online: Link
  • Merge CSV files online: Link

The way to use the online Excel and CSV file merge tools is the same, they differ from the website interface, so I don’t guide with specific pictures. You need to upload the file and wait for the merge to finish, then download it.

How to combine data by column (extra bonus)

This is just a bonus, and I think you will need it. Like in this case, I run Facebook ads with multiple accounts, so I want to calculate the total number of each day.

For example: on 10/06/2020, there are 5 accounts running Facebook ads, output 5 files with date 10/06/2020, now I have to combine all data of 5 accounts on 10/06/2020 again.

Step 1: After merging the Excel or CSV file, click on an empty cell to make the cell that will receive the aggregated data table (like I chose G1), then you click Data and select Consolidate.

Click Data and select Consolidate
Click Data and select Consolidate

Step 2: You tick Top row and Left row to combine by header above and left. In the Reference box, select the entire data area, including the title.

Select 2 lable cells and drag to select the data container
Select 2 cell labels and drag to select the data container

Step 3: The date header column does not display the correct format, and you need to format the date column to Short Date.

Format the date column again
Format the date column again

That’s it, and you don’t need to sit and type commands or click plus each cell anymore.

Aggregated data by title
Aggregated data by title

Epilogue

No matter what field you work in, I believe this article will help you a lot if you have touched on reporting data. Merge multiple Excel files into one, multiple CSV files into one file, merge Excel and CSV files online, and a bonus guide to merge data by title (label). Did you solve your Excel problem? Ask questions in the comments below!

Đánh giá bài viết
Share49Pin11Share9
Next Post
Cách dùng Authenticator trên điện thoại và máy tính

How to use Google Authenticator on PC

Cách loại trừ tất cả ứng dụng khỏi quảng cáo hiển thị GDN - How to exclude apps in Google Display Network

How to exclude apps in Google Display Network

Comments 4

  1. Nguyenthoa Top fan
    2 years ago

    Cám ơn b, Minh Hiếu. Vì máy của mình không có phần New Query nên mình không làm Offline được. Cám ơn b nhiều nhé!

    Reply
    • Minh Khuê Top fan
      2 years ago

      Không có gì Thoa ơi ^_^

      Reply
  2. Nguyenthoa Top fan
    2 years ago

    Cách hợp nhất Online chỉ cho phép ghép tối đa 10 file thôi hả add. Có cách nào hợp nhất nhiều file hơn kg b? Chỉ giúp mình với nhé. Cám ơn b.

    Reply
    • Minh Khuê Top fan
      2 years ago

      Chào Thoa, mấy trang online người ta làm vậy để không bị mất quá nhiều dung lượng tạm thời cho mỗi người dùng đó. Muốn gộp thật nhiều file thì tốt nhất nên làm Offline. Còn online bạn phải mất công làm nhiều lần thôi à.

      Reply

Leave a Reply Cancel reply

I will review and reply to all comments within the day. Please feel free to leave your comments on this article!

Your email address will not be published. Required fields are marked *

Recommend for you

  • Trending
  • Latest
Hướng dẫn thêm hiệu ứng bóng lướt qua cho logo web - How to add light sweep effect to website logo with CSS

How to add light sweep effect to website logo with CSS

06/08/2021
0
Cài SSL cho website WordPress chuyển HTTP sang HTTPS - How to add SSL to website and redirect HTTP to HTTPS

How to add SSL to website and redirect HTTP to HTTPS

23/07/2021
0
Cách kiểm tra ngày kích hoạt iPhone & Macbook - How to check iPhone and Macbook activation date

How to check iPhone and Macbook activation date

15/07/2021
0
Cách phân quyền Google Analytics và Tag Manager - How to add users to Google Analytics and Tag Manager

How to add users to Google Analytics and Tag Manager

28/07/2021
0
Cách chỉnh tốc độ chuột MacBook, Magic Mouse, Logitech trên MacBook

How to increase mouse tracking speed Mac

14/05/2022
0
Cách cài đặt Power BI trên Mac và Windows | How to download Power BI for Mac and Windows

How to download Power BI for Mac and Windows

15/10/2021
0
Cài đặt tracking chuyển đổi Contact Form 7 How to set up Contact Form 7 tracking

How to set up Contact Form 7 tracking

08/10/2021
0
Cách gỡ ứng dụng trên Mac và dọn dẹp sạch sẽ - How to uninstall apps on Mac completely

How to uninstall apps on Mac completely

08/10/2021
0

Lucid Gen

A blog sharing about digital marketing, miscellaneous about technology and what you might need that the author knows.

Developed by blogger
Minh Khue › Donate

228 Posts and 4146 Comments

Disclosure: This website has advertisements. If you don’t have money, how can you write?

 Google Play  Microsoft Store

Recent Comments

  • Minh Khuê on How to download Adobe on Mac for free for life
  • mm on How to download Adobe on Mac for free for life
  • Minh Khuê on How to download Adobe on Mac for free for life
  • danny on How to download Adobe on Mac for free for life
  • Elle Ambrose on How to uninstall Microsoft AutoUpdate on Mac

Image sources

Lucid Gen edits images from the following sources: Freepik, Unsplash & Pixabay.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

  • About
  • Contact
  • Terms and policies

© 2019 Lucid Gen with by Minh Khue DMCAProtected

No Result
View All Result
  • Marketing
    • Facebook
    • Google
    • Zalo
    • Content marketing
    • Email Marketing
    • Marketing tools
    • SEO
  • Website
    • WordPress
  • Technology
    • Computer
    • Phone
  • Life

© 2019 Lucid Gen with by Minh Khue DMCAProtected