Attorney bookkeeping: Enter client, vendor and employee list into QuickBooks

Hi there, Linda Artesani, Artesani Accounting. I'm back in my home office. Uh, yay! So happy to be back at a desk. And we're going to continue this week showing you the, uh, how to import client list, how to import vendor list, and employee list. And basically, I'm going to keep the video short just to show you how to do the client list, and then from there we'll move on to showing you the exact import. It's all the same. You're going to grab the report from wherever, whatever software you're exporting it out of, grab it as a CSV or an Excel spreadsheet, and copy it over to the formatted sheet that comes from Intuit right in the, right in the import section under the gear. Now, when you do export the data, it's been my experience some programs, especially some of the law firm ones, They export kind of with a lot of garbage and a lot of giant headers. We've even had some that even the team couldn't really clean up. It was just more work to do the cleanup and try to get it to be formatted right. So you have to have some really mad Excel skills to do this. And if you don't, I'm going to give you a link in my blog post to show you the steps to maybe separate the text. So if it doesn't come out exactly how Intuit wants you to import it, you want to make sure that you, when you get the records out, you can do some tricks like separating using the Data tab in Excel and separating it, maybe delimited, delimiting it. So you're going to use a delimiter and separate it by maybe the comma if it's there, or a space. There's options there to do that in Excel to separate the data. I'm not going to jump into that because that'll make the video long, and it would be too long to show you all the different different scenarios. But this way, I'll give you a link to a pretty good website that helps me when I'm trying to separate data, to kind of break it apart into the pieces that I need so that I can pull it into the form that we're importing and make it work so that the import comes in smoothly and everything navigates over to the new QuickBooks Online file. I'm going to take you into QuickBooks Online first in case you are taking it out of that one QuickBooks Online file. This happens with some clients. We get a file that isn't exactly the way we need it to be. We'll have a client come and they try to do it themselves and they made a bit of a mess. A lot of times when that happens, we'll actually look at the data and say, look, we're going to start over. New file, new import. We're going to bring in a new chart of accounts and kind of pull it over. Then we will extract it out of one QuickBooks Online file and bring it into another. So I'm going to show you a little bit of where to find the list and how to get them out of QuickBooks into Excel. And then you will have to copy and paste. They don't export perfectly. Back when you could bring it back down into desktop, you could bring it back up and it came in nice and easy. It was a few clicks. Now maybe you don't even have a desktop QuickBooks file, so it's not even really an option and it doesn't really copy over. So this way we can do this pretty quickly and just with a few clicks and a little bit of trickery with Excel, you're going to be able to pull that in, pull it out of whatever file, whatever program you're using. If it's law-specific, that's where your client list lives. Maybe it's Excel itself and you just need to kind of copy and paste it over. I know a lot of attorneys keep things in Excel. You can move it from there as well. So let me share my screen. I'm going to take you into QuickBooks, show you how to export a list out of QuickBooks Online, and then show you, uh, how to format it and bring it in. It won't take that long in this video. Let me share my screen. So I wanted to show you this because quite frankly, there's been times when I've been like, where is the client list? I need to export it out of one file. And if you don't do this very often, you might think, well, I'm going to go to Sales and I'm going to go over here to the Client List and I'm like, hey, I can select them all here and I can export them here and just grab this and export them to Excel from here. But the problem is with this is that it— if you look at it, it's going to send it out into a format that's not very pretty. So this really doesn't work and it doesn't give you all the data fields that you need to export. So what we're going to do is we're going to go to Reports. That's the easiest way to get it out. Same with the Chart of Accounts, you just go to Reports, or you can also go to— you can also, like I mentioned, export it out through their list. So same thing, Chart of Accounts, you're going to go for Lists. Now I've always had trouble where the heck is the report with the list, trying to find it. So I just type list in the search bar and it brings up all the lists. You got Client Contact, Department List, Employee, Invoice list. So you've got your list reports, quite a few here that you can pull. Then this is the report that you'll be able to— I'm going to grab the client contact list because I want to get all the data fields pushed over. And as you can see just by looking at this, I'm going to export this out to Excel. If I come over here and I export it out to Excel, what you can see here is I'm going to have a problem because if we go over to the template with QuickBooks and see how it's formatted in QuickBooks Pull this over. Go to Gear, go to Import Data, go to the Clients. The problem is— and don't forget, it's the same thing that we're going to have when we import as we did with the Chart of Accounts and as we did with the Client List, the Client Ledger List. It's— the problem is we have to make sure that we have with the name, we want to have the parent name and the matter since it's a sub-customer. So we're gonna have to use that colon and put the name right next to it to import it in properly. But let me go to this file that we just downloaded. So here's the sample. Here's the dummy file for the formatting, right? You can see it's the name. If you're dealing with companies, a lot of attorneys do, you got company. You can do a customer type. You could do separate customer types. Maybe you deal with more residential clients, but then you also do corporate. So you could create those customer types or client types. In this case, your email, uh, there's a field, the column for your email. Phone, mobile, fax— these are all here. And if you have that in your original file, it will be there. But because this street, city, state, and zip are— and country are all in one string on the other file that we exported out, I want to show you a little trick of the trade to help you get this done. So you want to click Data, but you don't want to click this data, you want to click that top. I'm going to click here. I'm on a Mac, you might not have this if you're on a PC. And then I'm going to click Text to Columns. And what I want to do here is I want to separate I really want to separate it. If this was all one string, I'd want to separate the street, the city, the state, right? But you might have to separate every, every word into a column and then cut and paste and put them back. And you just go delimited here, and then you're going to come to the next tab, and then what you're going to do is you're going to click space, and space is going to separate it like this. And then what you're going to do is separate it all, push it into another whole page or a whole nother section of your Excel spreadsheet. And then once you have all of the items separated, then you're going to go in and then recombine the ones you want. So you're going to want to combine the 123 Accounting Lane into one tab. You're going to want to combine San Francisco into one tab. So you're going to have to take a little bit of time to get into this. Now, actually, I'll— what I'll do is I'll put a couple of links into the page so that you can see a couple of cool videos that I revert back to when I can't remember how to do this because I don't do it all the time. Now I'm back in the new file, the one I'm going to move the data. I took it out of another file and I'm going to move it into this file. And we've already formatted the spreadsheet, so I'm going to come over here to the gear. And again, just like we've been doing, right, we're going to import the data. We're going to grab that customer or client list. We're going to browse. We're going to grab— let's see where I saved that— right here, Law Firm Data Import Formatted. And then I'm going to Click Next, just like we've done with the chart of accounts and everything. This file didn't have a lot of things, so the city, the street, the province, and all that stuff is here. Um, it didn't have the company or email. It didn't have all that data. So if you did, it would obviously recognize it. Hit Next, and then there's my data. And you can do the cleanup here too as well. So if you see some things that are missing, I'm bringing it in at the client level because this file is filled out on the client level, so you don't need to add the address into all the, all the matters. But you can see here the matters are going to be imported. The client— this is the client list. Now, this is not the one we did last week with the liability accounts, but very similar because we want parent account and sub-parent. So parent account and sub-account here. So you can see Smith Mary, and you see the colon, right? So you can see the colon. So that shows you that it's going to come in as a sub of Mary Smith. You can see that. So I'm just going to hit Import and let it go, let it fly. Here we go. And there's 18 customers and hey, they all went in. Wonderful. Everything's good. So you're going to do the same thing with your vendor list. You're going to do the same thing with your employee list. Employees are going to be a little bit trickier because if you are using QuickBooks Online Payroll, you're going to go and need to go in and enter all those details, W-2 information, all that critical stuff. Or if you're using QuickBooks Online Payroll, you can actually give it to the employee and let them fill out their own things. That's a new feature in QuickBooks Online Payroll, which is pretty cool. Saves you a lot of time. So I hope that was helpful. Kind of similar to what we've done already with the chart of accounts and the client's ledger accounts that we imported last week. But we're going to get into the better, more juicy things next week. We're going to work on the products and services list. And customizing that to work efficiently for your law firm. And then after that, we'll bring in those open ledger balances and start to backbuild all that data. I hope this is helpful. If you think this is daunting and you really don't want to do it and you're an attorney, give us a call. We do accounting for law firms. We do data rebuilds, data migration. Give us a call. We will be more than happy to help you. And if you're an accountant or a bookkeeper and you want to learn more about this stuff, join our Accountants Law Lab. So the links will be in the post. Thanks for watching, and don't forget to hit subscribe. Bye now.