Import bank transactions from Excel to QuickBooks

Hi Les9. Importing CSV files is possible in QuickBooks Desktop, however, you will need to download the QuickBooks Import Excel and CSV toolkit. This contains the PDF instructions and guidelines as well as a few samples to show the proper formatting. 

If you'd like to learn more about importing and exporting data in QBDT check out these articles as well:

• Import and export data in QuickBooks Desktop

• Import/export CSV files

• Export, import, and edit IIF files

• Import web connect (.qbo) files

As you may have noticed, there are quite a few options available for importing data into QuickBooks Desktop. I recommend you check through them all before you decide which one you'd like to use.

If you need anything else though, please leave a reply below so I can get back with you. Enjoy the rest of your day. 

If you don't want to learn IIF files yourself, you also can quickly convert a CSV file opened in Excel to an IIF file using our BRC IIF Transaction Creator 

Hi Steve,

Thank you for your answer. 

As far as I understand, I can only import account list, vendor list, customer list and item list from Excel to QuickBooks but what about bank transactions?.  I know I can import bank transactions from QBO file, but I want to import bank transactions from Excel file (.CSV). Is that possible?

Thank you for your help!

HI Steve,

Thank you for your response.

I can see that I can import account list, vendors, customers, items... from excel to QB. Also, I know that I can import bank transactions from QBO file to QB.  But what about bank transactions from Excel (.cvs) file  to QB? Is there any way to do this?. 

Thanks for your help.

Thanks. Good to know.

Good Morning, @Les9. 

Thanks for reaching back out to the Community. Depending on your bank, you should be able to go to their website and log into your online banking and download transactions into an Excel CSV file. Afterward, you can import the Excel CSV file into QuickBooks Desktop. If you run into any trouble, just let me know. I want to make sure that all of your concerns are addressed. 

I'm always here to lend a helping hand. Have a great day!

@Les9 

If you are more familiar with XLS, utilize the trial period of these importer tools

https://transactionpro.grsm.io/qbd

https://partners.saasant.com/affiliate/idevaffiliate.php?id=5051_2

Thanks for your response.

When I go to File > Utilities > Import > Excel Files

"Add or edit multiple list entries" - Manage your customer, vendor, and item lists all in one place. Paste from excel to quickly add new or edit existing entries. Choose :Lists > Add/edit multiple list entries to begin making multiple entries. If you'd like QuickBooks to take you there now, click Yes

I click no and I go to the screen called: "Add your excel data to QuickBooks". When I add my excel file, it says that "The excel file you're trying to import is not a QB supplier templated. The templates that you sent in your first response was for account list, vendor list, customer list and item list. My excel file is not any of those lists, the excel file that I want to import has information about bank transactions, in 3 columns:

| Date | Description | Amount  |

For example:

| 05/01/2019  | Starbucks store | 10.45  |

Which template should I use?.

Thanks

RE: As far as I understand, I can only import account list, vendor list, customer list and item list from Excel to QuickBooks

That is not correct.

RE: what about bank transactions?

When using the IIF Transaction Creator you can import most types of financial transactions.

RE: I want to import bank transactions from Excel file (.CSV). Is that possible?

You can start with a CSV file, but the data needs to be converted to an IIF file first.

---

@Les9 wrote:

 

Which template should I use?.

 

---

Steve - I'm in the process of Importing Bank/Credit Card transactions in EXCEL format into QB 2020 (upgraded from QB 2012) because my bank only makes the Credit Card transactions available in either PDF or EXCEL formats.  Since 2012, my credit card account in QB has been set up with the following Column Headers (left to right): DATE;  TYPE;  PAYEE;  ACCOUNT (under PAYEE);  MEMO (under PAYEE);  CHARGE;  PAYMENT;  BALANCE.  I would like to leave these QB Column Headers in place for the "Importing" process.  I was going to change the Column Headers on the Bank Credit Card/EXCEL spreadsheet to Match the QB Column Headers, then do the Import.  By doing this Column Header matching between the Credit Card/EXCEL spreadsheet and the existing QB Credit Card account, CAN I AVOID DOING THE MAPPING in the QB Import Process?  To me, changing the Excel Spreadsheet Column Headers is much easier than trying to do the Mapping of the QB Account.  AM I CORRECT IN USING THIS PROCESS TO IMPORT THE EXCEL SPREADSHEET?   I've spent at least 2 hours on the phone with QB Customer Support and they only understand the Importing of QBO files into QB.  I just spent $200 for QB Desktop Pro 2020 and hate to spend another $50-$60 on QB "Level 2 Customer Support", when this is the only question that I have.  I appreciate your help on this.  Thank you GK07

