Automatic Update of Excel sheet based on several CSVs in a folder

I have a couple of questions. Is the page that you want to update Datas? Are all the CSV files of the same format? is there a way to tell what date they represent (is the date included in the data)?

Could you post a sample or two CSV files?

I'm looking at the four CSV files. Are these the only formats we'll see. I notice that two of them have the same format.

It looks like your week begins on a Monday. So the CSV files were run yesterday (Aug 6th) and are for the week starting Jul 30. So you want me to find the lines of data in the CSV file for Jul 30 and append them to the data on which sheet (Dats of Last Week Data or both)?

I'll need some help matching the CSV column headings to the headers on the destination sheet(s). See sheet 1 in the attachment.

I still need an answer as to which sheet to update.

I notice you have 169 named ranges. It appears that the main reason for most of the names is to simplify the formulas by using the named range instead of column letters. I suggest that you consider converting the ranges into Excel Tables. In this case, tables have two advantages:
1. You can use the column header names in formulas and VB Code which makes them easier to understand and debug.
2. Tables know how big they are. So instead of using a whole column like BT:BT you can use [Contest PV] and it will cover exactly the number of rows that you need.

For more information on tables, look at this wiki: http://www.utteraccess.com/wiki/Tables_in_Excel.

I have enough to get started. At least I can set it up to read the CSV files and tell what type they are so I can parcel out the data.

You also have 57 connections to outside data sources. These connections seem to be date stamped. Do you add them weekly?

I am wondering if we can simplify the workbook somehow so you don't have as many connections and named ranges.

Also, I need a sample Referrers Chart Visit Referrals CSV file.

OK, that's sort of what I have. I have it updating the Last Week Data tab, but I can come up with an add-on macro to sandwich it into the Datas Tab.

I am assuming that what is before the square brackets in the map is the CSV File name. If so, I'd like a sample of each file. Given the moderately large number of files, I might change the way I read them and use the map. This will make it possible for you to modify the report if you add columns without having to change any code. To the extent that I can, I like to have things driven by data rather than coding it in. It takes a bit more up front effort, but pays off in maintainability.

You expect me to remember something from last week!? . I think I remember what I did here. I'll work on it when I get a break

This is what I have so far.

On the Datas sheet, I moved the summary information to the top of the page and froze the panes. I think this makes for a better display. The "bottom line" sometimes looks better on the top. Also it eliminates trying to move this information around to add a last row to the data.

Also, since the data is in a table, could you consider dropping some of the named ranges in favor of table references?

I created a map sheet with two helper pivot tables.

The first pivot table is a unique list of the file names so I only have to process each file only once. Once I process a file, it is moved to the processed directory so it is not processed again.

Cell I1 is the "Working File" name. It is used to identify the file the code is currently working on and sets the True / False for the Helper Column called Match File. This, in turn, is used to get a list of the Headers in the dashboard that are populated by that file.

Column L has comments. The code checks to see if the file exists, the column in the file is there and the specified date is also in the file. If there is an error, a comment is written and the file is skipped. So check that these all say, File Processed OK.

Last Week Data, I made a table of this and there were references to external sources. I overrode those and might have broken something in the process. My intention is to add in the formulas to this row of data so it can be copied / pasted in its entirety. Let me know what information should be coming in from the external links and I will come up with a way to fill them in.

The last steps are to fix the Last Week Data Sheet and to copy / paste that row to the end of the data.

When I ran the application against the files you sent, I had one file left over: 2018-08-08-083354.Categories.Top.Chart.KudosGiven.2018-07-02-2018-08-05. Should this be in the map somewhere?

I only did an "eyeball" QA on this. Please check that the information in the files you gave me wound up in the correct slots.

I'll take the next steps when you tell me what to do with the last week's data sheet.

Go to the control panel sheet. Type in the path name to where you have the CSV files in Cell B1. In Cell B2, type in the path name where you want the program to put the CSV files when it is done processing them. Make sure both paths exist.

Pay attention to Cell B7. The program computes the Monday date on which you want to report. If you need to report a different Monday fill in Cell B7. Otherwise leave it blank to get the default Monday. I had it filled in to test with the data you provided.

Then run the macro called GetData. Go to View -> Macros. You can use the options to map this to a Control Key such as CTRL-s (I use CTRL-s a lot because it is sort of "obsolete" - it is meant to stop the scroll of text being streamed to the window. Other control keys have more current meanings.)

Otherwise, I can give you a version with a button on the Control Panel to run the macro.

The way it is designed now is to get only the latest week's data.

I could give you a range on the Control Panel sheet where you get to select the dates to import. This will take a bit more programming in that I will have to throw out data that already exists for the dates you specify. If this is OK with you, then I can give it a shot.

Actually, no, but I should have time to look at it this week. You are in my queue but don't hesitate to ping me again if you don't get back to you this week.

I think I might have this working.

Go to the control panel set the directories and add the new dates.

My apologies. I'm in the midst of a major overhaul of one of my reports. It looks like I can cut the time down from about 4 and a half hours to about 10 minutes.

