Forum Discussion
extract data from double line headers, multiple files on folder, unmatching columns
I have a folder with a lot of CSV files like the table below.
in each file there is a different list of companies.
in some files there is only one company, in another files I get up to 93 companies. each company in a new row.
items and dates repeats a lot on each row.
each month a new CSV will be created. this is why I need to load the whole folder instead of single files.
when I load the folder, company column from first file does not match any other file.
I need somehow to get a list of all companies and their IDs. I am looking for your help.
if there is another topic on this issue, please link it.
Yes, it will be future proof. Please check out this video:
10 Replies
You need 2 queries:
1) Company-table: Keep first 2 rows -> delete first 2 columns -> transpose table
2) Transaction-table: Delete 1st row -> Promote Headers -> Select first 2 columns (item and date) -> Unpivot other columns
I understand what you say, but your idea works only for single files.
I am loading folders.
All the files in the folder contain the same double header, and none of the companies names match.
in each file there is a different list of companies.
in some files there is only one company, in another files I get up to 93 companies. each company in a new row.
You would need to transform these queries into functions.
Then create 2 custom columns where you call each of these functions.
In a 3rd custom column you can perform the merge / lookup of company names and then you can expand that column.
Delete all other column before and you will get one table with all data in it.