Hello there, GK07,

You'll want to move the data from the Excel to IIF. This way, you'll be able to import your credit card transactions to QuickBooks. 

To do so, you'll want to download and install the .IIF import kit. Let me show you how:

1. Download the kit.
2. Select the Save button to download the IIF_Import_Kit.zip file to your Windows desktop.
3. Use WinZip or any compatible file compression utility to extract the contents of the downloaded file. The contents will automatically extract into a sub folder called .IIF Import Kit.
4. Open the IIF Import Kit folder to view its contents.

After that, copy your data from the Excel file to the IIF file. Then, import it to QuickBooks. Here's how:

1. Go to the File menu, select Utilities then Import then IIF Files.
2. Select Import IIF.
3. Locate the file, then select OK. Note: We don't recommend the option Import it for me. I'll fix it later. Since this option bypasses data review, some of the info may not be correct or complete for your books. It also requires that you switch to a single user.
4. Once the import is successful, you'll see the numbers of lists and transactions imported.
5. Press Done.

I've added these handy articles for more details about the step above:

• IIF Overview: import kit, sample files, and headers
• Improved IIF Import in QuickBooks 2019 and later.

Keep me posted if you have follow-up questions. I'm always around to help.

Thank you and stay safe!

When I try to download, import etc. the account does not show up in my Use an existing quickbooks account. The account has a weird number for Bank Acct No. & Routing Number . I have tried deactivating the account but it has a warning Your Federal Employer Identification Number (EIN) is not valid. Our bank has a new Web Connect and this account is still listed with the old web connect that does not work anymore. Any suggestions on clearing the invalid EIN so I can deactivate and re activate to new Web connect?

What if your bank doesn't offer the .QBO format? I am using a desktop Mac version 2019. I cannot seems to find a way to import credit card transactions. Is there a way to do this?

Hi there, @foleyterri.

Currently, we can only use a .QBO file to get transactions into the bank register. You can utilize any supported third-party apps that can help convert a file to a .QBO file format. 

Here's how:

1. Go to the Intuit Marketplace website.
2. Enter the keyword or name of the app in the Search field.
3. Press Enter.

I'd also like to let you know that our developers are constantly uncovering and considering new ways to improve the program. To be updated with our latest news and enhancements, you can visit our QuickBooks Blog site.

If you have any other questions, feel free to post them here. We're always here to help. Have a good one.

IsQuickBooks Import Excel and CSV toolkit made for Mac OS Catalina?

---

@Steve_C wrote:

Hi Les9. Importing CSV files is possible in QuickBooks Desktop, however, you will need to download the QuickBooks Import Excel and CSV toolkit. This contains the PDF instructions and guidelines as well as a few samples to show the proper formatting. 

 

If you'd like to learn more about importing and exporting data in QBDT check out these articles as well:

• Import and export data in QuickBooks Desktop

• Import/export CSV files

• Export, import, and edit IIF files

• Import web connect (.qbo) files

As you may have noticed, there are quite a few options available for importing data into QuickBooks Desktop. I recommend you check through them all before you decide which one you'd like to use.

 

If you need anything else though, please leave a reply below so I can get back with you. Enjoy the rest of your day. 

---

Thanks for joining this thread, morel.

You need to use the IIF text file or set up for Online Banking in order to import bank transactions.

If you wish to proceed, you can follow the steps below:

1. Go to Banking menu at the top, then select Online Banking Setup.
2. In the search field, enter your Bank's name.
3. Depending on your bank, you may be presented with options for Direct Connect/Web Connect or Web Connect only.
4. On the Link your Accounts screen, you can choose which bank accounts you can add to QuickBooks.

