CODEWITHSUNDEEP
excelvbacsvpowerquerym
Ethan Rævan
Ethan Rævan

Reputation: 333

How can I combine multiple CSV files with different column names and column order in Excel?

I have 350+ CSV files I need to merge into one aggregate file. Using Power Query for Excel, I was able to easily merge all of the files in a few minutes. However, after inspection, not all of the files follow the same column order or name. For example, some files go by:

first, last, address, city, state, username

But some other files goes by:

address1, address2, city, state, last_name, first_name, age

Using Power Query, Excel, VBA, or something else - is there a way to effectively merge multiple multiple CSV files with different column names and column order? I tried searching online, but none of the solutions are related to what I was looking for (example).

The end result with the example above should look like this:

username, last_name, first_name, age, address1, address2, city, state

Any extra columns that don't exist in one sheet can be blank (or null) and the finalized column order doesn't matter because that can be rearranged later.

Upvotes: 2

Views: 5666

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Similar to this related post, the Table.Combine function should do the trick, assuming you have headers to match columns with.

Load the CSV files in via Load from Folder, convert CSV to table format and promote headers, then combine with Table.Combine.

let
    Source = Folder.Files("C:\Path\To\Folder\With\CSVs"),
    #"Added Custom" = Table.AddColumn(Source, "CSV", each Table.PromoteHeaders(Csv.Document([Content]))),
    #"Combine CSVs" = Table.Combine(#"Added Custom"[CSV])
in
    #"Combine CSVs"

Upvotes: 4

Related Questions