• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Combine files with different header names|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Combine files with different header names|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCombine files with different header…
sp_PrintTopic sp_TopicIcon
Combine files with different header names
Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
1
December 22, 2020 - 12:09 am
sp_Permalink sp_Print

Hi all,

I am sure I have done this before but can't remember how

I have a folder with csv files that I need to combine, they contain sales forcasts for the next 14 days so everyday a new csv file is generated with columns for the next 14 days..

We keep the old data to refer back to and also the number of products can vary month to month and the file date is above the headers. I know how to extract that its dealing with the different header names that I am stuck on.

Ive attached some example files so you can see what I need.

 

John

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
December 23, 2020 - 2:16 am
sp_Permalink sp_Print

Hi John,

Try this solution: https://www.myonlinetraininghu.....ng-content

Avatar
Duncan Williamson
Member
Members
Level 0
Forum Posts: 11
Member Since:
December 23, 2020
sp_UserOfflineSmall Offline
3
December 24, 2020 - 12:03 am
sp_Permalink sp_Print

Firstly, John, remove that first row in every testx.csv files because it is redundant as it seems the entry in row 2, column B relates to the date give in row 1rnThat has the benefit of allowing Query to use the new first row as the header row for each file.

I then agree with Mynda that you need to create a separate Query for each .csv file ... no big deal in this example.

Then once you have Appended your files, you can unpivot the data, Close & Load it and create a Pivot Table very easily now and analyse your data. 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
December 24, 2020 - 12:32 am
sp_Permalink sp_Print sp_EditHistory

1.jpgImage Enlarger

Using the File attached, any csv files can be combined, no matter how different their headers are.

Of course, like Duncan said, first row is irrelevant and must be removed. The image attached shows how the result looks like.

However, this demonstrates that you can combine from folder, without having to create a query for each source file.

sp_PlupAttachments Attachments
  • sp_PlupImage 1.jpg (28 KB)
Avatar
Duncan Williamson
Member
Members
Level 0
Forum Posts: 11
Member Since:
December 23, 2020
sp_UserOfflineSmall Offline
5
December 24, 2020 - 6:16 am
sp_Permalink sp_Print

Thanks Catalin

I did take a look at your Get CSV function earlier but when I tried to get it to import all three csv files at once, it asked me to give it a file path rather than a folder path. That led to three files, three queries ... did I use the function wrongly?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
December 24, 2020 - 1:55 pm
sp_Permalink sp_Print

@Duncan:

If you tried to open the function, you actually invoke the function and that indeed asks for a file path.

The file is supposed to be used as is, the only change you need to do is to change the FOLDER path from settings sheet. Then you refresh the query, data will be extracted from ALL csv files from that folder with a single query.

If you want to look at the GetCSV function, avoid opening it directly, this action invokes the function and creates a new query. Instead, open another query, from the queries list on the left select the function and open the Advanced Editor, it's the only way to see the code without invoking the function.

12.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage 12.jpg (27 KB)
Avatar
Duncan Williamson
Member
Members
Level 0
Forum Posts: 11
Member Since:
December 23, 2020
sp_UserOfflineSmall Offline
7
December 24, 2020 - 11:36 pm
sp_Permalink sp_Print

Thanks again, Catalin,

I read the file more carefully this time and pasted my own folder path into the first sheet and it worked as you designed it.

I know the Advanced Editor, thanks, even though I will not say I am an M expert at all. I have installed a couple of other functions before. 

I tried an example of my own in your file but I think I have found that your function does not pick up the column headers from those csv files ... I will look at your code after Christmas Day and see what I might suggest ... either because of my file or because of the code.

Merry Christmas if you celebrate it!

Duncan

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
December 25, 2020 - 12:01 am
sp_Permalink sp_Print

Hi Duncan,
The function is adjusted for this topic, John's test files have the headers AFTER the first row. You have to remove the line in GetCSV function that removes the first row.
The code for this scenario is:

(FilePath)=>
let

