Combining CSV Files Programmatically

A while ago I needed to import a bunch of comma-delimited files into the Excel file I working on at work. This was a tedious process and I thought Id speed it up for next time. So sifting through the web and visiting a bunch of different macro websites I piece together this code. It imports all the .csv files within the directory you specify into an Excel file. The code has a few rough edges but I thought Id share before I forgot about it.

Sub ImportCSVs()

 Dim fso     As Object
 Dim fldr    As Object
 Dim fil     As Object
 Dim wbResults As Workbook

 Range("A2").Select

 strFldrPath = ActiveCell.FormulaR1C1 'cell with file path, edit this as needed

 Set fso = CreateObject("scripting.filesystemobject")

 Set fldr = fso.GetFolder(strFldrPath)

 For Each fil In fldr.Files

     Set wbResults = Workbooks.Open(Filename:=fil.Path, UpdateLinks:=0)

     Range("A1:Z600").Copy

     ThisWorkbook.Activate

     Sheets.Add After:=Sheets(Sheets.Count)

     LastSheet = Sheets.Count

     Sheets(LastSheet).Name = fil.Name

     Sheets(LastSheet).Activate

     Range("A1").Select

     ActiveSheet.Paste

     Application.CutCopyMode = False

     wbResults.Close SaveChanges:=False

 Next fil

End Sub

API Data Caching

I needed to cache some API data to a file recently. However I did not want the file to be saved every single time someone hit my server but every 10 minutes. Below is a function I used to accomplish that.

<?php
function cacheData($api_query, $save_as_file, $minutes_til_expire) {
    try {
        if (file_exists($save_as_file)) {

            $time_expire = time() + ($minutes_til_expire * 60); // Expire Time (3 mins)

            if(filemtime($save_as_file) <= $time_expire) {
                // If file is older than expire time, replace it
                unlink($save_as_file);
                file_put_contents($save_as_file, file_get_contents($api_query));
            }

        } else {
            // If file doesn't exist, create it.
            file_put_contents($save_as_file, file_get_contents($api_query));
        }
        readfile($save_as_file);
    } catch(Exception $e) {
        // In case of exception, email me
        $to      = 'you_email@gmail.com';
        $subject = 'JSON File Exception';
        $message =  $e->errorMessage();
        $headers = 'From: webmaster@your_website.com' . "\r\n" .
        'Reply-To: webmaster@ your_website.com' . "\r\n" .
        'X-Mailer: PHP/' . phpversion();
        mail($to, $subject, $message, $headers);
    }
}
?>

A MySQL Database Assistant (Helper) Class

Connecting to a MySQL database is pretty easy but there are things you find yourself always doing over and over again on different projects like sanitizing, outputting your result arrays, changing your errors from displaying on screen to being written to the Apache error log. That’s why I have created this simple database assistant class.

$assistant = new DbAssistant('localhost','database', 'root', 'password');
$assistant->setOutputErrors(true); // display output errors on screen
$assistant->sanitize($user_input);

The following returns a basic results array:

$results = $assistant->executeQuery("SELECT * FROM mytable");

Or the following prints the results array:

$assistant->executeQueryAndDisplayResults("SELECT * FROM mytable");

View: db_helper.php.txt



Warning: This class does not currently support parameterized statements. Please use parameterized statements to avoid SQL injecton attacks.


Parameterized Statements Resource: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Here are some usage examples: