Excel VBA to import CSVs to Multiple Tabs

 My very noddy Macro to import CSV files: 

As part of a recent project, I was required to, as much as possible, automate the production of an excel spreadsheet. Currently we have no facility to do this directly. The plan ended up being to export the many queries as CVSV files. Every month these CSV files are then send to the relevant internal staff via e-mail as a password protected zipped file. 

The process of importing these into a spreadsheet was achieved using a macro. It has been a very long time since I have used VBA for anything because I have always been able to achieve it in SQL or python and as such my macro was cobbled together from stackoverflow answers and a little research. The end result however is a functional piece of code that loops through a range within a config tab to grab a csv and import it into the named tab until it reaches the end of the range. This ends up with 8 tabs being populated from 8 CSVs and minimal work for the analysts. 

In total it took less than a week, which had meetings, to develop the SQL, ETL and macro and it will save the end user around a day a month. 

Here is the code I used: 

VBA Code:

Build Excel in Oracle using xlsx_builder

I mentioned in a recent post that I was looking to automate the production of some Excel workbooks from an Oracle Database. I think I finally hit upon a solution that might get approved for use. The answer might well be the xlsx builder package. 

There are some useful comments here:

https://technology.amis.nl/languages/oracle-plsql/create-an-excel-file-with-plsql/

The package is here: 

https://technology.amis.nl/wp-content/uploads/2021/03/as_xlsx20.txt

I was keen to give the package a go so compiled it on my home laptop. The only extra config that was required was setting up a file directory, a quick Google sorted that along with knowing I needed to call this in caps. 

Once compiled it was simply a case of calling the package. There is no real documentation that I could find however producing basic extracts was easy. 

This produced a nice spreadsheet as shown below. 

The output was as expected, 2 tabs, with the right data and all the data. Time to give it some stress testing and to see what else it can do in terms of data volume and formatting etc. 

Automating Excel Exports from Oracle

At the moment there are some reports that are created that I have been told are generated using OBIEE. Now whilst there is nothing wrong with this that is not the end of the story and from the requirements I have so far they follow quite a few steps:

The Steps:

1) there are 10 analyses, some that take several minutes to run. 

       1a) The analyses need to be updated to reflect the latest period 

2) Once run they are downloaded and copied into a Excel template. 

3) Data is checked and verified 

4) E-mailed to the final customer. 

The Issues:

Whilst the above does not seem like much there are several issues, excluding the fact the final customer is not just signing into OAC. 

1) Time - Producing these reports takes time, running the front end OAC stuff alone takes 15-30 minutes. 

2) Human Error - There is a lot of scope for human error, it does not take much to miss one of the variables in OAC (note since finding this out I have created a session variable to stop this manual step). Copy and paste errors into Excel or forgetting a tab etc.  

Automating Excel output:

There are several different options and I am still in the early stages of working out the best way, whatever happens we can assume I will write some SQL, SQL that will be more efficient that the OAC generated stuff, that will accurately reflect the data for the current period. So options to investigate include: 

1) Within Oracle scheduling jobs and file out stuff is fairly simple. It would be very easy to schedule the above view outputs to exported as a CSV every month and write a macro to collate them into a spreadsheet. This still take the overall time to almost  0 and development time is fairly minimal. 

2) Use python to automate office - Currently not sure that we have everything configured to allow this. Plus, as much as I love python, it is not something the team has a great deal of knowledge with so getting this to the point where it is production ready and could easily be supported probably rules it out. 

3) Use the Oracle Cart tool. Honestly I only came across this today on the following blog and have managed to use it on my home laptop to create the config that can automatically generate a multi tabbed spreadsheet based off the table I setup. Biggest issue with this option is I am not sure how to automatically set this up on a production system and provide to our BI team. 

4) Something Else, we do have the ability to run R scripts, BI publisher (though nobody is keen on it) and I am sure there are other way.