View trend line of 2 fields from multiple CSV files

I have no idea how to do this. I don't even know if my approach here is correct. I'm open to other solutions.

I have multiple CSV files. See attachments. The goal is to make an excel trend chart to see folder size changes of the DirectorySize field of each CSV.

Is there a way to make a trend line of each row's Path and DirectorySize fields from multiple CSV files?
So, I'd see something like:
Path, DirectorySize_2017_05_30, DirectorySize_2017_08_08, DirectorySize_2017_10_23, DirectorySize_2017_11_05

Open in new window

2017_05_30_Public_home_share.csv
2017_08_08_Public_home_share.csv
2017_10_23_Public_home_share.csv
2017_11_05_Public_home_share.csv
LVL 27
NVITEnd-user supportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Try attached file.
There are 3 sheets, Csv, Data and Pivot.

Csv: The csv import data.
Only Path and Directory size are imported.

Data: Data from all csv files, with the file date from the file name added in a column.
Some of the paths for the same user start with "d:\public\home\share\" and some with "d:\dir\share\".
That is removed from the paths.

Pivot: The result with a table and a chart.
You can filter on Path or Date, either in the table or the chart.

Sheet Csv and Data can be hidden.

Accept macros on file open, and press the update button to run.
Select the folder with the csv files.
If that is always the same folder, it can be changed to a fixed path and no folder selection.
Trend-from-csv-data.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NVITEnd-user supportAuthor Commented:
I appreciate your help, Ejgil.
I also love how the "Update from CSV Files" button, which lets me pick the CSV file. Then it redraws the chart.

Still...

Maybe I don't know how to use Excel.
Maybe I am not stating my need correctly.
Maybe the need is too complex.
I don't know.

The pivot doesn't show as I would like. I attached what it currently looks like.Current stateEverything looks the same for each date.

I would like to see the top 10% folders that are increasing the fastest for the whole date range.
Ejgil HedegaardCommented:
You should not have the date as the first column, then the chart is impossible to read, and everything looks the same.
It must be the folder, then you can see the change for each folder.

Try attached, where only folders with increase are shown, up to 10% of the number of folders, rounded up, so here with 23 folders, it is 3.

I have added a 2 digit number in front of folder name, to make the pivot table display the folders in descending order for the change.
Trend-from-csv-data.xlsm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.