If you work in local government, you've probably run into this situation before: You ran a report from one of your large enterprise applications, and now you need to import it into Excel for analysis. But rather than a CSV or some other format that comes right into Excel, you're given a text file that won't import directly into Excel and also contains some strange formatting issues.
It's so frustrating. What should be a 5 minute process is now going to take you half the day manually fixing these problems.
Let's look at a real world scenario where you can use Excel to quickly transform a hard-to-use report into an easy-to-analyze spreadsheet.
For this example, we'll be converting a budget report from a popular software from Tyler Technologies called Incode into a spreadsheet that contains all of the accounts and totals, but none of the other stuff.
Incode offers a variety of pre-built and custom reports, so yours may not look exactly like this one, but the general rules will still apply. You can follow along by downloading the raw report here.
Step 1: Look at the report
The first thing you'll see is that when you save a report from Incode, it looks exactly like the Report Viewer in the application. This is because the report that's generated is just a text file, and the Report Viewer is not much more sophisticated than Windows Notepad.
Page 1 is a summary of revenues and expenditures across all of the funds in the report. Excel is pretty good as a calculator, so we can always recreate these totals later. As as result, we won't need any of this information.
Page 2 is where the account-level information begins. The first account record is on line 57, and you can see the full account code, account name, current budget, current period, prior year amount, year to date actual and encumbrances, the budget balance, and percent of budget are included in the report.
The format is going to be consistent for all account rows, so we just need to look at one of them to figure out how many characters each column includes. We'll use these values to set our delimiters when importing into Excel.
It's always helpful to look for rows with the most varied data. In this case, negative numbers have parentheses around them, so we'll want to make sure we account for that.
You can count them yourself, or you can just use the values I came up with below:
If you're following along at home, the columns we want (with line breaks) are:
- Account Code (20)
Step 2: Import into Excel
Fire up Excel and click File > Open, then navigate to the report file and select it. You'll be shown the file import wizard, in which you'll tell Excel how to read this file.
Select Fixed Width and click Next to move to step 2, which is where you select the column widths. Use the settings above and click 'Next' to move to step 3.
Step 3 is where you can choose data types for each column. This can be helpful for dates, or to ensure certain fields are treated as text or integers. In our case, it doesn't matter (everything will be converted correctly).
PRO TIP: If you're importing a "ZIP Code" column, always make sure to choose Text for that column. Some ZIP Codes start with a zero (0). If you import as a number, Excel will drop that leading zero, meaning you'll have 4-digit ZIP Codes. Yikes.
Click Finish will bring up a new Excel window with your imported text file.
Step 3: Cleaning your data
All of the work we've done so far has merely gotten our data into a spreadsheet. Now the fun part begins.
As you can see, there are a lot of unneeded rows of data, but you'll notice that Excel did helpfully convert our report's negative numbers (surrounded by parentheses) into actual negative numbers, which was nice of Excel.
The easiest way to remove those unneeded rows is to simply sort your data. In this case, all of the account-level rows have the same format, which looks kind of like this:
Highlight columns A through I by clicking and dragging on the column headers. Select the Sort & Filter > Custom Sort menu:
Since we don't have a header row, you'll simply select Column A and sort A to Z. Once you click OK, you'll see some ugly stuff (but don't worry, we're almost there).
Now, all of our account rows are grouped together, which makes deleting everything else rather straightforward. Simply select the rows with non-account data and delete them. You'll notice a few spare lines in between each fund, so you'll want to delete those as well.
Once you finish deleted everything, you should find yourself with 212 rows of actual, relevant data!
In future posts in this series, we'll show you how to use lookup tables to add context to this data and how to create a template spreadsheet for reports that you may want to analyze on a regular basis!