If you’re like most small businesses, you’ve got most of your critical data stored in some sort of software package or app. Your financial data is probably in QuickBooks, for example, and maybe your CRM data is in HubSpot. No matter which package the data is in, it resides in some sort of database product, like MIcrosoft SQL Server or Oracle. Wouldn’t it be nice if you could connect Excel to that database, so that you can use the filtering, charting and pivoting features to analyze your data?
Chance are, it can be done. This is a big topic, and there are lots of ins-and-outs. But let’s start taking a look to see how you might accomplish this task.
Importing or Linking
Excel has many options for connecting to your data. Let’s break it down into two main categories:
Importing
Linking
With the Importing option, you extract the data from your software package, and then import that data into Excel. This is a simple option, and widely available. If you’ve got the ability to generate a CSV or TXT file from your software package, you can import that into Excel.
The downside of this option is that you end up with a static set of data. If the data in your software package changes, your Excel file won’t be aware of that.
That’s where the Linking option comes in. It’s possible to connect Excel to your database with a live link, so that any changes made in your software package will be reflected in your Excel data. This is a great option for dashboards and dynamic queries that will ensure you always have the latest-and-greatest data.
The drawback with this method is that it’s not as widely available as the Import option. Your software package must provide some method of connecting to it, which is not always available. In particular, I’m finding that many of the modern, cloud-based software packages don’t allow this.
Importing
Let’s take a quick look at importing your data into Excel. As with all things Microsoft, there are multiple ways to accomplish this task. Here’s one of them.
In my software package, I generated a CSV file containing my list of vendors. When I open the CSV file in Notepad, it looks like this:
CSV stands for “Comma Separated Values”, and as the name implies, commas are used to separate the individual values from each other. On the top row, we have the headings, with each column separated by a comma. On subsequent rows, we see the seven columns for each vendor (ID, name, address, city, state, zip and phone) separated by commas.
Check out the second data row – the one for vendor ID 1943. The name of this vendor is DEF, Inc. Notice that it’s encased in quotes? That’s because the vendor's name contains a comma. My software package added the quotes on this name so that the comma in the name won’t be considered a column delimiter.
TXT files are little bit different than this, but for purposes of this discussion, they’ll mostly work the same way.
The Import Wizard
You might be able to open your CSV file in Excel by double-clicking it, but that doesn’t work for everyone. Also, if you do, you may not get the Import Wizard screens.
Instead, open Excel, and then use the “From Text” option on the Data ribbon:
As always, your ribbon might look a little different than mine, but you should be able to find this option if you hunt around a little bit.
Once I click this button, the Import Wizard opens. I’m prompted to locate the file, and after I do, the first screen of the wizard is displayed:
The “Data Type” option at the top is important. As we previously saw in Notepad, my CSV file is delineated with commas between the columns, so I’ll choose “Delimited” here. If you’re using a TXT file, you might not have delineators. Instead, some TXT files use a “Fixed Width” format. In that case, you’ll see that each column is lined up, like this:
See how each column is neatly aligned on all rows? That’s a fixed-width file, meaning that each column is the same width for all rows.
If that’s the case for your file, you’d choose the “Fixed Width” option.
Getting back to my CSV import, I’m going to ignore all other options on this first Wizard screen, and click Next. That brings me to this screen:
Notice that “Tab” is the only delimiter that’s checked off. Since my file uses commas as a delimiter, I’ll check that off instead. You’ll notice that the “Data Preview” then changes to show my data correctly aligned in columns:
You might also notice that the quotes have now been removed from the “DEF, Inc” vendor. That’s because of the “Text qualifier”, which is set to a double-quote. Once again, this is Excel understanding that the quotes were only used to make sure the comma in the vendor’s name didn’t cause a column break. It’s correctly deduced that it should remove the quotes, but tread everything that was within them as a single value.
Now that my data preview looks good, I’ll click the “Next” button. This brings me to the final screen of the Import Wizard:
This final screen allows me to specify a data type for each column. In most cases, you don’t need to do this, but if you’ve got any date columns in your data, take a quick look to see if Excel seems to have correctly formatted those. If not, select the column and click the “Date” option button.
I’m going to click the Finish button, and I’ll be asked where I want the imported data:
As you can see, the default is to add the data to an existing sheet, but I can choose to add a new worksheet instead.
I’m ignoring all other options for now, to keep things simple. After I click OK, here’s what my Excel sheet looks like:
As you can see, the data from the CSV file is now in Excel, and has been properly parsed into individual columns.
One interesting note: look at the zip code. Excel interpreted this column as a number, which is why it dropped the leading zero. I could have fixed that back on the final screen of the Import Wizard, where I could have selected that column and set its data type to Text.
Now that your data is in Excel, you can use all of your Excel tools to report and analyze the data. If this is a file you’ll be importing frequently, it might be worth your while to learn a little bit about macros, so that you can automate the import.
Up Next: Linking
In my next Excel blog, I’ll look at how to link this data, rather than importing it. As I mentioned previously, linking will allow you to have an up-to-date data set in Excel. If you’d like to be notified when that post goes live, subscribe using the form below.
Did this work for you?
Although the steps are very similar in different versions of Excel, minor differences can sometimes be confusing. If you need some direction, reach out to me. We can set up a zoom to look at your issues and set you on the right path.
As always, let me know if you’ve got any questions on this topic. See you back here next time!
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions#learningneverstops#smallbusiness #excel #microsoftexcel#msexcel #excelformulas #dataanalysis
© North Port Solutions, LLC, 2022. All rights reserved.