posted
I can't believe finding an answer to this is so hard. Support is useless and no one knows what a CSV file is or what an import is or why someone would want to import from Excel into QBO... it's frustrating.
I want to import checks that are actually ACH transactions related to some of our work. Each check can have multiple Categories (Expense accounts) and multiple Classes and Customers. So, one check is multiple lines of information.
I cannot figure out how to actually get this to import. The documentation is nonexistent. There is a sample CSV file -- and a sample Excel file -- but the samples don't really explain a lot. From what I can find, this is what the Excel file format should be...
But QBO has various problems with each of the permutations I try of this format. The objective is to get ONE check recorded with Check #ACH, with ONE Payee, that does NOT have to be Printed. And that ONE check will have three different expenses associated with it, each going to different accounts, customers and classes.
What is the correct format to get QBO to import it like I want?
To import, I select the GEAR icon, Import Data, select CHECKS, and then NEW LAYOUT (because that lets me use Excel without having to go to CSV).
Any ideas anyone?
Best answer January 31, 2025
Solved
Best Answers
Despite attempts by Quickbooks Support Staff to "help" or "answer" my question... they fundamentally didn't understand what I was asking, and gave information that was inaccurate.
I was able to solve the problem through Trial & Error... but the software remains very buggy and inconsistent on this process.
Here is the layout that works to import a check from Excel, a check that is a split transaction with more than 1 line item.
| Check No | Payee | Transaction Memo | Print Later | Payment Date | Bank Account | Type | Category | Description | Amount | Customer / Project | Billable | Class |
| 1 | ABC Corp | Project Payments for portions of scope completed | FALSE | 01/31/2025 | Operating Acct | Category Details | Dataset Purchase | Purchased XYZ dataset to use in anlysis | 1.25 | Alpha | FALSE | East |
| 1 | Category Details | Equipment Rental | Rented meeting space for project planning | 2.45 | Bravo | FALSE | South | |||||
| 1 | Category Details | Printing and Copying | Made copies of project plans | 3.15 | Charlie | FALSE | North |
How you do the import makes a difference, though. The process is buggy, however, and there were minor issues that popup -- so be careful.
Gear/Import Data/Checks - Use CSV
Take the table of info above and export to a CSV. When you then import the CSV file, the first thing you see is the dialog to "Map Your Column Headings". However, QBO will not auto match the Category Detail section, so you have to do it manually every time -- and that is easy, but requires a lot of clicking. THen you will be presented with a preview of your import. It all looks good and when you click SAVE, you will get the precise check entry in your register that you want. Even if you use a check number of "EFT" or "ACH" -- the import will work. The only snag is that it doesn't automatically map column headings. And, of course, you have to export from Excel to a CSV.
Gear/Import Data/Checks - NEW LAYOUT - Use CSV
Take the table of info above and export to a CSV. When you want to import, click on the NEW LAYOUT button at the top. When you then import the CSV file, the first thing you see is the dialog to "Map Your Column Headings". Voila! All of your column headings match perfectly! Then you will be presented with a preview of your import. Here's where it gets a little wonky. The BILLABLE column does not respect what you imported... it checks these items as billable, even though my file had that set to FALSE.
However, it doesn't matter -- because QBO ignores those fields during import and you can't mark anything as billable. It all looks good and when you click SAVE, you will get close to the precise check entry in your register that you want: the check number gets ignored, the Billable gets ignored, and so does the Print Later... the check has no check number and has to be switched from Print Later (fyi, in my case, these are ACH transactions that do not need to be printed). The import seems easier, but the results just ignore some of teh flags in the data. And, you still have to export from Excel to a CSV.
Gear/Import Data/Checks - NEW LAYOUT - Use EXCEL!
Take the table of info above and export to a CSV. When you want to import, click on the NEW LAYOUT button at the top. Now import the original Excel file, not a CSV version of it. You will see the dialog to "Map Your Column Headings". Voila! All of your column headings match perfectly! Then you will be presented with a preview of your import. The BILLABLE column does not respect what you imported... it checks these items as billable, even though my file had that set to FALSE. However, it doesn't matter -- because QBO ignores those fields during import and you can't mark anything as billable. It all looks good and when you click SAVE, you will get close to the precise check entry in your register that you want: the check number gets ignored, the Billable gets ignored, and so does the Print Later... the check has no check number and has to be switched from Print Later. The import seems easier, but the results just ignore some of the flags in the data. But, you don't have to export from Excel to a CSV.
This is not the experience we want you to have while importing your checks from CSV or Excel into QuickBooks, JohnnykT. L. I'm here to provide you with the relevant information to ensure your check imports correctly.
It's essential to know that QuickBooks supports importing CSV files in either a 3-column or 4-column format. These are the only formats that QuickBooks can process effectively. Here’s a sample of the correct formatting:
Additionally, may I ask what specific errors you're encountering while importing your CSV and Excel file? If you do encounter an error message when you upload, do the following:
- Remove Zeros: Delete any zeros (0) from the file and leave those cells blank.
- Correct Description Column: Ensure that the Description column does not contain any numerical values.
- Adjust Column Headers: If the word "amount" appears in the Credit or Debit column headers, please remove it. The headers should simply read "Credit" and "Debit."
- Standardize Date Format: Ensure all dates are in a consistent format. We recommend using dd/mm/yyyy.
For more detailed information you can check the format of the CSV file from the given information above.
However, if your bank does not support CSV files but offers formats like Quicken (QFX), QuickBooks Online (QBO), or Microsoft Money (OFX), you can manually import your data.
Furthermore, I'm adding this resource for future reference on how to categorize transactions uploaded to QuickBooks Online: Categorize online bank transactions in QuickBooks Online.
Our team is here to provide comprehensive support as you investigate these options in importing checks from CSV and Excel into QuickBooks. For further clarification or assistance, please reach out through this channel. We look forward to helping you enhance QuickBooks experience.
I know another way to achieve this, @Anonymous. I’m here to clarify any questions or issues you may have.
Since you're currently utilizing the New Layout, please note that the option to add a line item is unavailable. To manage your check transactions effectively, I recommended switching to the Old Layout. To add a new line item, click on the Number column and choose the appropriate option for the new line item.
On the other hand, you might want to review this material for additional information on Common questions about importing data to QuickBooks Online.
Once the checks are imported, you can review the entries in the Check Detail report. For more hints on running reports in QBO, see this article: Run a report in QuickBooks Online.
Our doors are always open to help you again if you have follow-up questions about importing checks from CSV and Excel into QuickBooks. I'd be happy to help. Have a good one.
Despite attempts by Quickbooks Support Staff to "help" or "answer" my question... they fundamentally didn't understand what I was asking, and gave information that was inaccurate.
I was able to solve the problem through Trial & Error... but the software remains very buggy and inconsistent on this process.
Here is the layout that works to import a check from Excel, a check that is a split transaction with more than 1 line item.
| Check No | Payee | Transaction Memo | Print Later | Payment Date | Bank Account | Type | Category | Description | Amount | Customer / Project | Billable | Class |
| 1 | ABC Corp | Project Payments for portions of scope completed | FALSE | 01/31/2025 | Operating Acct | Category Details | Dataset Purchase | Purchased XYZ dataset to use in anlysis | 1.25 | Alpha | FALSE | East |
| 1 | Category Details | Equipment Rental | Rented meeting space for project planning | 2.45 | Bravo | FALSE | South | |||||
| 1 | Category Details | Printing and Copying | Made copies of project plans | 3.15 | Charlie | FALSE | North |
How you do the import makes a difference, though. The process is buggy, however, and there were minor issues that popup -- so be careful.
Gear/Import Data/Checks - Use CSV
Take the table of info above and export to a CSV. When you then import the CSV file, the first thing you see is the dialog to "Map Your Column Headings". However, QBO will not auto match the Category Detail section, so you have to do it manually every time -- and that is easy, but requires a lot of clicking. THen you will be presented with a preview of your import. It all looks good and when you click SAVE, you will get the precise check entry in your register that you want. Even if you use a check number of "EFT" or "ACH" -- the import will work. The only snag is that it doesn't automatically map column headings. And, of course, you have to export from Excel to a CSV.
Gear/Import Data/Checks - NEW LAYOUT - Use CSV
Take the table of info above and export to a CSV. When you want to import, click on the NEW LAYOUT button at the top. When you then import the CSV file, the first thing you see is the dialog to "Map Your Column Headings". Voila! All of your column headings match perfectly! Then you will be presented with a preview of your import. Here's where it gets a little wonky. The BILLABLE column does not respect what you imported... it checks these items as billable, even though my file had that set to FALSE.
However, it doesn't matter -- because QBO ignores those fields during import and you can't mark anything as billable. It all looks good and when you click SAVE, you will get close to the precise check entry in your register that you want: the check number gets ignored, the Billable gets ignored, and so does the Print Later... the check has no check number and has to be switched from Print Later (fyi, in my case, these are ACH transactions that do not need to be printed). The import seems easier, but the results just ignore some of teh flags in the data. And, you still have to export from Excel to a CSV.
Gear/Import Data/Checks - NEW LAYOUT - Use EXCEL!
Take the table of info above and export to a CSV. When you want to import, click on the NEW LAYOUT button at the top. Now import the original Excel file, not a CSV version of it. You will see the dialog to "Map Your Column Headings". Voila! All of your column headings match perfectly! Then you will be presented with a preview of your import. The BILLABLE column does not respect what you imported... it checks these items as billable, even though my file had that set to FALSE. However, it doesn't matter -- because QBO ignores those fields during import and you can't mark anything as billable. It all looks good and when you click SAVE, you will get close to the precise check entry in your register that you want: the check number gets ignored, the Billable gets ignored, and so does the Print Later... the check has no check number and has to be switched from Print Later. The import seems easier, but the results just ignore some of the flags in the data. But, you don't have to export from Excel to a CSV.
