How to combine multiple tables with different headings in Excel between many files that ca

Good evening.

I would like to try to combine totally different tables that will eventually be present in the various files contained in a single folder. The table that will be created will consist of all the different headers, of all the files in the folder.

Regarding the tables, it should be noted that:

1) files may not contain tables;
2) the title of the columns may not match;
3) the tables could have a different size.

The main problem will be to identify, within each file, the presence of tables (set of data sorted by rows and columns).

Basically the table that will be formed will consist of the column headers of all the files taken only once and the data of each table of files to be merged.

I note that I posted a similar request in other forums of which only one user has given me directions.

example of excel file contained in a folder:

https://www.dropbox.com/sh/7mugjqjdx...Em_htv0na?dl=0

links of posts from other forums:


CHANDOO:
https://chandoo.org/forum/threads/qu...-tables.39044/

MREXCEL:
https://www.mrexcel.com/forum/power-...bles-addition- plain-text-precedes-follows-tables.html

Thank you

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

I created some (simplified) example files to explain myself better.

I created files: 1,2,3, 4
File 2 contains only one table, file 4 does not contain a table and files 1 and 3 contain a table and other words I have indicated with "X" (with "x" I have indicated everything that can be: sentences, words , numbers, etc. - that is everything that can be different from a table.

My goal is to create a single table among the many, which contains business data (name, street, phone, email, etc.) that can be found among the different files in a single folder.

To do this I thought (as I wrote in the original post) to add (to the file to be created) all the columns of the tables taken only once (to avoid duplicates of headers). This mode seems almost impossible to achieve ... This is the case of the "sheet 1" of the merge file.

Another way to achieve my goal, could be to create a table with multiple headers (separated by a comma or other). In this way we could aggregate columns that, although containing data of the same type, are indicated with non-identical headers. This is the case of the "sheet 2" of the merge file.
*
Unfortunately, having no knowledge of programming, I can not understand how this operation could be made easier. Most probably the second hypothesis could be more just.

Thank you

First: with my way you'll need PowerQuery add-in

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

Second: I do not know Italian and I did what I could. You have to do the rest yourself
Third: to see quasi result PowerQuery should be installed, then after open merge file..xlsx you need to change path to the source files: PQ Editor - Home - Data Source Settings. If not it will wont work.

Thank you for your intervention.

I changed the path of the folder. I would need to understand what I should do to start it ..


I'll explain to you the part you did not understand:

In the second case (sheet 2 of the merge file), in the file to be merged, I have inserted many similar titles (you will see abbreviations as "cap" for the zip code) separated by a comma. The titles included, are the data of companies that I’m interested to extrapolate from individual files (in addition to others that I will identify later).
You will find:

name, company (this column will include the names of the companies)
address, street (this column will include the addresses)
legal form, type of company
cap, zip code (this column will include the postal codes of companies)
prov, province (this column will include the provinces of business location)
email, e-mail, e-mail (this column will include business emails)
tel, telephone (this column will include the telephone numbers of the companies)
fax (this column will include the fax numbers of the companies)

In this way, when the header of a column of the different files (1,2,3, etc.) is identical or similar to one of the words separated by a comma of the joined file (merge file), the contents of the column will be transferred in the column where there is the correspondence.

While in the first case the merged file will be formed by many columns that could contain data of the same type, that will not be included in the same column only for the fact that the title is not identical (think of a file that has the 'heading' phone 'and another that has the abbreviation "tel"), in the second case i should be able to extrapolate the data that interest me (represented by the headers that I will insert) creating a compact table (with only columns created by me). The negative aspect of the second mode and that I should be good at identifying within each column heading of the merged file (merge file), all the right keywords (separated by a comma) that could be used in all files for represent the same data. In this case I thought that, in order to identify the right keywords, it would be ideal to be able to extract in a single column all the headings of the tables of all the files, delete the duplicates and analyze each single headers.

In addition to writing how to start the query, could you prepare another for the second case?

Furthermore, it is possible to insert in the first column of both solutions, the name of the file to which it belongs, so you can return to the file in case there are problems (I forgot to write it in the previous message "sorry").

Thank you

too much to read...

firstly you need to change (add new if doesn't exist in any query) headers to be the same for all queries

eg.

name file

Denominazione, nome, ditta, societa, impresa

sede, sede legale, indirizzo, via

forma giuridica, forma legale, tipo impresa

cap, codice di avviamento postale

prov, provincia

email,e-mail,e.mail

tel, telefono

fax

to

name file

Denominazione

sede

forma

cap

provincia

e-mail

telefono

fax

then add prefix file1, file2, file3, file4 to the Name column accordigly
then append all query tables into one and load to the sheet

PowerQuery is ETL tool (Extract, Transform, Load) so data is extracted, then you need to transform data to the correct layout then load to the sheet.

PQ is not one-click or copy/paste solution.

but if you want merge these query tables add index to each table then merge by index (it doesn't make sense to me in this case but maybe because of italian there )

Hi Sandy666.

In the message where you attached the file with the query, you did not tell me how to run it. Could you kindly write me?

I would like to better understand what you wanted to tell me about the second situation. Following the first solution the file may not have sufficient columns if the different files to be joined have different headers, so I thought about the second solution. I would not change the headers of the files to be merged in order to perform subsequent checks in case of errors or other. I will have many files (example: 20000) for each State, then in every language.
Do you think the query needs to be changed?

Thank you

not 2000 but 20000 and more.
This is why I proposed the second solution ....
With the second solution, once the query has been identified the presence of a table within a file, it should copy the headers of each column with the keywords I enter in each column heading of the table that will be created.
What do you think about it?

I think you need to decide what you want to achieve
btw. second solution is just merging

yes, a merger that takes into account keywords.

As soon as I get home I'll try both ...

For me the problem was how to identify the presence of tables within a file avoiding other written (it seems that you can only do with power query) and how to align the different headers in order to extract the data that interests me, avoiding to exclude data. The first solution would be convoluted, just taking into account the high number of columns considered the number of files.

Is it possible to extract the column headings of each file in a single column eliminating duplicates? In this way I will be able to better identify the keywords to be assigned to each column ontesting of the table in which the union will take place.

Thank you

Do I need a different query to extract only table headers by arranging them vertically in a column?

General Note: Read me Try to avoid joining to my Black List by doing this below If that takes care of your original question, & to say Thanks and for better Motivation, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. Thank you.

you need whole table form each file then copy/duplicate whatever you want - because I don't understand what are you tryin' to do

Hello. I have the file. Apart from that I have difficulty to start it because even if I indicate the path of the folder, it seems that the files are not detected inside this (an error symbol goes out to the individual files). Fore the version I have of power query is older than yours and that gives me the error.

To avoid creating many columns, as I wrote in previous posts, I asked you if you can extract only data that have headers similar to the key headers inserted in each column of the merge file (only from these columns must be formed the file merge). The example you see in the second sheet of the merge file

Could you fix it?

Thank you

Hi Sandy666.
I will try to explain better by asking you not to consider the posts written so far not to make you confused. Only consider this explanation:


Keep in mind that:

1) I have to download files from the web;
2) files may also not contain tables;
3) the tables can be completely different from each other (also as number of columns and content);
4) tables while containing non-identical headers may contain similar headers (there may be synonyms of headers incorporating same data) - (this point is very important);

