Importing transaction data from CSV including accounts

Matt_Moore · ‎October 11, 2022

Good Morning,

I am going back and importing historical data into a new (to be created) quickbooks file.

I am organizing all of this data in Excel and have just over 5,000 rows. I would like to get all this organized, including Payee, Date, Amount and Account. I have figured out how to go from PDF to CSV to QBO using 2qbo convert software. This will require me to make very many "rules" in Quickbooks.

Does anyone have recommendations for a way to have a column in Excel of the desired accounts and then just do a simple import rather than having to create so many individual rules? Or any other ways to solve this problem?

Thanks so much.

Matt

Fiat Lux - ASIA · ‎October 11, 2022

What type of transaction do you need to import? Credit card charge, credit card credit or else?

Matt_Moore · ‎October 11, 2022

The transactions are from my Chase bank account which has the fields Date, Payee and Amount.

I am going through and organizing this data and would like to assign an account to each transaction the import it. That is I would like to map account in Excel to account in Quickbooks and them import the data and be done. Going though and setting up a couple of hundred import "rules" is something I would have to do if I convert to a QBO file then import that.

Thanks,

Matt

Fiat Lux - ASIA · ‎October 11, 2022

Like this one?

TxnDate	PrivateNote	Currency	ExchangeRate	DepositToAccount	Entity	LineDesc	LineAmount	Account
28/07/2020	Manual Feed			Checking	Jane Smith	Monthly Dues	25	Account1
29/07/2020	Manual Feed			Checking	Bank of the West	July Fees	-15	Office Expenses
30/07/2020	Manual Feed			Checking	Staples	Paper for Copier	-50	Office Expenses
01/08/2020	Manual Feed			Checking	Teddy Jones	Charitable Contribution	500	Account1

GlinetteC · ‎October 11, 2022

I can see how convenient to your business to have a column in Excel for the desired accounts, Matt_Moore.

The option to have this in QuickBooks is unavailable. I recommend sending this idea to our product developers. They look through submitted feedback for future updates and use those suggestions to develop changes for the product.

Here's how to do it:

Go to the Help menu at the top.
Select Send Feedback Online.
Click Product Suggestion.
Click on the drop-down for Type of Feedback and select Product Suggestion.
Select the Product Area.
Enter your thoughts and suggestions.
Click Send Feedback.

To be updated with our latest news and updates, including product improvements, I encourage you to visit the What's New section on your QBDT account or open this link to view our product update blogs.

You can also check this link: Import and export data in QuickBooks Desktop. This will show you more info on what other data you can export and import into QuickBooks Desktop.

Just drop me a comment below if you have any other questions. I'll be here to help.

Matt_Moore · ‎October 11, 2022

Thank You, This is what mine looks like. I would like to set it up so I could import it into Quickbooks and it would go to the correct account. That is these would all go to the account 1120 Property (sub account Farm) (sub sub account Maintenance and Materials)

1000 Property

1100 Farm

1120 Maintenance and Materials

Date	Downloaded Payee	Amount	Account Name
9/16/2015	Vendor One	($66.51)	1120 Property:Farm:Maintenance and Materials
9/21/2015	Vendor Two	($21.90)	1120 Property:Farm:Maintenance and Materials
12/14/2015	Vendor Three	($33.25)	1120 Property:Farm:Maintenance and Materials

Fiat Lux - ASIA · ‎October 11, 2022

You should import the data as Bills

Vendor

Transaction Date

RefNumber

Bill Due

Terms

Memo

Address Line1

Address Line2

Address Line3

Address Line4

Address City

Address State

Address PostalCode

Address Country

Vendor Acct No

Expenses Account

Expenses Amount

Expenses Memo

Expenses Class

Expenses Customer

Expenses Billable

Items Item

Items Qty

Items Description

Items Cost

Items Class

Items Customer

Items Billable

Unit of Measure

AP Account

Currency

Exchange Rate

Bell Telephone

01/09/2020

123

#########

Net 30

Office Telephone

Bell Telephone

1 Main Street

Newton

NY

12345

USA

Telephone

1000

Sales Line

Bell Telephone

01/09/2020

123

#########

Net 30

Office Telephone

Bell Telephone

1 Main Street

Newton

NY

12345

USA

Telephone

500

Service Line

Ajax Lumber

01/09/2020

400

#########

Net 30

Repairs

Ajax Lumber

200 Smith Street

Anytown

NY

11111

USA

Repairs

750

Front Door

Vendor and Ref Number are mandatory

and Bill Payments

Payee

Transaction Date

Apply to Bill RefNumber

Payment method

Bank Account

Credit Card Account

RefNumber

Amount

Memo

Discount Amount

Discount Account

Discount Class

To Be Printed

Exchange Rate

Bell Telephone

01/10/2020

123

Check

Checking

101

1500

Y

Ajax Lumber

01/10/2020

400

Check

Checking

102

1500

Y

Parts R Us

01/10/2020

1200

QuickBooks Credit Card

CC1

85

N

Payee, Payment Method, Reff Number and Amount are mandatory

Topics

Training

Community

Resources

Importing transaction data from CSV including accounts