Once done, you can download a statement from your Bank Web Connect website and import to QuickBooks.

Here's how:

1. Go to Banking menu at the top then select Downloaded transactions.
2. On the Downloaded Transactions window, select the account you want to import or download transactions.
3. Click Download. QuickBooks takes you to your financial institution’s website to
4. Download your transaction file. Then click Import to import the file into QuickBooks.

You can also check this article for more information on how to Set up and use Online Banking in QuickBooks for Mac.

For additional reference, you can check this article for detailed steps about importing transactions. On your keyboard, press Ctrl + F and search for the Importing items in batches to easily find the topic: https://qblittlesquare.com/wp-content/uploads/2018/09/QuickBooks-for-Mac-2019-1809.pdf.

Please know that I'm only a post away if you have anything else. Just leave a comment below, and I'll get back to you.

Hey there, sfvaughan. 

Don't worry, I've got you covered, follow these few steps to copy your data from the Excel file to the IIF file: 

1. Open an Excel file.
2. Click File in the upper-left hand corner, then select Open.
3. Click Browse and look for the IIF file.
4. Select the file and click Open.
5. If you can't see it, make sure that you select All Files in the lower-right hand corner.

I'm also including a very helpful tool that gives you an overview of the IIF files, check it out. 

• IIF Overview.

If there's anything else I can help with, please post down below. Thanks for your time and have a nice day. 

When I try to choose my account for my existing Quickbooks account it is not available in the drop down window.  I am actually in the account itself so I am not sure why it isn't populated as an existing account.

Let me provide some insights about importing transactions in QuickBooks, @WFB.

The reason why the only account showing on the drop-down menu is the account is due to the system being unable to recognize the Web Connect file you're trying to import. So it's providing you a credit card account that isn't linked to online services yet. 

To avoid this, let's download the Web Connect file (.QBO) from within QuickBooks Desktop. 

Here's how:

1. Go to the Banking menu.
2. Select Bank Feeds and then choose Banking Center.
3. Locate your bank and click Download Transactions. 
4. Sign in to your bank's website to download the Web Connect file.

This way, you can ensure that the Web Connect file will be downloaded directly to the correct credit card account.

You may open this article if you encounter any issues doing Web Connect: Fix Web Connect import errors.

To guide you in importing transactions in QuickBooks, I've added these articles:

• Import and export data in QuickBooks Desktop
• Export, import, and edit IIF files

Feel free to utilize the comment section below if you have other concerns. I'll be sure to get back to you. Have a wonderful day.

Hello, I do not have my banking information in Excel. My more recent bank transactions were imported. Is there a way to transfer the earlier transactions similarly? Thanks! 

Hi there, @cb12345.

Thank you for joining the thread. I can share with you some insights about how online banking connection works with QuickBooks.

Yes, once your account is connected for an online banking connection, the system automatically downloads the recent transaction available from your bank. For 90 days old and older transactions, you need to prepare an Excel .QBO file for them as suggested above, then manually import the file to QuickBooks.

Here's how:

1. From the Banking menu, select Bank Feeds, then Import Web Connect Files.
2. Locate the QBO file you saved, then select Open.
3. When prompted to Select Bank Account, select:
   • Use an existing QuickBooks account if the account you're importing transactions into is already set up in QuickBooks.
   • Create a new QuickBooks account if the account you're importing transactions into isn't in QuickBooks yet. Learn how to create an account.
4. Tap Continue. You'll see a dialogue box telling you that the data has been successfully read into QuickBooks.
5. Hit OK.
6. Go to the Bank Feeds Center to review your transactions.

To learn more about importing bank transactions with Web Connect files, I recommend checking out these articles:

• Import and export data in QuickBooks Desktop
• Set up bank accounts for Bank Feeds in QuickBooks Desktop

For more tips about banking or reconciliation, you can also open the topics from this link: Find help with bank feeds and reconciling accounts for QBDT.

Please let me know how else I can help you with your banking transactions. I'm more than happy to help. Have a good one!