Also, I am expecting a number of end-of-month reports that I must audit. This will happen either today or tomorrow.

I will have to re-visit the requirement to get back to the point where I left off. I will put this on my calendar for later this week.

I'm afraid not. I had cataract surgery on Monday. It was routine and I am doing fine, but it has put me behind schedule. I'll have to get to it the middle of next week.

I am just getting back to this issue now. The light bulb is flickering and I recall that I will have to set up some directories and re-download the CSV files. This will take a bit of time.

It looks like the new input data has month start dates instead of week start dates. So I will need some new data to reflect this. On the control panel, I can give you drop-downs for month and year. I will compute the start and end dates from this. It will also get around some regional settings issues. I will assume that I can limit Year to current year and the immediate past year. I'll do data checks to assure you don't start after you end .

I think I see the parts of the code where I compute weekly dates. I can make substitutions to compute monthly dates.

Please post new source CSV files. Zip them up and attach them.

I am not using the Last Week Data tab.

Ok, you threw another change in here that causes me to go back to the drawing board. I was expecting the source file names to remain the same. E.G. Traffic.Chart.Visits.csv, now this file is called 2018-12-10-105203.Traffic.Chart.Visits.2018-07-01-2018-11-30.csv.

I need to know what the naming conventions now are. I will now have to get a list of files before processing them and then compare them to the list of expected files to see if the pattern matches.

OK, I have this going. What I don't have is all the formulas in rows 2:11 - There were circular reference errors. I'll have to go back to the original and look at them. I think I can basically "clone" the formulas I do have.

On the control panel, enter in the month and years you want to track. I added some helper cells and conditional formatting to help with this. You will get a warning in cells D2 and D3 if you try to enter an end date less than the start date. Cells B6:D6 calculate the next expected date. You will get a warning in cells B2:C2 if the start date doesn't match this. You do NOT want to overlap dates. That is, if you have Oct 2018 already in the data, you do not want to import it again.

I'm not following the question. Yes, there are some blanks in the data because the files to populate them were not provided.

I can see a weakness in the design. All the files need to be processed at the same time. I can think of a two ways around this:

1. Do a sweep of the files in the inbound directory and warn you if any files are missing.

2. Write the data to a database and compile it as files are read, then use formulas on the datas sheet to fill in the data. This is a more robust solution, and although it might take a rewrite of the code, it isn't that difficult to do.

I am going with option #2 above. This should take care of the issue. I have the basic program already running, but it has two major bugs. I have the tools I need to track them down. I hope to have them cleaned up today.

The new scheme will be much more flexible: all you do is put the files in the folder. Don't worry about dates. Also you will have data in a format that can be used easily for other reports.

I got the bugs ironed out.

Here is a very workable prototype. Missing are:

Conditional formatting: I found it easier to eliminate it and re-establish it than try to preserve it.

The Summary formulas at the top: same reasoning.

And a small bit of code to add a new line for a date when it is needed.

Here is how the new system works. You put the files you want to import into the in-basket. It doesn’t matter if it is a complete set.

The program opens them and extracts the data. It gets the mapped file name from the name of the file. Bug number 1 was that this wasn’t happening correctly then I found that the octothorpe (Yes, that’s what a pound sign / hash / # is really called.) was causing a problem with the match. I found a work-around for it.

The program also gets the dates and column headers from the file and the values and puts all of it into a data table with the format:

Date | Mapped File Name | Header Name | Value.

What makes a record unique is the combination of date, file name and header name – a thing I call the composite after its database counterpart: composite key. As long as the file names and headers don’t change then the only thing that can change is the date.

As the program reads the file, it checks the composite keys in the existing table against the one it generates on the spot. If there is not a match, then this means it is a new record and the information is appended to the table.

If there is a match, then the program finds the record with the matching composite and updates its value field.

With this scheme, it is impossible to collect duplicate information so even if the new files have data overlap with existing data, it is not an issue.

Since each record is unique and there is no duplicate data, we can use SUMIFS to get the record’s value. Here is one of the formulas by way of example:

=SUMIFS(Table_Data[Value],Table_Data[Date],[@[1st day of month]],Table_Data[File],"Traffic.Chart.PageViews",Table_Data[Item],"Total Page Views")

These formulas are plugged into the table on the Datas sheet.

This is a much more effective system that trying to “map” a value from a cell in one file to a cell in another file. Also this data are arranged in a good format for pivot table analysis so you can generate a number of ad-hoc programs without much effort.
I’ll clean it up, but the only information you now need on the Control Panel Sheet is the Read CSV button and what is currently in cells G2 and G3 which tell you where to read the files and where to put them when the program is done reading them.

I did not have a complete set of CSV files, so I had to make up some data on rows 67 and below to test my formulas. Eventually, these records will be updated with good data or you can remove these rows.

Feel free to play with this version and also check out the values especially those I had to make up while testing.

I’ll get back to work with the cleanup (top formulas and conditional formatting).