#"Imported CSV" = Csv.Document(File.Contents(FilePath),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
Skip = Table.Skip(#"Imported CSV",1),
PromoteHeaders = Table.PromoteHeaders(Skip, [PromoteAllScalars=true])

in
PromoteHeaders

 

For a normal csv, the code should not have the Skip first row step:

(FilePath)=>
let

#"Imported CSV" = Csv.Document(File.Contents(FilePath),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
PromoteHeaders = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])

in
PromoteHeaders

Avatar
Duncan Williamson
Member
Members
Level 0
Forum Posts: 11
Member Since:
December 23, 2020
sp_UserOfflineSmall Offline
9
December 26, 2020 - 9:12 am
sp_Permalink sp_Print

There you are! Solved.

Thanks, Catalin, I will be using that function from now on!

 

Duncan

Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
10
December 30, 2020 - 12:40 am
sp_Permalink sp_Print

Hi Guys,

Thanks for all the replies so far but one key thing is missing the date in the first row isn't irrelevant.

If you look at the result csv I need to filter by the product and that date, it basically tracks forecasts for example

on the 1st it was forecast that on the 3rd abc would require 1 unit

but on the 2nd the forecast for the 3rd had changed to 2 units

date product 01/01/21 02/01/21 03/01/21 04/01/21 05/01/21 06/01/21 07/01/21
01/01/21 abc 1 1 1 1 1    
02/01/21 abc   2 2 2 2 2  

so I need to keep that date in the first row linked to each product in that csv and of course durin a year that will end up at 365 csv's which is why I was trying to avoid a query for each csv file.

at the moment I am looking at 2 querys one to pull the first line date linked to a id based on csv name and a second to do as in the GetCsv function then merge based on the csv name

regards

John

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
December 30, 2020 - 6:46 pm
sp_Permalink sp_Print sp_EditHistory

If you need to keep the first value, you can add it as a new column. This is how the function should look like:

(I collected the first cell value in a parameter before removing the first row, then added the value in a new column)

(FilePath)=>
let
Source = Csv.Document(File.Contents(FilePath),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
FirstCellValue = Table.ToColumns(Source){0}{0},
Skip = Table.Skip(Source,1),
PromoteHeaders = Table.PromoteHeaders(Skip, [PromoteAllScalars=true]),
AddColumn = Table.AddColumn(PromoteHeaders, "Date", each FirstCellValue)
in
AddColumn

A normal query , will look like:

let
Source = Csv.Document(File.Contents("C:\CsvFile.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
FirstCellValue = Table.ToColumns(Source){0}{0},
Skip = Table.Skip(Source,1),
PromoteHeaders = Table.PromoteHeaders(Skip, [PromoteAllScalars=true]),
AddColumn = Table.AddColumn(PromoteHeaders, "Date", each FirstCellValue)
in
AddColumn

The only difference between the function and this query is that the first line disappears. That's what converts any normal query to a function.
Because the file path will no longer be provided, hard type the sample file path:
File.Contents("C:\CsvFile.csv")

This will allow you to view the steps, add more processing if needed.

After you are done with converting one of the csv files, convert the query back to a function.

This is the best way to build a function. Start with a simple query from one of the files, then convert the query into a function by adding the first row with one or more parameters as needed, building such a custom function is very easy.

sp_AnswersTopicAnswer
Answers Post
Avatar
Duncan Williamson
Member
Members
Level 0
Forum Posts: 11
Member Since:
December 23, 2020
sp_UserOfflineSmall Offline
12
December 31, 2020 - 8:22 am
sp_Permalink sp_Print

I understand the points you make John but I still wonder about that first line. As far as I can tell, the date in cell B2 is exactly the same date that is in cell B3 of all of your files. That is why I said that first line is redundant and it would make your life a little easier if you were to work with cell B3 and remove that first line. Otherwise, Catalin has your problem in hand, I think!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Scott Miller, Roy Lutke, Jeff Krueger, Kylara Papenfuss, Ivica Cvetkovski
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.