Converting American Express PDF Statements to CSV with Powershell

Gah.  Importing accounts into accountancy programs can be tedious.  What makes it more tedious is when banks make life even more difficult.  American Express allows you to download statements in nice CSV formats though.

Yay!

Except if the statement is older than 6 months, then you have to download it as an non-formatted PDF.

Boo.

Last time I did this manually by grabbing elements from the PDF,  pasting into a CSV file one block at a time and then massaging the data to try and get it into my accounts package.  This was tedious, time-consuming and mostly inaccurate.

So this time, I exported the PDFs to .TXT and then wrote some Powershell to produce a formatted CSV from the non-formatted export.  This was interesting, time-consuming and mostly accurate.  So that’s a win in my book.

I used this an opportunity to learn regular expression processing in Powershell so there are two ways of doing it in the following code;  the second is what I tried first and is inelegant and overly complex.  Both work though (for a certain definition of ‘work’, see below).

I still had to check the data and manually fix a few things but as the source was mostly unstructured text I was amazed it worked as well as it did.

The script is below;  I’ve added some comments but this mostly as-is from when I bashed it out.

UPDATE:  I’ve revisited this with an updated and expanded script HERE.

#Export the American Express statement to .TXT then throw the script at it
#Source file is the txt export of the pdf
$FilePath="d:\temp\Statement_Dec 2013.txt"
#Where to put the output csv
$ExportFile="D:\temp\output.csv"
$File=Get-Content $FilePath
#Import the text file.  We're only interested in lines that start with a number of 3 letters, a space and then a number (ie Nov 02)
$FilteredFile1=$File | ?{($_ -match "^[A-Za-z]{3} \d") -or ($_ -match "^\d+\,?\d*\.\d+")}
$List=@()
for($i=0;$i -lt $FilteredFile1.Count;$i++)
{
    $FilteredString=""
    #If the entry starts with a date
    if ($FilteredFile1[$i] -match "^\w{3}\s\d")
    {
        #Sometimes the entry spills onto the following line;  the following checks for that (a number at the start of the line).  If found the two lines are merged.
        if($FilteredFile1[$i+1] -match "^\d+\,?\d?\.\d+\s?$")
        {
            $FilteredString= $FilteredFile1[$i]+" "+ ($FilteredFile1[$i+1]).Trim()
        }else
        {
            $FilteredString=$FilteredFile1[$i]
        }
    #Otherwise check it's the format Number then date ;
    }elseif ($FilteredFile1[$i] -match "^\d+\,?\d*\.\d+[A-Za-z]+")
    {
        $FilteredString=$FilteredFile1[$i]
    }
    if ($FilteredString -ne "")
    {
        $Output | add-member -membertype noteproperty -name "Category" -value ""
        $Output | add-member -membertype noteproperty -name "Memo" -value ""
        $Output=new-object psobject
        #Numbers sometimes use the format where 1000's are seperated by commas
        if($FilteredString -match "^\d+\,?\d*\.\d+")
        {
            #The nicer way of doing it;
            #Lines start with a numeral ("^\d+\,?\d*\.\d+") followed by 3 digit
            #Every regular expression within brackets is output as a seperate entry in the matches array ([0] is always the entire string)
            #So we're interested in  (Inflow) (Payee) (Date)
            $FilteredString -match "(^\d+\,?\d*\.\d+)(.*)([A-Za-z]{3} \d{1,2}\s)" 1>$null
            $Output | add-member -membertype noteproperty -name "Outflow" -value ""
            $Output | add-member -membertype noteproperty -name "Inflow" -value ([float]$matches[1])
            $Output | add-member -membertype noteproperty -name "Payee" -value $matches[2]
            #Formats the date using 2013 as the year
            $Output | add-member -membertype noteproperty -name "Date" -value ("{0:dd/MM/13}" -f [datetime]$Matches[3])

        }else
        {
            #It doesn't start with a number;  these seem to be outflows
            #The less nice way that I wrote first, a seperate comparison for each part we're interested in.
            $Output | add-member -membertype noteproperty -name "Inflow" -value ""
            $FilteredString -match "^([A-Za-z]{3} \d{1,2}\s){2}(.*)(\s\d+\,?\d*\.\d+)?" 1>$null
            $Output | add-member -membertype noteproperty -name "Payee" -value $matches[2]
            $Matches.Clear()
            $FilteredString -match "^[A-Za-z]{3} \d{1,2}" 1>$null
            $DateString=$Matches[0]
            #Formats the date using 2013 as the year
            $Output | add-member -membertype noteproperty -name "Date" -value ("{0:dd/MM/13}" -f [datetime]$Matches[0])
            $Matches.Clear()
            $Filteredstring -match "\d+\,?\d*\.\d+\s?$" 1>$null
            $Output | add-member -membertype noteproperty -name "Outflow" -value ([float]$matches[0])
            $Matches.Clear()

        }
        #Write to a list of objects
        $List=$List+$Output
    }
    #Output the list of objects to a file
    $List | Export-CSV -Path $ExportFile -NoTypeInformation
}