I do not care to extract all the columns of each file (I have highlighted, in purple, data that I do not need to make you understand that not all columns are important). For this reason I have prepared a "file merge" table with the data headers that I would like to extract in the files that contain tables.

description of the table in which the data of the files containing the tables will be consolidated (I refer to the merge file):

the first line: contains the headers of the data you want to extract for each file containing a table (note that the headers may vary as type and number and that I have highlighted in orange the synonyms of the contents of the previous cell);

second line: contains the exact translation of the titles written in English (row 1);

lines (from 3 to 6): synonyms or similar terms of the headings in Italian (line 2) - I will create a file for each language by inserting multiple headers.

My idea:

I asked myself how to extract business contacts from many files downloaded from the web.

The answer:

1) I need a macro / query that only considers the files that have tables inside them (very important).

2) After having found a table it is necessary to understand if this table contains the data I need (company name, address, email, etc.).

3) point 2 would be easy to do when all the tables contain identical headings (if in my table I have the header "email" and in the files I have the same headings "email" - I will have an exact match and the content will be transferred to the merge table ).
What happens when the headers do not have to be identical (in my table I have the header "email" while in one of the files "example file 1" I have header "email" while in another file "example file 2" there is the header "e-mail." In this case it would be transferred in the column of emails of the merge file, only the contents of file 1 because the headings are identical while the contents of file 2 are not transferred because the header is not identical.

What I thought:
To avoid data loss I could insert in my table (file merge) the multi headers (those that go from line 2 to 6 for each column). In this way if the header of the files is included among the multi-header the content will be transferred to the table of the merge file (in this way I will not lose any data)

4) for each line I would like to insert in the first column the only name of the file from which the extraction takes place (not even the name of the sheet).

I hope I was clear ... I attach the files to understand what I would like

Then following your instructions I will try to create a macro that extracts the column headers of each file. once extracted I will understand which sno the headers are repeated maggiromente and I will transform the similar headers to make them equal. In this way, as you wrote to me, it will be possible to merge the columns.
we resend

I didn't say that - use VBA
I said - you CAN use VBA if you know what are you doing.
also I said you can do everything in PowerQuery Editor.