• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

question on macro that analyzes and combines files considering only the files with tables.

Good morning.
I would like to ask you a question based on your experiences. I will list a series of points in such a way that you can tell me which of these can be done.

Starting situation: In a folder I have files of different types (Excel, PDF etc. - if it is not possible with different files I could transform pdf files into Excel files, just as I could divide the different files into different folders by type).

Could you tell me which of these points you can do:

1) analyze each file to understand which of these contains a table (we could analyze the structure of the file invading the existence of different columns within them) by deleting files that do not have tables;

2) count how many times a string is repeated within each file containing tables;

3) Combine the different files containing tables. Merge should be done by adding, to the file to be created, the different headers contained in each file (if already exists there is no need to insert the header in the file to be created) and move the table contents in the new table according to the header.
Example:
Table "1" has the following column headers: "a, b, d"
the table "2" the headings: a, c (the heading "a" is the same for both tables)
from the union of these two tables a new file will be created with only one table inside that has the column headers equal to the two tables: "a, b, c, d" and the contents of each table.

Thank you
 

Chihiro

Excel Ninja
Possible for all of above.

1) will require PDF to be in Excel format. As well, table should be "Excel Table", not just data organized in table like structure.

2) Various methods available. But method that should be utilized will depend on actual workbook structure. The most flexible one is Range.Find method.

3) isn't easy without PowerQuery. PQ, by design, will add new column, if header does not exist in table that's being appended to. Will give null value for missing data (column) on either table. Otherwise, you'll need to handle missing and/or extra columns in your code/process.
 
To transform the pdf use this site:
https://www.pdftoexcel.com/

in the case of text files, the transformation can happen with a macro, right?

What is the difference between "Excel Table" and the data organized in a structure similar to a table?

I read that powerquery is a component of excel. Can you use the free version or do you need the paid version? Is PQ used to create the macro or even to use it?

In the end to create such a macro is complex?

I think it would be a useful macro for many ..

Thank you
 

Chihiro

Excel Ninja
Your question is too broad to give specifics.

When programming, I tend to avoid use of web services. Since, you never know when they'll change URL or even close.

Read up on link below for Excel Table structure.
https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

PQ comes as standard feature in Excel 2016. Otherwise you'd need add-in. Add-in for 2010 isn't updated, and may lack features available in current version.

PQ to some extent can be manipulated via VBA, but most process can be automated without use of VBA in PQ.

In the end to create such a macro is complex?
Yes. Especially when data structure isn't well defined. Code must be written in such a way to handle all kinds of exceptions. Not a easy thing to do.

I think it would be a useful macro for many ..
Sure, but I believe in enforcing rules at data entry point rather than spending resources to clean messy data. So to that end, I often create data entry forms and transformation steps, that are specific to my reporting needs. Not the other way around.
 
Hello. Thank you for your reply. In the text of the link you sent me earlier, it states that a table of excel is a combination of data organized by columns and rows (what it should be) then states that it should have other settings like filters etc. For me, a table is the first indication ...
I have to try to download power queries (I can probably use it myself that I do not understand anything about macros).
As for the question on the use of power query I probably will not have understood correctly (because I've never seen it and tried it), or, the translator did not translate in the right way. When using power query, can the macro be used by a user who has no power query?
Thank you

ps.
The translation is not correct regarding the use of the transformation link. I wanted to write to you that I use this link for the transformation:
https://www.pdftoexcel.com/
 

AliGW

Active Member
Please provide cross-post links - I believe you've posted this on at least two other forums. ;)
 
It is necessary to copy and paste the entire link instead of clicking on it. I do not understand why the whole link has not been recognized as a hyperlink.
 

AliGW

Active Member
Marc - you haven't said why you quoted my post. Are you taking issue with it or reinforcing it? Or am I the "idiot" in your signature line?
 

Marc L

Excel Ninja

Dear Ali,

of course it's a reminder which must obviously be respected
whatever his excuse
 

Marc L

Excel Ninja

You're welcome !
A reminder as per this forum rules …

stephano, as the last warning, next time this thread will be closed …
 
Hi Mark.
If you like, you could also contribute to the post. It will surely be appreciated by many others.
Is your strong power query?
 
Google translations often change the meaning of sentences.
I'm not able to program, unfortunately ...
It is a skill to program ... as any art ..
Chiro wrote in previous posts that it is quite difficult to create a similar query, maybe for you it is not ...
As for the cross post, I wrote here the first one. On excelforum, Sandy666, is competent enough and willing to help me.
 

AliGW

Active Member
But there is another forum where you have asked the question, and you haven't posted the link to it here. Why don't you?
 

Chihiro

Excel Ninja
Yes, I did write it is difficult, since you had wide open condition ;)

At any rate, you got solution from sandy666. Always remember the advice you got, post clear and concise requirement/question, along with sample data and expected output.

And the cross-posting rule.

This will get you higher quality solution(s) faster.
 
Thank you.
It can be difficult to understand how to set a request when you do not know the different ways in which you can achieve the results.
 

Chihiro

Excel Ninja
Detail your manual process of combining file(s)/tables. Clearly demonstrate how you get from data to output/result. Note your Excel version. Then, we can deduce what tools you have access to (typically) and what methods may best suite your need.
 

Marc L

Excel Ninja
It is a skill to program ...
Before creating any thread, at least think about your need
and only once you understand it, clearly explain it
as you were yet several times warned in your previous threads …

1) analyze each file to understand which of these contains a table (we could analyze the structure of the file invading the existence of different columns within them) by deleting files that do not have tables;
Even if you do not know how to code
- as children learn basics with few days, using the Macro Recorder -
you must first analyze how to in an Excel way and share your logic
in order we must know you understand your need !
And on technical points not in a general view …

So analyze your need and explain at least
what is your strategy to find out a workbook has a "table".

Of course after adding the missing expected link ! As per forum rules …

No clever analyze, no code as few have time or any will
to decypher your mind as here it's far not a mind reader forum
neither a do-my-job-in-order-I-get-the-university-chair.com !
As it's just about thinking, so working, sharing instead of just grabbing.

« If you can’t explain it simply, you don’t understand it well enough … »
Albert Einstein
 
Top